Rails auto-assigning id that already exists

95
votes

I create a new record like so:

truck = Truck.create(:name=>name, :user_id=>2)

My database currently has several thousand entities for truck, but I assigned the id's to several of them, in a way that left some id's available. So what's happening is rails creates item with id = 150 and it works fine. But then it tries to create an item and assign it id = 151, but that id may already exist, so I'm seeing this error:

ActiveRecord::RecordNotUnique (PG::Error: ERROR: duplicate key value violates unique constraint "companies_pkey" DETAIL: Key (id)=(151) already exists.

And the next time I run the action, it will simply assign the id 152, which will work fine if that value isn't already taken. How can I get rails to check whether an ID already exists before it assigns it?

Thanks!

EDIT

The Truck id is what is being duplicated. The user already exists and is a constant in this case. It actually is a legacy issue that I have to deal with. One option, is to re-create the table at let rails auto assign every id this time around. I'm beginning to think this may be the best choice because I'm have a few other problems, but the migration for doing this would be very complicated because Truck is a foreign key in so many other tables. Would there be a simple way to have rails create a new table with the same data already stored under Truck, with auto-assigned ID's and maintaining all existing relationships?

5
ruby-on-railsruby-on-rails-3postgresql
why aren't you letting rails assign the ID automatically? That would eliminate any danger of duplication — or is this a legacy data issue where you must retain old IDs? Just want to understand the business case a little since this is not business-as-usual when creating a new object. – MBHNYC
@MBHNYC I think that D-Nice is assigning a user_id while creating the company, and not id as you are thinking(and I did for a moment too). – Anil
Ooo good catch Anil — you're totally right. @D-Nice, perhaps add your migration for this table to your post incase there's something odd? Tempting to edit out that user_id to eliminate the confusion.. – MBHNYC
No, sorry it was unclear, the Company id is what is being duplicated. The user already exists and is a constant in this case. It actually is a legacy issue that I have to deal with. Will edit post with more info – D-Nice
You don't need to recreate the table. Just see my answer to reset the sequence. – Dondi Michael Stroma

5 Answers

89
votes

Rails is probably using the built-in PostgreSQL sequence. The idea of a sequence is that it is only used once.

The simplest solution is to set the sequence for your company.id column to the highest value in the table with a query like this:

SELECT setval('company_id_seq', (SELECT max(id) FROM company));

I am guessing at your sequence name "company_id_seq", table name "company", and column name "id" ... please replace them with the correct ones. You can get the sequence name with SELECT pg_get_serial_sequence('tablename', 'columname'); or look at the table definition with \d tablename.

An alternate solution is to override the save() method in your company class to manually set the company id for new rows before saving.

208
votes

I did this which solved the issue for me. 

ActiveRecord::Base.connection.tables.each do |t|
  ActiveRecord::Base.connection.reset_pk_sequence!(t)
end

I found the reset_pk_sequence! from this thread. http://www.ruby-forum.com/topic/64428

26
votes

Based on @Apie answer.

You can make a task and run when you need with:

rake database:correction_seq_id

You create tasks like this:

rails g task database correction_seq_id

And in the file generated (lib/tasks/database.rake) put:

namespace :database do
    desc "Correction of sequences id"
    task correction_seq_id: :environment do
        ActiveRecord::Base.connection.tables.each do |t|
            ActiveRecord::Base.connection.reset_pk_sequence!(t)
        end
    end
end
6
votes

I resolved this issue by following command.

Run this in your rails console

ActiveRecord::Base.connection.reset_pk_sequence!('table_name')
4
votes

Sounds to me like a database problem and not a Rails problem. Is it possible your database has an improper identity seed on your id column? To test try doing a couple inserts directly into your database and see if the same behavior exists.