2 2 Votes

SQLite: Reset AUTOINCREMENT

Question by Guest | 15/05/2017 at 18:06

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?

ReplyPositiveNegative

SmartUser

Show Profile | Message
Avatar
6Best Answer
12 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 tab;
DELETE FROM sqlite_sequence WHERE name = 'tab';

Assuming the table 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.
15/05/2017 at 19:05

ReplyPositive Negative
Reply

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.

Participate

Ask your own question or write your own articles on askingbox.com. How to do.