How to Reset Auto-Incrementing Primary Keys in SQLite?

SQLite auto-increment primary key reset method

Resetting auto-incrementing primary key values ​​is a common problem when working with SQLite databases. This article will explore this and provide effective solutions.

Scenarios and questions

User attempted to reset auto-increment fields for multiple tables in a SQLite database. However, using the DELETE FROM command to delete all data does not reset the field to 0. In contrast, auto-increment continues to increment from the last value after data is deleted.

Detailed explanation

This problem arises because SQLite uses a separate table named SQLITE_SEQUENCE to keep track of the highest ROWID value in each table. When a new record is inserted, the AUTOINCREMENT algorithm checks the SQLITE_SEQUENCE table to determine the next available primary key value.

Deleting data from the main table will only delete the associated rows. SQLITE_SEQUENCE The table remains unchanged, causing the auto-increment counter to continue incrementing from its last value.

Solution

To reset the primary key field to the desired value, execute the following two statements:

<code class="language-sql">DELETE FROM your_table;
DELETE FROM sqlite_sequence WHERE name='your_table';</code>

The first statement deletes all data in the main table. The second statement deletes the entry for that table in the SQLITE_SEQUENCE table, effectively resetting the highest ROWID value to 0.

Other notes

• Modification of the SQLITE_SEQUENCE table should be done with caution.
• Be sure to back up your database before making any changes.
• The solutions provided work for both manually created tables and tables created using tools like SQLite Maestro.

The above is the detailed content of How to Reset Auto-Incrementing Primary Keys in SQLite?. For more information, please follow other related articles on the PHP Chinese website!