# Many to many link table compound primary key explosion

**URL:** <https://www.ruby-forum.com/t/many-to-many-link-table-compound-primary-key-explosion/51644>\
**Category:** Rails\
**Created:** [January 5, 2006, 12:57am UTC](https://www.ruby-forum.com/t/many-to-many-link-table-compound-primary-key-explosion/51644 "2006-01-05T00:57:26Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![johnlester](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e480ec/32.png) [@johnlester](https://www.ruby-forum.com/u/johnlester)\
**Post date:** [January 5, 2006, 12:57am UTC](https://www.ruby-forum.com/t/many-to-many-link-table-compound-primary-key-explosion/51644/1 "2006-01-05T00:57:26Z")

</div>

Hi,

I have a many-to-many relationship between two tables, questions and  
answers. My MySQL schema for the link table creates a primary key from  
the two fields to prevent duplicate records. When I add the same answer  
to the same question more than once, rails attempts to create a  
duplicate record and explodes with a MySQL error:

MysqlError: Duplicate entry ‘3-3’ for key 1: INSERT INTO  
answers\_questions (`question_id`, `answer_id`) VALUES (3, 3)

Now, I realise I can fix this by removing the primary key from my  
schema, and some would say the primary key is unnecessary, but I think  
Rails’ behaviour is wrong. Any thoughts? MySQL in particular supports  
“ON DUPLICATE KEY UPDATE” which would help. Should rails delete any  
existing link records first?

If the link table had other attributes, and I didn’t have my compound  
primary key, which of the link table’s duplicate records attributes  
would be included on my next select? (the latest I’d hope 🙂

Any ideas for a workaround? I currently add answers to questions like  
this: @question.answers \<\< @answer. Should I just implement an  
push\_unique\_answer method to the questions model and check for existing  
links there first? Or override the @question.answers method?

Thanks in advance,

John.  
[http://johnleach.co.uk](http://johnleach.co.uk)

create table answers\_questions (  
question\_id int unsigned not null,  
answer\_id int unsigned not null,  
constraint fk\_qid foreign key (question\_id) references questions(id),  
constraint fk\_aid foreign key (answer\_id) references answers(id),  
primary key (question\_id, answer\_id)  
) ENGINE=InnoDB;