SQLite Reset Primary Key Field

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.

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.

You can reset by update sequence after deleted rows in your-table

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

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.

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);
}

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()