Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQLite: *prevent* PRIMARY KEY value from resetting after delete all rows [duplicate]

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)

like image 931
dlchambers Avatar asked Oct 14 '15 20:10

dlchambers


1 Answers

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

like image 165
ravenspoint Avatar answered Oct 03 '22 02:10

ravenspoint