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.

score 309 · Accepted Answer · answered Oct 21 '09 at 16:03

309

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.

answered Oct 21 '09 at 16:03

Nick Dandoulakis

42,588
16
104
136

14

Just a small side note: Table name in where clause is case sensitive – 321X Aug 08 '11 at 20:07
7

another way is updating sqlite_sequence table. update sqlite_sequence set seq = 0 where name='' This will not reset the rowid of sqlite_sequence table. – binary Mar 13 '12 at 09:52
@binary, thanks for mentioning an alternative way :) I'd like to note that SQLite reuses "deleted" space, so it doesn't really make much of a difference which solution we'll choose. – Nick Dandoulakis Mar 13 '12 at 10:27
1

Note that SQLite only creates the sqlite_sequence table when you define an autoincrement column. It doesn't exist before then. – Zachary Yates Jun 11 '14 at 16:14
@ZacharyYates, indeed, but it's already mentioned on the quoted text. – Nick Dandoulakis Jun 11 '14 at 18:18
`no such table sqlite_sequence` is the error I get when I tried to run that command. What now? – dialex Nov 16 '15 at 16:16
@dialex, Zachary Yates commented about that and it's also mentioned in my answer. – Nick Dandoulakis Nov 16 '15 at 16:35
can anyone tell how to do this for room ? – Farhan Ibn Wahid Aug 03 '19 at 02:52
I'm getting: 'SQLiteException: no such column' error on second line of code with this. – Androidcoder Aug 13 '19 at 16:54
Great, fir me it worked, however I have a question. Why the size of the file is just the same despote we deleted the rows? – GSandro_Strongs Dec 03 '20 at 14:35
1

@GSandro_Strongs, the size is the same because SQLite reuse the "deleted" space. There's the [VACUUM command](https://sqlite.org/lang_vacuum.html) that rebuilds the database file to take the minimal amount of disk space. – Nick Dandoulakis Dec 03 '20 at 15:49

score 86 · Answer 2 · answered Apr 09 '15 at 02:54

86

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

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

answered Apr 09 '15 at 02:54

Huỳnh Ngọc Bang

1,572
1
19
22

can you tell me how to do this for room ? – Farhan Ibn Wahid Aug 03 '19 at 02:54
2

what do you mean @Psycho ? I can't understand :) – Huỳnh Ngọc Bang Oct 11 '19 at 07:52
actually I was asking how to do this in Room Database..... well the problem is solved for now – Farhan Ibn Wahid Oct 13 '19 at 06:00
@Psycho can you tell me how you used this using Room database? – prakashpun Jan 17 '20 at 11:54

score 6 · Answer 3 · answered Nov 09 '13 at 16:33

6

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.

answered Nov 09 '13 at 16:33

Daniel Lefebvre

364
4
11

How does this help for an app in production? – Ojonugwa Jude Ochalifu Nov 18 '21 at 07:31
This answer is very clear, easy to implement and easy to verify that it works – Clive Long Feb 16 '22 at 09:30

score 0 · Answer 4 · edited Dec 18 '13 at 10:55

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

score 0 · Answer 5 · edited Dec 26 '21 at 23:44

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

score 0 · Answer 6 · answered Apr 02 '23 at 16:40

I realize this answer is coming very late, but I am building a .Net Maui app and faced the same issue. I am using the sqlite-net-pcl nuget and solved this problem by simply dropping the table and creating it again. The primary key is set back to 1.

       database.DropTableAsync<Waypoint>();
       database.CreateTableAsync<Waypoint>().Wait();

SQLite Reset Primary Key Field

6 Answers6

Linked

Related