TinyTds::Error: Cannot insert the value NULL into column 'ID'

Question

My Ruby on Rails system is moving from Oracle to Microsoft SQL Server 2012. The back end database has already been converted by a third part from Oracle to Microsoft SQL Server. I have no control over the schema structure. This cannot be changed.

Using activerecord-sqlserver-adapter, tiny_tds and Freetds I can connect to the new database. Most DB requests work well.

However, many of the tables have ID primary keys that are auto incremented by SQL Server, in other words, PK IDENTITY(1,1) columns. The same code that worked with Oracle connections fails under SQL Server with the following error:

TinyTds::Error: Cannot insert the value NULL into column 'ID'

I know why it is doing this as the ID column is indeed a primary_key, and IDENTITY(1,1) column and includes the NOT NULL restriction. That is fine.

Inserting into the table works fine if using raw SQL execute statements where of course I exclude the ID column from the INSERT statement.

However I have spent days googling and I cannot find a way of telling Ruby on Rails not to try and save the ID column.

So I have an instance of a class, @book, that contains

Library::Book(id: integer, title: string, isbn: string ...)

When I do a @book.save! it generates the error above.

@book = Library::Book.new( .. )
:
:
@book.save!

TinyTds::Error: Cannot insert the value NULL into column 'ID'

Rather that resort to bare metal SQL, how do I do things more Railsy and tell it I want to save the record but not try and save the ID field as it is auto incremented? So effectively I am trying to save

Library::Book(title: string, isbn: string ...) if a new insert entry or Library::Book(id: integer, title: string, isbn: string ...) if trying to update an entry.

Due to imposed restrictions I am using: Ruby 2.3.3p222 Rails 4.0.13 activerecord (4.0.13) activerecord-sqlserver-adapter (4.0.4) tiny_tds (1.0.2) Freetds 1.00.27

Tom · Answer 1 · 2019-02-08T20:05:15.667

1

You can use ActiveRecord::Relation#find_or_initialize_by. This assumes that you have enough attributes known at the time to uniquely identify the record.

That will solve the last part of your question. But it sounds like the id column is not set to auto-increment. You need to set that so when Rails sends a null id the DB will set it properly.

Update: To make the column auto-increment you will need to run a migration. You can then do:

Alter table books modify column id int(11) auto_increment;

Update: If we cannot modify the database we can do:

class Book

  private

  def create_or_update(*args, &block)
    self.id ||= Book.maximum(:id) + 1
    super
  rescue ActiveRecord::RecordNotUnique
    self.id = nil
    retry
  end
end

It's pretty janky, and I would highly recommend updating the column if possible.

edited Feb 08 '19 at 20:05

answered Feb 08 '19 at 14:13

Tom

1,311
10
18

Thanks Tom. This is what I have been trying to find, a means of telling Rails that the ID column is an auto increment type. I cannot find the statement or syntax to do this. I have self.primary_key= "id" in the model. Do I need to modify this or define the auto increment attribute elsewhere? Some of the suggestions I have seen seem not to be supported in my environment. – user321321 Feb 08 '19 at 14:35
@user321321 I updated my answer with a migration to change the columns behaviour – Tom Feb 08 '19 at 14:38
Hi Tom. The Microsoft SQL Server table is fixed and has the ID defined as this. I cannot change it. [ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL. Will the migration above modify the structure of the DB? – user321321 Feb 08 '19 at 14:52
@user321321 I added a way to override the Rails behavior if you are not allowed to modify the DB. – Tom Feb 08 '19 at 15:03
Thanks Tom. It's certainly stopped the insert error. I am just wondering though by doing this is there a very slight possibility that two separate processes may both be assigned that same ID, the first inserting a new record and the second overwriting the first? I am a novice with the workings of ActiveRecord. – user321321 Feb 08 '19 at 15:36
@user321321 It wouldn't overwrite the first. The database would complain about uniqueness (assuming it is a unique column). I added one way to protect against that. If there is a race condition that it loses it will reset the id and try again. – Tom Feb 08 '19 at 15:44
Thank you. I'll give it a go over the week end. Many thanks. – user321321 Feb 08 '19 at 15:53
1

Why not `self.id ||= Book.maximum(:id).to_i + 1` seems a bit more concise – engineersmnky Feb 08 '19 at 19:43
@engineersmnky Because early morning :). Updated with the much better query. – Tom Feb 08 '19 at 20:06

TinyTds::Error: Cannot insert the value NULL into column 'ID'

1 Answers1