Need help with Foreign Keys

[jayjmcc]

Can someone please help me to setup foreign keys between these 3 tables.

I would like to setup the FK as follow:

id_prov -->(FK)idprov_city and id_city -->(FK)idcity_suburb

The problem I'm experiencing is when I feed my database using dependent dropdown menus the value used to control the dropdowns is fed into the db not the label.

I would appreciate any help.

The tables are listed below


Province:

CREATE TABLE `db`.`province` (
`id_prov` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,
`name_prov` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id_prov`)
)
ENGINE = InnoDB;

City:

CREATE TABLE `db`.`city` (
`id_city` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`idprov_city` INTEGER UNSIGNED NOT NULL,
`name_city` VARCHAR(65) NOT NULL,
PRIMARY KEY (`id_city`)
)
ENGINE = InnoDB;

Suburb:

CREATE TABLE `db`.`suburb` (
`id_suburb` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`idprov_suburb` INTEGER UNSIGNED NOT NULL,
`idcity_suburb` INTEGER UNSIGNED NOT NULL,
`name_suburb` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id_suburb`)
)
ENGINE = InnoDB;

 

[PHV]

Why not using the FOREIGN KEY ... REFERENCES ... clause ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jayjmcc]

Ok I tried it and I could reference city to province.

But when I try to reference suburb to city I get the following error:

Cannot add or update a child row: a foreign key constraint fails (`pns_prop/#sql-990_9`, CONSTRAINT `#sql-990_9_ibfk_1` FOREIGN KEY (`idcity_suburb`) REFERENCES `city` (`id_city`))

Do you know why i get this error?

 

[r937]

you get that error whenever you try to insert a value into the idcity_suburb column that doesn't exist in the city table's id_city column

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon