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.

score 27 · Accepted Answer · edited May 23 '17 at 12:09

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)

DaniG2k · Answer 2 · 2021-09-23T06:21:59.030

20

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')

edited Sep 23 '21 at 06:21

answered Apr 01 '15 at 10:05

DaniG2k

4,772
36
77

ALTER SEQUENCE is not supported by MySQL – PinnyM Feb 12 '16 at 15:56
The question is about MySQL not PostgreSQL. Although your answer is generally useful, it's better to keep answers relevant to the original question. – Peter T. Jul 29 '21 at 15:39
Added instructions for MySQL – DaniG2k Sep 23 '21 at 06:24

score 5 · Answer 3 · edited May 23 '17 at 11:53

5

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.

edited May 23 '17 at 11:53

Community

1
1

answered Sep 18 '16 at 03:57

Neil

4,578
14
70
155

score 0 · Answer 4 · answered May 19 '21 at 13:32

0

for sqlite3 it would be:

ActiveRecord::Base.connection.execute('delete from sqlite_sequence where name="your_tablename"')

answered May 19 '21 at 13:32

Oliver Gaida

1,722
7
14

How to reset auto increment field in a ActiveRecord migration?

4 Answers4

Linked