SQLite Reset Primary Key Field

user_189478 0
Viewed 126772

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.

sqlite sql
5 Answers

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.

user_26543940

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_10259350

If you are working with python and you want to delete all records from some table and reset AUTOINCREMENT.

You have this table

tables_connection_db.execute("CREATE TABLE MY_TABLE_DB (id_record INTEGER PRIMARY KEY AUTOINCREMENT, value_record real)")

So if you had added some records

connection_db=sqlite3.connect("name_file.db")
tables_connection_db=connection_db.cursor()

tables_connection_db.execute("DELETE FROM  MY_TABLE_DB ") # delete records
connection_db.commit()

name_table="MY_TABLE_DB"
tables_connection_db.execute("UPDATE sqlite_sequence SET seq=1 WHERE name=? ",(name_table,))
connection_db.commit()

connection_db.close()
user_176814250
Related