0

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?

  • Did you by any chance create this table before with an ID column, and then forget to blow it away before using this migration? Forget to restart your server? Or maybe this is in the test environment and you forgot to run `rake db:test:prepare`? The behavior looks goofy, so the next step would be to post more of the code that is triggering the error and some logs. – jdl Oct 13 '10 at 18:22
  • See my answer below. Microsoft SQL schemas and my prior experiments were screwing it up. –  Oct 13 '10 at 18:27

1 Answers1

0

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.