CODEWITHSUNDEEP
ruby-on-railssqliteactiverecord
jimcgh
jimcgh

Reputation: 5957

Reset the id field in a table in a sqlite db in Rails

Im using sqlite db in a sample rails application. From my users table, i have cleared all records using User.delete_all. But now whenever i insert a new record in the table using User.create, the id is starting at a value which is one more than the id of the last record which was there in the table. For example, if my table had 5 records and i cleared all, then when i do User.create, its starting at id 6. Is there any way i can make the id start from 1 again ?

Thank You

Upvotes: 0

Views: 4243

Answers (2)

jimcgh
jimcgh

Reputation: 5957

Similar question : How to reset a single table in rails? . We can run the following at rails console to reset id column to 1 for a sqlite table

ActiveRecord::Base.connection.execute("DELETE from sqlite_sequence where name = '<table_name>'")

Upvotes: 5

sebastian_oe
sebastian_oe

Reputation: 7320

You seem to have autoincrement turned on for the id column. Sqlite handles these values in an internal table called sqlite_sequence. You could reset the id for a particular autoincrement-enabled table by querying:

UPDATE "sqlite_sequence" SET "seq" = 0 WHERE "name" = $YOURTABLENAME

However, this is not a good idea because the autoincrement functionality is intended to be used in a way that the user does not influence its algorithm. Ideally, you should not care about the actual value of your id but consider it only as a unique identifier for a record.

Upvotes: 3

Related Questions