Foreign Key problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Foreign Key problem

Author

Topic

jmcc1928
Starting Member

3 Posts

Posted - 2009-07-31 : 13:59:53

Please help me with setting up foreign keys.

Here are my 3 tables

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;

Here is how I'm trying to setup my Foreign Keys:

(This First one works)
ALTER TABLE `db`.`city`
ADD FOREIGN KEY city(idprov_city) REFERENCES province (id_prov)

ERROR:
(Cannot add or update a child row: a foreign key constraint fails)
ALTER TABLE `db`.`suburb`
ADD FOREIGN KEY suburb(idcity_suburb) REFERENCES city (id_city)

Thanks in Advance

JM

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-31 : 14:05:37

You probably want to post your question in a MySQL forum (this is an MS SQL Server forum)...but...seems you have a value (or values) in your suburb table, idcity_suburb column that are not in the city table. Find those, fix them, you should be ok

jmcc1928
Starting Member

3 Posts

Posted - 2009-07-31 : 14:26:11

Thank you that worked

JM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources