CODEWITHSUNDEEP
ruby-on-railsrubyoracle-databaseruby-on-rails-3rails-activerecord
Micheal
Micheal

Reputation: 2322

Why does rails specify primary key id manually in the insert statement?

I am using the rails has_many_belongs_to_many association as follows:

class MemberRole < ActiveRecord::Base
  attr_accessible :org_id, :name, :permission_ids
  has_and_belongs_to_many :permissions
end

class Permission < ActiveRecord::Base
  has_and_belongs_to_many :member_roles
end

My join table is as follows:

CREATE TABLE MEMBER_ROLES_PERMISSIONS
( member_role_id NUMBER(38,0) NOT NULL REFERENCES member_roles,
  permission_id NUMBER(38,0) NOT NULL REFERENCES permissions
);

When I try to create a new record as follows:

role = MemberRole.create(name: role_params["name"], org_id: role_params["org_id"], permission_ids: role_params["permission_ids"])

INSERT INTO "MEMBER_ROLES" ("ID", "NAME", "ORG_ID") VALUES (:a1, :a2, :a3) [["id", 66], ["name", "test100"], ["org_id", "2"]]

INSERT INTO "MEMBER_ROLES_PERMISSIONS" ("MEMBER_ROLE_ID", "PERMISSION_ID") VALUES (66, 8)

The problem with the above approach is that my member_roles table has sequence and trigger created as follows:

CREATE SEQUENCE MEMBER_ROLES_SEQ;

set define off;
CREATE OR REPLACE TRIGGER member_roles_bir 
BEFORE INSERT ON MEMBER_ROLES 
FOR EACH ROW

BEGIN
  SELECT MEMBER_ROLES_SEQ.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;

Because of the above the "ID" inserted inside the table is 67 instead of 66.

Should rails be even trying to insert the id manually? Is there a way I can tell rails not handle the id insert and let the oracle trigger handle it?

Upvotes: 5

Views: 911

Answers (2)

Jad
Jad

Reputation: 1288

FYI, in Oracle, where you have triggers and sequences in action, you need to specify:

self.sequence_name = :autogenerated

in the model, so that Ruby/Rails knows that it shouldn't try to push its own ID into that table.

Upvotes: 1

AshwinKumarS
AshwinKumarS

Reputation: 1313

This is because of the way oracle enhanced adapter works. Check these lines of code This is used to fetch the value from the sequence, so i assume Active record will be specifically sending this with the create statement and hence ID gets displayed in the query log.

Upvotes: 1

Related Questions