Rails automatically assigns an identifier that already exists

Question

I create a new entry, for example:

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

My database currently has several thousand objects for the truck, but I assigned an identifier to several of them, so as to leave some identifier available. So what happens, the rails create an element with id = 150, and it works great. But then he tries to create an element and assign it id = 151, but this identifier may already exist, so I see 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 id 152, which will work fine if this value has not yet been accepted. How can I get rails to check if an identifier exists before it is assigned?

Thank!

EDIT

Truck ID is what is duplicated. The user already exists and is a constant in this case. This is actually a problem that I have to deal with. One option is to recreate the table to allow the rails to automatically assign each identifier this time. I'm starting to think that this is the best choice, because I have a few more problems, but the migration for this will be very difficult, because Truck is the foreign key in many other tables. Will there be an easy way to have rails for creating a new table with the same data that is already stored under the truck, with automatic identification of the identifier and maintaining all existing relationships?

Answer 1

Rails probably uses the built-in PostgreSQL sequence. The idea of ​​consistency is that it is used only once.

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

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

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

An alternative solution is to override the save () method in your company class to manually set the company ID for new lines before saving.

Answer 2

I did this to solve this problem for me.

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

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

Answer 3

Based on @Apie's answer .

You can complete the task and complete it when you need:

 rake database:correction_seq_id 

You create the following tasks:

 rails g task database correction_seq_id 

And in the generated file ( 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

Sounds to me like a database problem, not a Rails problem. Is it possible that your database has the wrong semantic seed in the id column? To try trying a couple of inserts directly into your database and see if the same behavior exists.

Answer 5

I solved this problem with the following command.

Run this in your rails console

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