SQLite Forum

INTEGER PRIMARY KEY AUTOINCREMENT with UPSERT skips numbers
Login

INTEGER PRIMARY KEY AUTOINCREMENT with UPSERT skips numbers

(1) By anonymous on 2021-04-06 12:28:13 [link] [source]

Using the upsert-clause on a table created with an autoincrement primary key may skip numbers.

DROP TABLE IF EXISTS vocabulary;

CREATE TABLE vocabulary
(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  word TEXT UNIQUE,
  count INTEGER DEFAULT 1
);

INSERT INTO vocabulary(word) VALUES('one') ON CONFLICT(word) DO UPDATE SET count=count+1;
INSERT INTO vocabulary(word) VALUES('one') ON CONFLICT(word) DO UPDATE SET count=count+1;
INSERT INTO vocabulary(word) VALUES('one') ON CONFLICT(word) DO UPDATE SET count=count+1;

INSERT INTO vocabulary(word) VALUES('two') ON CONFLICT(word) DO UPDATE SET count=count+1;
INSERT INTO vocabulary(word) VALUES('two') ON CONFLICT(word) DO UPDATE SET count=count+1;
INSERT INTO vocabulary(word) VALUES('two') ON CONFLICT(word) DO UPDATE SET count=count+1;

SELECT * FROM vocabulary;

Expected result:

id word count
1 one 3
2 two 3

Actual result:

id word count
1 one 3
4 two 3

The conflicting inserts do increment the primary key counter even if the insert didn't happen.

(2) By Gunter Hick (gunter_hick) on 2021-04-06 13:07:06 in reply to 1 [link] [source]

The difference is that without the ON CONFLICT clause, the duplicate INSERT is "rolled back" and does not consume a rowid, whereas the ON CONFLICT clause makes the insert "commit", albeit as an update, and thus consume a rowid that was generated for the discarded row.

(3) By Richard Hipp (drh) on 2021-04-06 13:25:24 in reply to 1 [source]

Quoting from the AUTOINCREMENT documentation:

Note that "monotonically increasing" does not imply that the ROWID always increases by exactly one. One is the usual increment. However, if an insert fails due to (for example) a uniqueness constraint, the ROWID of the failed insertion attempt might not be reused on subsequent inserts, resulting in gaps in the ROWID sequence. AUTOINCREMENT guarantees that automatically chosen ROWIDs will be increasing but not that they will be sequential.

(4) By anonymous on 2021-04-06 13:57:19 in reply to 3 [link] [source]

I must have overlooked this part because I did look at the documentation before posting. I guess I was too concentrated on the ON CONFLICT part to look out.

In my case I can drop the AUTOINCREMENT and just use a normal PK which gives me the desired result but I had it in my test-case (where the holes where in the 1000s).