Skip to content
Advertisement

Foreign key relationship with composite primary keys in MySQL

Which is the best way to create a relationship between two tables when referenced table has a composite primary key?

table1{
   id,
   name
}

table2{
   id1,
   id2,
   name
}PrimaryKey(id1, id2)

Advertisement

Answer

One way is this

alter table t add constraint fk_t_id1_id2
foreign key (id1, id2) references table2(id1, id2);

As Gordon said, the best option is create an auto incremental ID and make this the primary key.

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement