CODEWITHSUNDEEP
mysqlsql
Juan Torres
Juan Torres

Reputation: 77

Error adding foreign keys in many to many relationship

having two tables : "personnes"

CREATE TABLE IF NOT EXISTS personnes(
id      INT UNSIGNED NOT NULL AUTO_INCREMENT,
nom      VARCHAR(40)  NOT NULL,
PRIMARY KEY(id)
)ENGINE=InnoDB;

and "listadresses"

CREATE TABLE  IF NOT EXISTS listadresses(
id      INT UNSIGNED NOT NULL AUTO_INCREMENT,
adresse VARCHAR(40)  NOT NULL,
PRIMARY KEY(id)
)ENGINE=InnoDB;

i'm unable to add a foreign key for each column based on the primary key of the children tables to reproduce a many to many relation, how could i achieve this behaviour

CREATE TABLE  IF NOT EXISTS liaisons(
id_nom  INT UNSIGNED NOT NULL ,
id_adresse INT UNSIGNED NOT NULL ,
CONSTRAINT fk_nom FOREIGN KEY(id_nom) REFERENCES personnes(id),
CONSTRAINT fk_adresse FOREIGN KEY(id_adresse) REFERENCES listeadresses(id),
PRIMARY KEY(id_nom,id_adresse)
)ENGINE=InnoDB;

instead i got error code 1215. cannot add foreign key constraint, thanks in advance for any insight !

Upvotes: 0

Views: 76

Answers (1)

Mees Kluivers
Mees Kluivers

Reputation: 530

Here's your problem :)

Instead of:

REFERENCES listeadresses(id),

try

REFERENCES listadresses(id),

It can't add the foreign key, as obviously listeadresses doesn't exist, but listadresses does.

Upvotes: 2

Related Questions