Rails - Jan 2006 - many to many link table compound primary key explosion

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

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;

I think to begin with, an appropriate change to your data model would be:

create table answers_questions (
  id int not null autoincrement,
  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 (id)
) ENGINE=InnoDB;

In general, it''s considered better practice to have primary keys be
''meaningless'' and not use them to implement what essentially
is a business
rule.

You can then implement uniqueness on the two foreign keys directly in active
record using the built in validation capabilities it provides to ensure that
you  don''t get duplicates.

For info on validation, see:

     http://api.rubyonrails.com/classes/ActiveRecord/Validations.html

You have a choice to put the validation logic in the database or put it in the
ActiveRecord models. There are reasons to go either way, but in this case
pulling the valdation out of the database and putting it in the ActiveRecord
models looks like it may be easier nd give you more flexibility.



-- 
Kevin Bedell
http://www.kbedell.com

"The future belongs to those who believe in the beauty of their
dreams."
- Eleanor Roosevelt



----- End forwarded message -----


-- 
Kevin Bedell
http://www.kbedell.com

"The future belongs to those who believe in the beauty of their
dreams."
 - Eleanor Roosevelt

Hi Kevin,

thanks for the advice.

I don''t believe changing my data model is the right thing here.  For a
start it''s already helped me identify this potential problem :)  

Also, those rows are already unique by the combination of answer_id and
question_id.  Adding a new primary key called id is meaningless.  

An additional problem is that ActiveRecord includes all columns from the
link table when joining the linked table, so the id field would collide
(the link table id would take preference as I understand).

Technically, the primary keys in this instance aren''t implementing a
business rule.  The link table should never include duplicate link
records.  At the end of the day, I''m trying to do something that
violates this rule, which is wrong.  Perhaps the best course of action
is just to figure out a graceful way to handle it in my code.  I don''t
know how to override the @question.answers.push method, if this were to
be the best option.

John.
http://johnleach.co.uk

p.s: Having just tested it, I see that without this compound primary
key, rails creates duplicate rows as I suspected.


On Thu, 2006-01-05 at 00:04 -0500, Kevin Bedell wrote:
> I think to begin with, an appropriate change to your data model would be:
> 
> create table answers_questions (
>   id int not null autoincrement,
>   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 (id)
> ) ENGINE=InnoDB;
> 
> In general, it''s considered better practice to have primary keys
be
> ''meaningless'' and not use them to implement what
essentially is a business
> rule.
> 
> You can then implement uniqueness on the two foreign keys directly in
active
> record using the built in validation capabilities it provides to ensure
that
> you  don''t get duplicates.
> 
> For info on validation, see:
> 
>      http://api.rubyonrails.com/classes/ActiveRecord/Validations.html
> 
> You have a choice to put the validation logic in the database or put it in
the
> ActiveRecord models. There are reasons to go either way, but in this case
> pulling the valdation out of the database and putting it in the
ActiveRecord
> models looks like it may be easier nd give you more flexibility.
> 
> 
> 
> -- 
> Kevin Bedell
> http://www.kbedell.com
> 
> "The future belongs to those who believe in the beauty of their
dreams."
> - Eleanor Roosevelt
>

I agree that putting an id column in the join table is a bad idea because of
the ''clobbering'' that will happen.  The documentation even
mentions this.
Not to say that you can''t, but you would then have to create a
different
relationship between the models

questions (hm)...(bt) answers_questions (bt)...(hm) answers

and answers_questions would become a model

I might recommend the following however

class Question < AR::Base
  has_and_belongs_to_many :answers

  # only add the answer to the question only if the answer is not already
associated
  def add_answer(answer)
    answers << answer unless answers.include?(answer)
  end
end

a = Answer.create(...)
q = Question.find(1)
q.add_answer(a)


On 1/5/06, John Leach <john@johnleach.co.uk>
wrote:
>
> Hi Kevin,
>
> thanks for the advice.
>
> I don''t believe changing my data model is the right thing here. 
For a
> start it''s already helped me identify this potential problem :)
>
> Also, those rows are already unique by the combination of answer_id and
> question_id.  Adding a new primary key called id is meaningless.
>
> An additional problem is that ActiveRecord includes all columns from the
> link table when joining the linked table, so the id field would collide
> (the link table id would take preference as I understand).
>
> Technically, the primary keys in this instance aren''t implementing
a
> business rule.  The link table should never include duplicate link
> records.  At the end of the day, I''m trying to do something that
> violates this rule, which is wrong.  Perhaps the best course of action
> is just to figure out a graceful way to handle it in my code.  I
don''t
> know how to override the @question.answers.push method, if this were to
> be the best option.
>
> John.
> http://johnleach.co.uk
>
> p.s: Having just tested it, I see that without this compound primary
> key, rails creates duplicate rows as I suspected.
>
>
> On Thu, 2006-01-05 at 00:04 -0500, Kevin Bedell wrote:
> > I think to begin with, an appropriate change to your data model would
> be:
> >
> > create table answers_questions (
> >   id int not null autoincrement,
> >   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 (id)
> > ) ENGINE=InnoDB;
> >
> > In general, it''s considered better practice to have primary
keys be
> > ''meaningless'' and not use them to implement what
essentially is a
> business
> > rule.
> >
> > You can then implement uniqueness on the two foreign keys directly in
> active
> > record using the built in validation capabilities it provides to
ensure
> that
> > you  don''t get duplicates.
> >
> > For info on validation, see:
> >
> >      http://api.rubyonrails.com/classes/ActiveRecord/Validations.html
> >
> > You have a choice to put the validation logic in the database or put
it
> in the
> > ActiveRecord models. There are reasons to go either way, but in this
> case
> > pulling the valdation out of the database and putting it in the
> ActiveRecord
> > models looks like it may be easier nd give you more flexibility.
> >
> >
> >
> > --
> > Kevin Bedell
> > http://www.kbedell.com
> >
> > "The future belongs to those who believe in the beauty of their
dreams."
> > - Eleanor Roosevelt
> >
>
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060105/0945098b/attachment.html

Hi Chris,

thanks, I''ll use that method.  

Do you think ActiveRecord''s behaviour is correct?  Or should it check
for duplicates itself here?  Without a compound primary key this might
have gone undetected and I think DHH''s "single layer of
cleverness"[1]
post has people believing the db shouldn''t be checking this stuff.

Should I perhaps file a bug?

John.
http://johnleach.co.uk


[1] http://www.loudthinking.com/arc/000516.html
I''m not suggesting that *is* what DHH is saying though :)

On Thu, 2006-01-05 at 06:47 -0500, Chris Hall wrote:
> I agree that putting an id column in the join table is a bad idea
> because of the ''clobbering'' that will happen.  The
documentation even
> mentions this.  Not to say that you can''t, but you would then have
to
> create a different relationship between the models
> 
> questions (hm)...(bt) answers_questions (bt)...(hm) answers
> 
> and answers_questions would become a model
> 
> I might recommend the following however
> 
> class Question < AR::Base
>   has_and_belongs_to_many :answers
>   
>   # only add the answer to the question only if the answer is not
> already associated
>   def add_answer(answer)
>     answers << answer unless answers.include?(answer)
>   end
> end
> 
> a = Answer.create(...)
> q = Question.find(1)
> q.add_answer(a)