has_and_belongs_to_many & Duplicate entry Error
In one of my Rails projects at eSpace, i had a Many to Many association between two models. this allowed me to look more deeply into the has_and_belongs_to_many association.
When you say:
class Question < ActiveRecord::Base
class Survey < ActiveRecord::Base
Rails will assume there is a table questions_surveys that include foreign keys to the two entities.
So this table should be created by the following migration
create_table :questions_surveys,:id => false do |t|
t.integer :question_id, :null => false
t.integer :survey_id, :null => false
:id=>false will prevent the creation of the default primary key for that table.This is very important for has_and_belongs_to_many associations. as the API documentation say; other attributes in thatrelation will be loaded with the objects and will be read only, including the :id. So failing to disable the id generationfor that table will cause the loaded objects to have and "id" attribute holding the value of the id or the questions_surveys entries instead of the ids of the target entity (question or survey in our example).
That's why u may have only 20 questions but when you try "survey.questions.collect(&:id) you'll find values that are totally out of the range.
This is also the cause of the "Mysql::Error: Duplicate entry '#' for key #" entry error you'll find while adding entries. (survey.questions << question )
It is highly recommended in the rails documentation to use a real model to represent the many to many association.But if your case was just a simple two foreign keys table, use has_and_belongs_to_many, just don't forget to disable the id generation.
I must admit.. most of us don't read the API documentation so carefully and fall into this