33 Votes


Question by Guest | Last update on 2021-05-13 | Created on 2017-05-15

I have an ID-column in my SQLite database in which I am automatically increasing the integer value with the help of AUTOINCREMENT as soon as a new record is inserted into the table.

This is working like I want. However, I am frequently deleting this table and as soon as I am writing new data into the empty table again, the counting is not starting from the beginning but from the last integer value the column had before.

Is it possible to reset this AUTOINCREMENT value so that it will start at 0 respectively 1 again?

10Best Answer16 Votes

SQLite is storing the last ROWID within the table SQLITE_SEQUENCE that is automatically managed by SQLite. The values within this table are kept even if you delete or empty other tables.

So, you not only have to delete your table itself, but also the information stored about your table from the SQLITE_SEQUENCE meta table. You can do that for example like that:

DELETE FROM sqlite_sequence WHERE name = 'tab';

Assuming the table you want to delete is called "tab". The first query would delete all data from your table, the second line would delete the entries from SQLITE_SEQUENCE. When adding new data after that, the counting will start from the beginning again.

UPDATE sqlite_sequence SET seq = 10 WHERE name = 'tab';

Alternatively, you can also change the SQLITE_SEQUENCE values individually. For instance, the query above would let the counting begin at 10.
2017-05-15 at 19:05

ReplyPositive Negative

Related Topics

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.


Ask your own question or write your own article on askingbox.com. That’s how it’s done.