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:
name: stores the name of the table (case sensitive);
seq: stores the last incremented value of the table.
The contents of this table can be modified using
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
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
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`;
This post was published by Daniyal Hamid. Daniyal currently works as the Head of Engineering in Germany and has 20+ years of experience in software engineering, design and marketing. Please show your love and support by sharing this post.