0

If you delete the rows on the end of the table, the autoincrement resets:

sqlite> CREATE TABLE my_table (
    id INTEGER PRIMARY KEY
);
...
sqlite> SELECT * FROM my_table;
1
2
3
sqlite> DELETE FROM my_table WHERE id = 3;
sqlite> SELECT * FROM my_table;
1
2
sqlite> INSERT INTO my_table DEFAULT VALUES;
sqlite> SELECT * FROM my_table;
1
2
3

I want the last command to return

1
2
4

Is this possible?

1 Answer 1

1

You'll get such behaviour if you defile primary key as AUTOINCREMENT:

CREATE TABLE my_table (
  id INTEGER PRIMARY KEY AUTOINCREMENT, ..

Demo: https://dbfiddle.uk/U0Y3H-9w

Docs on AUTOINCREMENT: https://www.sqlite.org/autoinc.html

the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.