Reset the row number count in SQLite3/MySQL

Question

I am using SQLite3. I load a table with say 30 rows using integer as Primary ID and it auto-increments.

Now I delete all the rows from the table and then, reload some new information onto the table.

Problem is: the row count (my PrimaryID) now starts with 31. Is there any way that I can start loading new rows from the number 1 onwards?

Answer 1

SQLite

Use:

DELETE FROM your_table;    
DELETE FROM sqlite_sequence WHERE name = 'your_table';

Documentation

SQLite keeps track of the largest ROWID that a table has ever held using the special SQLITE_SEQUENCE table. The SQLITE_SEQUENCE table is created and initialized automatically whenever a normal table that contains an AUTOINCREMENT column is created. The content of the SQLITE_SEQUENCE table can be modified using ordinary UPDATE, INSERT, and DELETE statements. But making modifications to this table will likely perturb the AUTOINCREMENT key generation algorithm. Make sure you know what you are doing before you undertake such changes.

Found the answer on SO: SQLite Reset Primary Key Field

MySQL

Use:

ALTER TABLE tbl AUTO_INCREMENT = 1;

In either case, the database doesn't care if the id numbers are sequencial - only that the values are unique. If users never see the primary key value (they shouldn't, because the data can change & won't always be at that primary key value), I wouldn't bother with these options.

Answer 2

For MySQL:

Use TRUNCATE TABLE tablename to empty the table (delete all records) and reset auto increment count.

You can also use ALTER TABLE tablename AUTO_INCREMENT = 0; if you just want to reset the count.

For SQLite:

DELETE FROM tablename;
DELETE FROM SQLITE_SEQUENCE WHERE name='tablename';

---

References

SQLite AutoIncrement
MySQL AutoIncrement

Answer 3

For SQLite use (not need to delete and create the table)

UPDATE SQLITE_SEQUENCE SET SEQ=0 WHERE NAME='table_name';

For MySql use

ALTER TABLE table_name AUTO_INCREMENT = 1;

Answer 4

You should not use AUTOINCREMENT in this case. Simply define your primary key as INTEGER PRIMARY KEY and the count will be reset to 1 after a DELETE FROM query. Without AUTOINCREMENT, the default behaviour will still be an automatic increment of the primary key as long as you don't run out of space in your table (in that case, old - deleted - values will be reused).

More information available in the SQLite Autoincrement document.

Answer 5

ALTER TABLE tbl AUTO_INCREMENT = 0;