6

I have a SQLite table, with a few columns, including an "ID" column which is INTEGER PRIMARY KEY.

When I insert, this value increments as expected.

However, when I delete all rows, this column's value reverts to 1 on the next insert.

Is there a way to have the column value continue to increment from the highest value before deletion?

For example:

  • (empty table)
  • insert
  • insert

(ID value of 2nd row is 2, next insert would be 3)

  • delete all rows
  • insert

(ID value for this latest row is 1, but I want it to be 3)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
dlchambers
  • 3,511
  • 3
  • 29
  • 34

2 Answers2

9

If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuse of ROWIDs over the lifetime of the database. In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.

https://www.sqlite.org/autoinc.html

ravenspoint
  • 19,093
  • 6
  • 57
  • 103
  • This does seem to do the trick. I was put off by the "should be avoided if not strictly needed. It is usually not needed" on item 1 of that page. – dlchambers Oct 15 '15 at 11:56
0

What version of SQLite are you using? I am using SQLite version 3.8.11.1. Primary key values are not resetting.

Create table and insert some data

sqlite> create table test (id integer primary key autoincrement, name varchar(20));
sqlite> select * from test;
sqlite> insert into test (name) values ('hello1');
sqlite> insert into test (name) values ('hello2');
sqlite> select * from test;
id          name
----------  ----------
1           hello1
2           hello2
sqlite> select * from sqlite_sequence;
name        seq
----------  ----------
test        2

In an autoincrement column, sqlite keeps information of sequence in sqlite_sequence internal table that you can query also.

Delete data. Notice that sequence stays as-is.

sqlite> delete from test;
sqlite> select * from sqlite_sequence;
name        seq
----------  ----------
test        2
sqlite> select * from test;
sqlite> insert into test (name) values ('world1');
sqlite> insert into test (name) values ('world2');
sqlite> select * from test;
id          name
----------  ----------
3           world1
4           world2
sqlite> select * from sqlite_sequence;
name        seq
----------  ----------
test        4

Based on this it appears that sequence numbers are staying as-is after deletion. I am using the autoincrement keyword.

zedfoxus
  • 35,121
  • 5
  • 64
  • 63