How to Reset Autoincrement Number Sequence in SQLite?

SQLite uses an internal table named sqlite_sequence to keep track of the largest ROWID. This table is created/initialized only when a table containing an AUTOINCREMENT column is created. Each row in the sqlite_sequence table has two columns:

1. name: stores the name of the table (case sensitive);
2. seq: stores the last incremented value of the table.

The contents of this table can be modified using UPDATE and DELETE statements. Therefore, we can use those to reset the sequence counter.

Updating the Table Number Sequence

You could simply update the seq column to the next ROWID you want. To update it to the largest ROWID, for example, you could do the following:

UPDATE `sqlite_sequence`
SET `seq` = (SELECT MAX(`col_name`) FROM 'table_name')
WHERE `name` = 'table_name';

Alternatively, setting the seq to 0 would work as well:

UPDATE `sqlite_sequence` SET `seq` = 0 WHERE `name` = 'table_name';

The reason this works is because SQLite uses the next available number for the ROWID when it encounters an invalid value (such as 0) or when there's a duplicate ROWID.

Deleting the Table Number Sequence

DELETE FROM `sqlite_sequence` WHERE `name` = 'table_name';

If a table sequence is deleted from the sqlite_sequence table (or it doesn't exist), SQLite would use the next available number for the ROWID when a new row is inserted in that table.

Viewing the Current Table Sequence

You can view the contents of the sqlite_sequence table simply by using a normal SELECT statement, for example:

SELECT * FROM `sqlite_sequence`;