Does PRIMARY KEY constraint defined on table level guarantees AUTOINCREMENT and no values reuse?

Question

Consider the following table definition:

CREATE TABLE names (
    id INTEGER,
    name TEXT NOT NULL,
    PRIMARY KEY (id)
)

Does it guarantee that the id will be auto-incremented for every new insert AND that the values for deleted rows will not be reused?

I looked up in the documentation for Sqlite3, but couldn't find the answer.

Missing rowids can be reused after the largest one in the table is a very large number indeed (or if the biggest one in the table is deleted), unless `AUTOINCREMENT` is used to change how a new rowid is calculated. See https://www.sqlite.org/autoinc.html for details. — Shawn, Mar 28 '19 at 13:32
Yes, I have read that document, but it didn't answer my question. — Andrey Panin, Mar 28 '19 at 18:16

score 1 · Accepted Answer · answered Mar 28 '19 at 20:08

id INTEGER PRIMARY KEY on it's own guarantees (requires) a unique integer value and will if no value is specifically assigned provide one until the highest value has reached the highest allowed value for a 64 bit signed integer (9223372036854775807) after which an unused value may be found and applied.

With AUTOINCREMENT there is a guarantee (if not circumvented) of always providing a higher value BUT if 9223372036854775807 is reached instead of allocating an unused number an SQLITE_FULL error will result. That is the only difference from the point of view of what number will be assigned.

Neither guarantees a monotonically increasing value.

Without AUTOINCREMENT the calculation/algorithm is equivalent to

1 + max(rowid) and if the value is greater than 9223372036854775807 an attempt is made to find an unused and therefore lower value.
- I've not seen that anyone has come across the situation where a random unused value has not been assigned.

With AUTOINCREMENT the calculation/algorithim is

the greater of 1 + max(rowid) or SELECT seq FROM sqlite_sequence WHERE name = 'the_table_name_the_rowid_is_being_assigned_to' and if the value is greater than 9223372036854775807 then SQLITE_FULL ERROR.
noting that either way there is the possibility that the max rowid is for a row that eventually doesn't get inserted and therefore the potential for gaps.

The answer is perhaps best put as: it's best/recommended to use the id column solely for it's intended purpose, that of efficiently identifying a row and not as a means of handling other data requirements, and if done so, there there is no need for AUTOINCREMENT (which has overheads)

In short

Does it guarantee that the id will be auto-incremented

NO

values for deleted rows will not be reused?

NO for the given code

for :-

CREATE TABLE names (id INTEGER PRIMARY KEY AUTOINCREMENT name TEXT NOT NULL)

again NO as if 9223372036854775807 is reached then an SQLITE_FULL error will result, otherwise YES.

So really AUTOINCREMENT is only really relevant (if the id used as expected/intended) when the 9223372036854775807'th row has been inserted.

Perhaps consider the following :-

DROP TABLE IF EXISTS table1;
DROP TABLE IF EXISTS table2;

CREATE TABLE IF NOT EXISTS table1 (id INTEGER PRIMARY KEY, somecolumn TEXT);
CREATE TABLE IF NOT EXISTS table2 (id INTEGER PRIMARY KEY AUTOINCREMENT, somecolumn TEXT);

INSERT INTO table1 VALUES (9223372036854775807,'blah'); 
INSERT INTO table2 VALUES (9223372036854775807,'blah');
INSERT INTO table1 (somecolumn) VALUES(1),(2),(3); 
SELECT * FROM table1;
INSERT INTO table2 (somecolumn) VALUES(1),(2),(3);

This creates the two similar tables, the only difference being the use of AUTOINCREMENT. Each has a row inserted with the highest allowable value for the id column.

An attempt is then made to insert 3 rows where the id will be assigned by SQLite.

3 rows are inserted into the table without AUTOINCREMENT but no rows are inserted when AUTOINCREMENT is used. as per :-

CREATE TABLE IF NOT EXISTS table1 (id INTEGER PRIMARY KEY, somecolumn TEXT)
> OK
> Time: 0.098s


CREATE TABLE IF NOT EXISTS table2 (id INTEGER PRIMARY KEY AUTOINCREMENT, somecolumn TEXT)
> OK
> Time: 0.098s


INSERT INTO table1 VALUES (9223372036854775807,'blah')
> Affected rows: 1
> Time: 0.094s


INSERT INTO table2 VALUES (9223372036854775807,'blah')
> Affected rows: 1
> Time: 0.09s


INSERT INTO table1 (somecolumn) VALUES(1),(2),(3)
> Affected rows: 3
> Time: 0.087s


SELECT * FROM table1
> OK
> Time: 0s


INSERT INTO table2 (somecolumn) VALUES(1),(2),(3)
> database or disk is full
> Time: 0s

The result of the SELECT for table1 (which may differ due to randomness) was :-

Does PRIMARY KEY constraint defined on table level guarantees AUTOINCREMENT and no values reuse?

1 Answers1

In short