How to reseed an an auto increment column in a SQLite database?

Question

Is it possible to reseed an auto increment column in a SQLite database, and if so, how is this done?

ie. The equivalent of DBCC CHECKIDENT ('MyTable', RESEED, 1) in SQL Server.

Answer 1

In SQLite there is a table named SQLITE_SEQUENCE, which tracks the largest RowId value that a table has. You can do insert, updates and deletes on this table. For example, to mimic similar functionality as the TRUNCATE TABLE statement SQL Server you could something like:

DELETE FROM MyTableName;
DELETE FROM SQLITE_SEQUENCE WHERE NAME = 'MyTableName';

In the above example all data from MyTableName is removed, and the auto increment rowid is reset by removing the value from the SQLITE_SEQUENCE table. See the documentation for AUTOINCREMENT for more information.

Answer 2

DELETE 
FROM MyTableName

select *
from SQLITE_SEQUENCE

update SQLITE_SEQUENCE 
set seq = 0
where name ='MyTableName'

Answer 3

Alternatively you could export a SQL file from the SQLite database. Then edit the generated SQL file and update the appropriate entries to the desired IDs or delete the INSERT statements. After this create a new empty database and seed it with the adjusted SQL file. The highest ID + 1 will then be the ID for a new entry...

With this way you can decide, which entries to keep and which entries should be removed as a non-destructive or even a more flexible approach.

For me it worked - clean and easy. ;)