Rails auto-assigning id that already exists

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

I resolved this issue by following command.

Run this in your rails console

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

Answer 5

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.