Sunday, September 21, 2008

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
   has_and_belongs_to_many :surveys

class Survey < ActiveRecord::Base
   has_and_belongs_to_many :questions

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.