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 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`;
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.