How to Reset an Auto-Incremented Primary Key in SQLite?

Resetting the Auto-Incrementing Primary Key in SQLite

Simply deleting data from an SQLite table using DELETE FROM doesn't reset the auto-incrementing primary key back to 1. To achieve this, you need a two-step process:

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

The first statement removes all rows from your table. The second, and crucial, statement deletes the entry from the sqlite_sequence table. This system table tracks the highest ROWID for each table with an auto-increment column.

Understanding SQLite's Autoincrement Mechanism

SQLite uses sqlite_sequence to manage auto-incrementing primary keys. When a new row is added to a table with an AUTOINCREMENT column, SQLite consults sqlite_sequence to find the largest existing ROWID and increments it.

Therefore, directly manipulating sqlite_sequence affects the auto-increment sequence. Proceed with caution and fully understand the potential impact before making any changes.

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