Many to many link table compound primary key explosion

Rails
1

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 :slight_smile:

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

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;