SQLite Reset Primary Key Field

Question

I have a few tables in SQLite and I am trying to figure out how to reset the auto-incremented database field    I read that DELETE FROM tablename should delete everything and reset the auto-incremement 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  integer 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

User · Accepted Answer

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

User · Answer

If you want to reset every RowId via content provider try this  rowCounter 1  do                           rowId   cursor getInt 0               ContentValues values              values   new ContentValues                values put Table Health COLUMN ID                         rowCounter               updateData2DB context  values  rowId               rowCounter             while  cursor moveToNext      public static void updateData2DB Context context  ContentValues values  int rowId        Uri uri      uri   Uri parseContentProvider CONTENT URI HEALTH         rowId       context getContentResolver   update uri  values  null  null

User · Answer

You can reset by update sequence after deleted rows in your-table  UPDATE SQLITE SEQUENCE SET SEQ 0 WHERE NAME  table name

User · Answer

As an alternate option  if you have the Sqlite Database Browser and are more inclined to a GUI solution  you can edit the sqlite sequence table where field name is the name of your table  Double click the cell for the field seq and change the value to 0 in the dialogue box that pops up

[sql] SQLite Reset Primary Key Field

Examples related to sql

Examples related to sqlite