Need help with Foreign Keys
Need help with Foreign Keys
(OP)
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;
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;
RE: Need help with Foreign Keys
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Need help with Foreign Keys
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?
RE: Need help with Foreign Keys
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon