Here are two of the crosslinked tables that I have to work with (I am using an existing schema). My question is how do I UPDATE or INSERT new addresses knowing that sometimes the City or Street will not exist in the other tables? The Provinces table is already populated, this one will not be a problem.
CREATE TABLE `adresses` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`idProvince` int(11) NOT NULL DEFAULT '1',
`idCity` int(11) NOT NULL DEFAULT '1',
`idStreet` int(11) NOT NULL,
`civicNo` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `liProvince` (`idProvince`),
KEY `liVille` (`idCity`),
KEY `liRue` (`idStreet`),
CONSTRAINT `liProvince` FOREIGN KEY (`idProvince`) REFERENCES `provinces` (`idProvince`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `liCity` FOREIGN KEY (`idCity`) REFERENCES `cities` (`idCity`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `liStreet` FOREIGN KEY (`idStreet`) REFERENCES `streets` (`idStreet`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
CREATE TABLE `cities` (
`idCity` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(200) NOT NULL,
PRIMARY KEY (`idCity`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
How many queries will that require? Iâm only starting to get the hang of this. I have no problem updating one table, or updating two with a JOIN, but that only works if both tables contain the data Iâm adding. If I insert or update an address with a city name that is not in the cities table, wonât that throw off an error since the idCity in the addresses table canât be NULL?
[quote=âkimlevesque330, post:1, topic:101133, full:trueâ]⊠sometimes the City or Street will not exist in the other tables?[/quote]the answer is, you must allow those columns to be NULL, and then insert NULLs into them, whenever you do not know the city or street
I will always know all these fields, since these will be required before an UPDATE or INSERT is done. For Example if I have the address already in the database:
Do we still have to go thru the NULL process to update everything or can we do it all together since we have all the info? Iâd rather not change the schema or the tables as another application is already using this same database for a different purpose.
IF you have these addresses in a table or at least a temp table, you can do it in three steps (OK technically five but itâs three steps logically)
Use a JOIN query to find any city/state/provinces arenât on your holding tables. VERIFY THE DATA!!!
SELECT t.idProvince
, t.idCity
, t.idStreet
, p.id
, c.id
, s.id
FROM tempAddress t
LEFT OUTER JOIN provinces p ON p.id = t.idProvince
LEFT OUTER JOIN cities ON c.id = t.idCity
LEFT OUTER JOIN streets s ON s.id = t.idStreet
WHERE p.id IS NULL OR c.id IS NULL OR s.ID is NULL
After youâve verified the data (youâd be amazed how badly people spell/type), use the INSERT INTO statement form to add any city/state/province that arenât on the tables. Youâll want to do this once for provinces, once for cities, once for streets.
Now you can insert/update your data into addresses accordingly (INSERT INTO can be used again)