How to reset auto increment field in a ActiveRecord migration?

Question

In my migration I have:

def up
   MyModel.destroy_all
   MyModel.create!({:id=>1,:name=>'foo'})
   MyModel.create!({:id=>2,:name=>'fooBar'})
   MyModel.create!({:id=>3,:name=>'fooNull'})
end

because I need to override data that was already on my_models table

But Even though I'm specifying the id on MySQL it continues the numbering from the position it already was.

I need to rest the counter on the auto increment for id to have only this 3 new records with that id values trough Active Record migration on my Ruby on Rails application.

Answer 1

You have 2 separate issues. One is that you are trying to specify the id with mass assignment, rails won't allow you to do that. See Overriding id on create in ActiveRecord for a way to do that.

The other issue is that the auto-increment isn't resetting. Each DBMS has a unique way of setting an increment counter, and rails doesn't give you a generic way to access them, although it is implemented for some of them (not MySQL), see Rails way to reset seed on id field

So you'll need to run the MySQL specific SQL for this, which is something like:

ALTER TABLE my_models AUTO_INCREMENT = 1;

This should reset to the number after the largest id in your table (1 if there aren't any)

Answer 2

PostgreSQL:

ModelName.connection.execute('ALTER SEQUENCE model_name_id_seq RESTART WITH 1')

For example, resetting the auto increment field for the User model would look like:

User.connection.execute('ALTER SEQUENCE users_id_seq RESTART WITH 1')

MySQL:

ModelName.connection.execute('ALTER TABLE model_names AUTO_INCREMENT = 1')

For example, resetting the auto increment field for the User model would look like:

User.connection.execute('ALTER TABLE users AUTO_INCREMENT = 1')

Answer 3

In case anyone else wonders how to do this with MYSQL: here is the code one would use in the migration in order to reset the auto increment for a table:

ActiveRecord::Base.connection.execute('ALTER TABLE foo_bars AUTO_INCREMENT = 1')

• Where foo_bars would be the table name whose auto_increment you are resetting.

I figured this out thanks to the answer for this question.