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
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?