How to reseed an an auto increment column in a SQLite database?
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.
DELETE FROM MyTableNameselect *from SQLITE_SEQUENCEupdate SQLITE_SEQUENCE set seq = 0where name ='MyTableName'