Unrestrict primary key not working with MSSQL

[Venom721]

Given a model

class Status < Sequel::Model(DB[:statuses])
end

It has only two columns id and name.

When i try to run

::Statuses.unrestrict_primary_key
::Statuses.find_or_create(id: 1, name: 'Active')
::Statuses.unrestrict_primary_key
I get the error "Cannot insert explicit value for identity column in table 'statuses' when IDENTITY_INSERT is set to OFF."

Only I got it to work was
::Statuses.unrestrict_primary_key
::Statuses.db.run "SET IDENTITY_INSERT statuses ON"
::Statuses.find_or_create(id: 1, name: 'Active')
::Statuses.db.run "SET IDENTITY_INSERT statuses OFF"
::Statuses.unrestrict_primary_key

Am I missing something or should the unrestrict_primary_key method enable and disable identity insert?

[Venom721]

We are using sequel 5.77.0

[jeremyevans]

The error you are getting is a database error, not a Sequel::Model error. unrestrict_primary_key is for controlling mass assignment at the model layer, it has no effect on whether the database supports assigning to identity columns. If you remove unrestrict_primary_key, you get a different model-layer error, correct?

[Venom721]

Yes I get a Mass assigment error stating that the id is a restricted primary key.

[janko]

FWIW, activerecord-sqlserver-adapter gem automatically turns identity insert on and off around insert statements, see code.

[jeremyevans]

That's certainly not something I would want to do by default. I guess I would consider a model plugin for it, though.

[jeremyevans]

The implement the support by trying to parse SQL: https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/58c72ea552613a05ec8ce5321382c19d6248790a/lib/active_record/connection_adapters/sqlserver/database_statements.rb#L385

That's obviously not acceptable for Sequel, but for a model plugin, it shouldn't be needed, just see if the primary key column is a key in the hash of values being inserted.

[Venom721]

Thanks for the quick replies, I misunderstood this mechanism.