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

Resetting Auto-Incrementing Primary Keys in SQLite Databases

This guide explains how to reset the auto-incrementing primary key sequence in an SQLite table. This involves removing all existing rows and resetting the internal counter.

The Solution:

To reset the primary key, execute these two SQL commands:

<code class="language-sql">-- Remove all data from the table
DELETE FROM your_table;

-- Reset the auto-increment sequence
DELETE FROM sqlite_sequence WHERE name = 'your_table';</code>

Replace your_table with the actual name of your table.

Explanation:

SQLite maintains the sqlite_sequence table to track the highest auto-increment value for each table. Simply deleting rows doesn't reset this counter; it resumes from the last used value. Therefore, deleting the table's entry from sqlite_sequence ensures the next inserted row receives a primary key value of 1.

Important Considerations:

• Data Loss: This process deletes all data from the specified table. Back up your data before proceeding.
• Hidden Table: The sqlite_sequence table is a system table and isn't normally displayed in standard schema views.
• Careful Modification: Incorrectly modifying sqlite_sequence can disrupt auto-increment behavior. Use these commands precisely as shown.

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