sqlt-sqlsqliteidentity-column

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

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.

Solution

  • 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.