Reset sqlite squence autoincrement

Question

I want to reset autoincrement in my table. I tried to do this, but when I created and inserted a new row an id is not 1, but was just autoincremented .

@Query("delete from sqlite_sequence where name='approval';")
void delete();


@Query("DELETE FROM approval")
void nukeTable();

How can I reset autoincrement to start from 1 again?

You should delete the table and create a new one with the same one — TK-421, Jul 23 '19 at 11:24
Possible duplicate of [SQLite Reset Primary Key Field](https://stackoverflow.com/questions/1601697/sqlite-reset-primary-key-field) — TK-421, Jul 23 '19 at 11:50

score 0 · Answer 1 · answered Jul 23 '19 at 11:24

0

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

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

Reference

answered Jul 23 '19 at 11:24

ASHKARAN

365
1
13

score 0 · Answer 2 · answered Jul 23 '19 at 14:10

I don't know what Room may be bringing to the mix, but at the pure Sqlite3 level, both deleting the record from sqlite_sequence and setting its value to zero does the trick, provided all records are deleted from the table in question.

autoinc.sql3

create table ai ( id integer primary key autoincrement, value ) ;

select 'Normal insertion' ;
insert into ai(value) values ( 1 ), ( 42 ) ;
select * from ai ;

select 'Delete sequence only' ;
delete from sqlite_sequence where name = 'ai' ;
insert into ai(value) values ( 1 ), ( 42 ) ;
select * from ai ;

select 'Delete sequence and data' ;
delete from sqlite_sequence where name = 'ai' ;
delete from ai ;
insert into ai(value) values ( 1 ), ( 42 ) ;
select * from ai ;

Running this with sqlite3 < autoinc.sql3 gives the following output:

Normal insertion
1|1
2|42
Delete sequence only
1|1
2|42
3|1
4|42
Delete sequence and data
1|1
2|42

Exactly the same results are obtained if instead of deleting the record from the sequence table we reset it to zero using update sqlite_sequence set seq=0 where name = 'ai' ;.

However, as the results above show, if there are any records in the table, numbering is persisted (presumably with the highest-used-plus-one that a non autoincrement primary key uses).

Could it be something is inserting records between deleting/resetting the sequence and deleting the records from approval? Assuming Room supports it, try both operations within a single transaction.

Reset sqlite squence autoincrement

2 Answers2