Topic: Strange habtm table collision issues

  I'm really hoping that somebody can help me with this because I'm at a loss.  I have a habtm table called clients_ip_matters, and it seems to semi-randomly error out when inserting a record into the JOIN table because it is specifying the primary key (id).  Here's the relevant line from production.log

ActiveRecord::StatementInvalid (Mysql::Error: Duplicate entry '1' for key 1: INSERT INTO clients_ip_types (`client_id`, `id`, `ip_type_id`) VALUES (9, 1, 1)):

So, I'm confused as to why the habtm table insert is trying to specify an id of 1.  Obviously, this clashes with previous entries in the table.  What is going on here?


Re: Strange habtm table collision issues

I think I've solved my own looking at the wiki *idiot*

Apparently all habtm tables have a bug where there is an id primary key in the join table.  Taking it out apparently makes it go away.

Posted here for other idiots like me who don't read the wiki first.


Re: Strange habtm table collision issues

Rails is moving quickly away from HABTM relationships toward explicitly named (and modeled) relationships.  In your case the modern Rails style would be to  create a model named ClientIpTypes or something like that (with an appropriately named table.  Then that model would belong_to both a client and an ip_type.
This would allow you to track when the relationship was created, destroyed, who created it, etc.

Short of that you could get away with simply dropping the id from this join table.  You can make it a two-column table and set the primary key to both fields ("PRIMARY KEY (client_id, ip_type_id)").

I strongly recommend the first option because that's where Rails is headed.

Re: Strange habtm table collision issues

Ya I saw the keynote...but I thought this was all being rolled into ActiveResource which was dropped from 1.2?

If you could do this all along then why were there ever habtm tables and why isn't the first method you mentioned in the first edition of Agile Development?

Thanks for the info.