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?

Thanks! - Maddy

Answer 1

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 2

ALTER TABLE tbl AUTO_INCREMENT = 0;

Answer 3

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: http://stackoverflow.com/questions/1601697/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.