SQLite Reset Primary Key Field

Question

I have a few tables in SQLite and I am trying to figure out how to reset the auto-incremented database field.

I read that DELETE FROM tablename should delete everything and reset the auto-incremement field back to 0, but when I do this it just deletes the data. When a new record is inserted the autoincrement picks up where it left off before the delete.

My ident field properties are as follows:

• Field Type: integer
• Field Flags: PRIMARY KEY, AUTOINCREMENT, UNIQUE

Does it matter I built the table in SQLite Maestro and I am executing the DELETE statement in SQLite Maestro as well?

Any help would be great.

Solution 1

Try this:

delete from your_table;    
delete from sqlite_sequence where name='your_table';

SQLite Autoincrement

SQLite keeps track of the largest ROWID that a table has ever held using the special SQLITE_SEQUENCE table. The SQLITE_SEQUENCE table is created and initialized automatically whenever a normal table that contains an AUTOINCREMENT column is created. The content of the SQLITE_SEQUENCE table can be modified using ordinary UPDATE, INSERT, and DELETE statements. But making modifications to this table will likely perturb the AUTOINCREMENT key generation algorithm. Make sure you know what you are doing before you undertake such changes.

Solution 2

You can reset by update sequence after deleted rows in your-table

UPDATE SQLITE_SEQUENCE SET SEQ=0 WHERE NAME='table_name';

Solution 3

As an alternate option, if you have the Sqlite Database Browser and are more inclined to a GUI solution, you can edit the sqlite_sequence table where field name is the name of your table. Double click the cell for the field seq and change the value to 0 in the dialogue box that pops up.

Comments

[321X]

Just a small side note: Table name in where clause is case sensitive

[binary]

another way is updating sqlite_sequence table. update sqlite_sequence set seq = 0 where name='<tablename>' This will not reset the rowid of sqlite_sequence table.

[Nick Dandoulakis]

@binary, thanks for mentioning an alternative way :) I'd like to note that SQLite reuses "deleted" space, so it doesn't really make much of a difference which solution we'll choose.

[Chris Fong]

Note that SQLite only creates the sqlite_sequence table when you define an autoincrement column. It doesn't exist before then.

[Nick Dandoulakis]

@ZacharyYates, indeed, but it's already mentioned on the quoted text.

[dialex]

no such table sqlite_sequence is the error I get when I tried to run that command. What now?

[Nick Dandoulakis]

@dialex, Zachary Yates commented about that and it's also mentioned in my answer.

[Farhan Ibn Wahid]

can anyone tell how to do this for room ?

[Farhan Ibn Wahid]

can you tell me how to do this for room ?

[Androidcoder]

I'm getting: 'SQLiteException: no such column' error on second line of code with this.

[Huỳnh Ngọc Bang]

what do you mean @Psycho ? I can't understand :)

[Farhan Ibn Wahid]

actually I was asking how to do this in Room Database..... well the problem is solved for now

[prakashpun]

@Psycho can you tell me how you used this using Room database?

[GSandro_Strongs]

Great, fir me it worked, however I have a question. Why the size of the file is just the same despote we deleted the rows?

[Nick Dandoulakis]

@GSandro_Strongs, the size is the same because SQLite reuse the "deleted" space. There's the VACUUM command that rebuilds the database file to take the minimal amount of disk space.

[Ojonugwa Jude Ochalifu]

How does this help for an app in production?

[Clive Long]

This answer is very clear, easy to implement and easy to verify that it works