Ruby on Rails | Screencasts | Download | Documentation | Weblog | Community | Source

Ticket #5216 (closed defect: invalid)

Opened 3 years ago

Last modified 2 years ago

HABTM constructs incorrect INSERT into join_table when join_table.id column exists

Reported by: choonkeat@gmail.com Assigned to: David
Priority: normal Milestone:
Component: ActiveRecord Version: 1.1.1
Severity: normal Keywords:
Cc: josh@hasmanythrough.com

Description

A join_table usually contains at least 2 xx_id columns, meant as foreign_key columns of 2 other tables. When the join_table contains other extra columns, HABTM correctly ignores those columns and performs worker.skills << skill as INSERT INTO join_table (fk1_id, fk2_id) VALUES ...

However, if the join_table has its own id, HABTM gets confused and include the 'id' column into the INSERT with one of the fk id's value resulting in the sql INSERT INTO join_table (id, fk1_id, fk2_id) which is incorrect. Had HABTM continue to perform INSERT INTO join_table (fk1_id, fk2_id) VALUES ... it would've been correct.

Including extra columns in join_table shouldn't result in such drastic behavior differences?

Attachments

habtm_join_table_test.diff (4.7 kB) - added by choonkeat@gmail.com on 05/28/06 17:46:03.
unit test illustrating 1 error and 1 failure in mysql
habtm_join_table_test.patch1.diff (0.9 kB) - added by anonymous on 05/29/06 16:30:42.
patch for test_join_notid. not copying value into join_table if column is record's primary key
habtm_join_table_test.patch2.diff (454 bytes) - added by choonkeat@gmail.com on 05/29/06 16:32:27.
patch to fix test_join_notid_with_fk, turning primary_key_name into String when first assigned.

Change History

05/28/06 17:46:03 changed by choonkeat@gmail.com

  • attachment habtm_join_table_test.diff added.

unit test illustrating 1 error and 1 failure in mysql

05/29/06 16:28:48 changed by choonkeat@gmail.com

  • cc set to josh@hasmanythrough.com.

(with respect to the 2 tests that failed from attachment habtm_join_table_test.diff)

test_join_notid failed because when a column from 'record' (i.e. skills) exists in join_table, the value is used. this gives problem if the column is record's primary key - inserting its value into join_table is probably always wrong.

test_join_notid_with_fk failed for a slighter reason, the value for :foreign_key should be a String otherwise "case column.name when ..." comparison will fail. However, since value for :join_table is a symbol, things (at least i) got pretty mixed up regarding when to use what.

05/29/06 16:30:42 changed by anonymous

  • attachment habtm_join_table_test.patch1.diff added.

patch for test_join_notid. not copying value into join_table if column is record's primary key

05/29/06 16:32:27 changed by choonkeat@gmail.com

  • attachment habtm_join_table_test.patch2.diff added.

patch to fix test_join_notid_with_fk, turning primary_key_name into String when first assigned.

05/29/06 16:34:34 changed by josh@hasmanythrough.com

  • status changed from new to closed.
  • resolution set to invalid.

Join tables are not meant to have a primary key. If you want a primary key id, use has_many :through instead of has_and_belongs_to_many. Extra attributes on habtm are now deprecated and you shouldn't expect any support for them in the future.

07/30/07 17:15:15 changed by joshknowles

While I agree that join tables are not meant to have a primary key, it is the default behavior of a create_table migration to insert an id column. This causes issues on mysql when trying to create a habtm relationship as it sees the id column and trys to insert a value into it causing a duplicate key constraint error.

I agree with the original poster in that the INSERT statement for habtm shouldn't attempt to insert a value for "id".