Why is HABTM generating queries that include an 'id' column?

Question

I have a Role model and Permission model.

The Role model:

 has_and_belongs_to_many :permissions

The Permission model:

has_and_belongs_to_many :roles

The migration to create the permissions_roles table:

class CreatePermissionsRoles < ActiveRecord::Migration
  def self.up
    create_table :permissions_roles, :id => false do |t|
      t.integer :permission_id
      t.integer :role_id
    end
  end

  def self.down
    drop_table :permissions_roles
  end
end

When I try to assign permissions to a role, I get the error "Invalid column name 'id'." Further examination reveals that the query attempting to execute is:

INSERT INTO "permissions_roles" ("permission_id", "role_id", "id") VALUES (1, 1, 1)

Why in the world is it attempting to add a row with an id value?

Answer 1

Figured it out. I'm using Microsoft SQL on the back end. This has been one of the many nuances of working with Microsoft SQL.

The problem was that I had a table "dbo.permissions_roles" that included an id field from prior experimentation. The table that actually had the data in it was "rails_sa.permissions_roles" that did not include the id (rails_sa is the name of my schema and user that connects).

For some reason that I still can't explain, the data behind the models was getting stored in rails_sa.permissions_roles. But, the join table definition was coming from dbo.permissions_roles. I'm sure there is a rational explanation to this edge case.