In SQLite, when does a primary key column become an alias for the rowid, and when is AUTOINCREMENT worth adding?
answer
- every ordinary table has a hidden key
- the type name must match exactly
- BIGINT does not count
- DESC is a historical trap
- never reuse versus usually reuse
basics
~10 sA single-column primary key declared exactly INTEGER becomes the table's 64-bit rowid; INT or BIGINT do not. Ids are auto-assigned either way; AUTOINCREMENT only stops ids of deleted rows being reused, at extra cost.
solid answer
~40 sEvery ordinary SQLite table has a hidden 64-bit signed `rowid` that keys its B-tree. If the primary key is a single column whose declared type is exactly `INTEGER`, that column becomes an alias for the rowid: it must hold integers, a NULL or omitted value gets a fresh id (usually the largest plus one), and lookups use the table's own key. `INT`, `BIGINT` or `INTEGER PRIMARY KEY DESC` do not alias: you get an ordinary column, a separate unique index and a rowid that `VACUUM` may renumber. `AUTOINCREMENT` is not needed for automatic ids. It only guarantees that ids of deleted rows are never reused, tracked in `sqlite_sequence` at extra cost per insert. Add it when ids leave the database, for example ids synced to a server.
code
sql · 6 linesCREATE TABLE a(id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE b(id BIGINT PRIMARY KEY, name TEXT);
INSERT INTO a(name) VALUES ('x');
INSERT INTO b(name) VALUES ('x');
SELECT rowid, id FROM a; -- rowid 1, id 1: the same value
SELECT rowid, id FROM b; -- rowid 1, id NULL: an ordinary columngo deeper
Recall that ordinary tables have a hidden rowid and that INTEGER PRIMARY KEY makes a column that rowid, filled in automatically when you insert NULL.
Explain the exact-INTEGER rule, the DESC and BIGINT traps, what VACUUM can do to unaliased rowids, and what AUTOINCREMENT changes in id selection.
Spot an extra unique index from INT PRIMARY KEY in an inherited schema, and decide where id reuse would corrupt synced or cached references.
Set the team rule for SQLite primary keys: plain INTEGER PRIMARY KEY by default, AUTOINCREMENT only where ids leave the database, and why.
## Every ordinary table has a rowid Apart from virtual tables and tables declared `WITHOUT ROWID`, every SQLite table is a **rowid table**: each row has a unique, non-NULL, 64-bit signed integer key, and that key is what the table's B-tree is ordered by. You can read it as `rowid`, `oid` or `_rowid_`, unless a declared column already uses one of those names. Looking a row up by rowid, or scanning a rowid range, is around twice as fast as the same search through another primary key or index, because it goes straight to the table's own B-tree. A declared PRIMARY KEY on a rowid table is usually **not** that key. It is enforced like a UNIQUE constraint, through a separate index, while the rowid remains the real key. The exception is the integer primary key. ## When a column becomes the rowid | Declaration | Alias for the rowid? | |---|---| | `id INTEGER PRIMARY KEY` | yes | | `id integer primary key ASC` | yes (case and ASC do not matter) | | `id INTEGER, PRIMARY KEY(id DESC)` | yes (table constraint) | | `id INTEGER PRIMARY KEY DESC` | **no** (historical quirk) | | `id INT PRIMARY KEY` | no | | `id BIGINT PRIMARY KEY` | no | | `PRIMARY KEY(a, b)` | no (more than one column) | The rule is narrow: a **single-column** primary key whose declared type name is **exactly `INTEGER`**, in any mix of upper and lower case. Other integer names, such as `INT`, `BIGINT`, `SHORT INTEGER` or `UNSIGNED INTEGER`, give an ordinary column with INTEGER affinity plus a unique index, next to a separate hidden rowid. The `INTEGER PRIMARY KEY DESC` exception exists because of a bug in early versions that was kept to avoid breaking existing databases. The same rule applies in STRICT tables, where `INTEGER PRIMARY KEY` aliases the rowid and `INT PRIMARY KEY` does not. ## What the alias changes - **It must hold integers.** Writing a BLOB, or text or a real that cannot be converted to an integer without loss, fails with a `datatype mismatch` error (`SQLITE_MISMATCH`). The text `'42'` is converted and accepted. - **NULL means "assign one".** Inserting NULL, or leaving the column out, gives the row a new id automatically: normally one more than the largest rowid in the table, or 1 if the table is empty. - **It is stable.** `VACUUM` may change the rowids of tables that have no explicit `INTEGER PRIMARY KEY`, so an application that stores bare rowids as references should declare the alias. A foreign key also cannot reference the rowid itself; it must reference a named column. - **It saves an index.** With `BIGINT PRIMARY KEY` the table stores the id twice, once as a column and once in its unique index, and every lookup by id goes through that index first. A related quirk: in an ordinary rowid table, a primary key that is not the integer primary key can contain NULLs, because it is really just a UNIQUE constraint. Add `NOT NULL` to each primary key column, or use STRICT or WITHOUT ROWID tables, which disallow NULLs in the key. ## AUTOINCREMENT: a different guarantee, not automatic ids Automatic ids need no keyword. The default algorithm picks one more than the current largest rowid, so if you delete the row holding the largest id, that id can be handed out again. If the largest possible value, 9223372036854775807, is ever used, SQLite starts trying random unused values and fails with `SQLITE_FULL` if it cannot find one. `INTEGER PRIMARY KEY AUTOINCREMENT` changes the algorithm: 1. A new id is at least one more than the largest id that **has ever existed** in the table, so ids of deleted rows are never reused. 2. The high-water mark lives in an internal table, `sqlite_sequence`, which SQLite creates along with the first AUTOINCREMENT table and updates whenever an insert raises the maximum. 3. Once the maximum id has been used, further inserts fail with `SQLITE_FULL`. 4. Ids still have gaps: a failed insert can consume one, and ids from rolled-back transactions can be reused. The documentation advises avoiding it unless needed, because the bookkeeping costs CPU, memory, disk space and I/O on every insert. It is allowed only on an `INTEGER PRIMARY KEY` column and not in WITHOUT ROWID tables. It is worth it when an id escapes the database: ids synced to a server, written into logs or cached by clients, where a reused id would point at the wrong record. Engineers coming from MySQL often add it by habit, but SQLite's AUTOINCREMENT does not work like MySQL's, and a plain `INTEGER PRIMARY KEY` already numbers rows automatically.
- In SQLite, what happens when an UPDATE sets an INTEGER PRIMARY KEY column to 'abc'?The statement fails with a `datatype mismatch` error and is aborted. A rowid alias can hold only integers, so a BLOB, NULL, or any text or real that cannot be converted to an integer without loss is rejected, unlike in an ordinary column, which would store the text.
- With a plain SQLite INTEGER PRIMARY KEY, can a deleted row's id come back?Yes, in one case: new ids are normally one more than the current largest rowid, so deleting the row with the largest id lets the next insert reuse it. Ids below the maximum are not refilled by that algorithm unless the largest possible id has been used. If any reuse would be harmful, for example ids already sent to a server, declare `AUTOINCREMENT`.
A plain INTEGER PRIMARY KEY works like a queue that hands each newcomer one more than the highest number still in the room: if the holder of the top number leaves, the next arrival gets that number again. AUTOINCREMENT is a ticket roll that remembers the last ticket ever issued, so a number that has been handed out and kept is never printed again.
- BIGINT PRIMARY KEY is the same as INTEGER PRIMARY KEY in SQLite
- Without AUTOINCREMENT, SQLite does not assign ids automatically
- AUTOINCREMENT makes inserts faster in SQLite
- AUTOINCREMENT guarantees ids without gaps
- A rowid never changes once assigned, even without an INTEGER PRIMARY KEY