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.