Foreign key relationship with composite primary keys in MySQL

Question

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)

Gordon Linoff · Accepted Answer · 2017-10-26 10:39:54Z

The only way is using both keys:

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

However, I would encourage you to add an auto-incrementing column to table2 so such relationships can use a single key.

I wish add an ID AI PK, but i cant alter that table. Thank you so much for your help. I need upvote's on my question to achive the next badge..wich is very hard.

Sr rodriguez · Accepted Answer · 2017-10-26 11:39:45Z

-1

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.

answered Oct 26, 2017 at 11:39

366 bronze badges

2 Comments

I'm struck that this is basically a copy of my answer, posted half an hour afterwards.

Yes sorry. I was need in of boost me. I aprove your answer soon. No worry