-1

I have a table which requires unique IDs for each row, and which requires them to be stable if the database is rebuilt (so I can't just use rowid), but does not require that they be unique over the lifetime of the database as AUTOINCREMENT ensures. As per the documentation, this suggests I should not use AUTOINCREMENT, but I'm not aware of and have been unable to discover any other option for automatically assigning unique integer IDs to rows except by entering them manually, which kind of defeats the purpose.

Is there a way to have an integer primary key in a SQLite table with values that are automatically generated without using AUTOINCREMENT? Alternately, are the concerns raised in the documentation concerning AUTOINCREMENT either overblown or inapplicable to my current situation? I do not require that this part of the database be especially performant.

Improve this question
1
  • 1
    Pleaser be specific about the part of the documentation you are referring to, via a quote and a link to the relevant parts. Commented May 5, 2023 at 14:18

2 Answers 2

Reset to default
1

The documentation for SQLite Autoincrement says:

[...]

  1. On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually one more than the largest ROWID currently in use. This is true regardless of whether or not the AUTOINCREMENT keyword is used.

  2. 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.

This means that AUTOINCREMENT is not required for rows to be auto-incremented. It only determines whether IDs of deleted rows will be reused or not.

It also means that the Id is only generated automatically if you don't supply one at INSERT.

For you , this means:

  1. that you don't need to specify AUTOINCREMENT.
  2. your original IDs will be preserved when the database is being rebuilt by using INSERT commands and explicitly specifying the Id!
  3. when working normally with the database, you can ommit the Id in INSERTs and SQLite will generate one for you. For this to happen, declare the Id column as either ROWID or INTEGER PRIMARY KEY.
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

-1

A silly thought occurred to me just after posting this question, and it turns out that contrary to what I thought primary integer keys are in fact automatically assigned, but only if defined specifically as an INTEGER PRIMARY KEY. I had believed otherwise because I had instead defined one as an INT PRIMARY KEY in the database I was testing on, which I had expected incorrectly to behave the same way. I would still be interested in learning whether the concerns regarding AUTOINCREMENT in the documentation are warranted.

Improve this answer

1 Comment

Please edit your original question. Do not post additional information as an answer. This site does not work like a chat forum.

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.