SQLite Reset Primary Key Field

Question

I have a few tables in SQLite and I am trying to figure out how to reset the autoincremented database field. I read that: "DELETE FROM tablename" should delete everything and reset the autoincremement field back to 0 but when I do this it just deletes the data. When a new record is inserted the autoincrement picks up where it left off before the delete.

My Ident field properties are as follows:

Field Type = interger

Field Flags = PRIMARY KEY, AUTOINCREMENT, UNIQUE

Does it matter I built the table in SQLite Maestro and I am executing the Delete statement in SQLite Maestro as well?

Any help would be great.

Thanks.

Answer 1

Try this:

delete from your_table;    
delete from sqlite_sequence where name='your_table';

SQLite Autoincrement

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.