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?
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
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.
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
I resolved this issue by following command.
Run this in your rails console
ActiveRecord::Base.connection.reset_pk_sequence!('table_name')
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With