0

I would like to create a table that has multiple foreign keys to multiple different tables (as the relationship is many to many). 

#creating t1
CREATE TABLE t1
(ID          INT               AUTO_INCREMENT          primary key,
x1           VARCHAR(50)
);

#Creating t2
CREATE TABLE t2
(v1       VARCHAR(50),
v2        VARCHAR(50),
primary key (v1, v2)
);

#creating attended table
CREATE TABLE t3
(ID        INT,
v1         VARCHAR(50),
v2         VARCHAR(50),
primary key (ID, v1, v2 ),
foreign key(v1)                 references t2(v1),
foreign key(v2)                 references t2(v2),
foreign key(ID)                 references t1(ID)
);

Above is my code. I get no errors for creating t1 and t2. However, I get the following code when I try to create t3:

ERROR 1215 (HY000): Cannot add foreign key constraint

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
billyl320
  • 62
  • 1
  • 9

1 Answers1

0

The foreign key is the complete key of the other table - you can not only use half of it as FK.

t2 has a combined primary key. when referencing a fk in t3 you need both.

See Why use multiple columns as primary keys (composite primary key)

To create a complex FK see SQL Server: adding foreign key on multiple columns or for MySql see Multiple-column foreign key in MySQL?

Patrick Artner
  • 50,409
  • 9
  • 43
  • 69