MySQL Cross-table CREATE or UPDATE with new and existing items

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:

id=1
idProvince=1 (provinces.name=‘Quebec’)
idCity=1 (cities.name=‘Montreal’)
idStreet=2 (streets.name=‘Sherbrooke Blvd.’)
civicNo=‘24597’

and want to change it to the following address:
2459 Curé-Labelle St., Laval, Quebec
(Street and City names are new, the rest can be easily updated)

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)

  1. 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
  1. 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.
  2. Now you can insert/update your data into addresses accordingly (INSERT INTO can be used again)
1 Like