ROWID Tables and INTEGER PRIMARY KEY from First Principles

Learning outcomes

Most ordinary SQLite tables are rowid tables. That phrase describes physical identity, not a column you necessarily declared. Every row in a rowid table has a unique non-NULL signed 64-bit integer key used by SQLite's table b-tree. This lesson makes that rule observable and then connects it to the special INTEGER PRIMARY KEY declaration.

A rowid table has an internal integer key even if you never declared one

SQLite defines a rowid table as a table that is neither virtual nor declared WITHOUT ROWID. Most tables you create with ordinary CREATE TABLE syntax therefore have a rowid. The rowid is the key used to locate the row in the underlying table b-tree.

DROP TABLE IF EXISTS observation;CREATE TABLE observation (    label TEXT NOT NULL);INSERT INTO observation(label)VALUES ('alpha'), ('beta'), ('gamma');SELECT rowid, _rowid_, oid, labelFROM observationORDER BY rowid;

With a new table and ordinary inserts, you will typically see rowids 1, 2, and 3. The important rule is not that numbering “starts at one”; the rule is that each current row has a unique signed 64-bit integer rowid. Applications should not build business meaning around incidental numbers.

Exactly INTEGER PRIMARY KEY creates a rowid alias

In an ordinary rowid table, a single-column primary key whose declared type is exactly INTEGER becomes another name for the rowid. Case does not matter, but spelling does. INT PRIMARY KEY, BIGINT PRIMARY KEY, and other integer-affinity names are ordinary columns backed by uniqueness machinery; they are not rowid aliases.

DROP TABLE IF EXISTS ipk_demo;CREATE TABLE ipk_demo (    event_id INTEGER PRIMARY KEY,    message  TEXT NOT NULL);INSERT INTO ipk_demo(message) VALUES ('opened');INSERT INTO ipk_demo(event_id, message) VALUES (50, 'manual id');SELECT event_id, rowid, _rowid_, oid, messageFROM ipk_demoORDER BY event_id;

For every row, event_id and the three built-in rowid aliases refer to the same value. An insert of SQL NULL into the integer primary-key position also asks SQLite to allocate a rowid automatically.

Inspect physical consequences instead of memorizing slogans

Use PRAGMA index_list to see whether SQLite created an automatic index for a primary-key constraint. The exact INTEGER PRIMARY KEY case does not need one because the rowid itself enforces identity.

DROP TABLE IF EXISTS exact_integer;DROP TABLE IF EXISTS merely_int;CREATE TABLE exact_integer (    id INTEGER PRIMARY KEY,    payload TEXT);CREATE TABLE merely_int (    id INT PRIMARY KEY,    payload TEXT);PRAGMA index_list('exact_integer');PRAGMA index_list('merely_int');SELECT type, name, tbl_name, sqlFROM sqlite_schemaWHERE tbl_name IN ('exact_integer','merely_int')ORDER BY tbl_name, type, name;

The merely_int table should expose an automatic uniqueness index for its primary key, while exact_integer does not need a separate primary-key index. This is why “PRIMARY KEY always means a separate index” is an inaccurate mental model for SQLite.

rowid, _rowid_, and oid can be shadowed by declared columns

The built-in names rowid, _rowid_, and oid work only when a declared column has not taken that same name. This makes clever schemas fragile.

DROP TABLE IF EXISTS shadow_demo;CREATE TABLE shadow_demo (    rowid   TEXT,    payload TEXT);INSERT INTO shadow_demo(rowid, payload)VALUES ('business-row-A', 'sample');SELECT rowid, _rowid_, oid, payloadFROM shadow_demo;

Here, rowid refers to the text column you declared, while _rowid_ and oid still expose the internal integer. If you declare all three special names, none of those spellings can reach the hidden rowid. Production judgment: do not choose these names for ordinary application columns.

Normal automatic allocation is convenient, not a business sequence

When an insert omits the rowid or supplies NULL to an INTEGER PRIMARY KEY, SQLite normally chooses an unused integer, usually one more than the largest current positive rowid. If the maximum possible signed 64-bit value has been used, the algorithm can search for another unused positive value instead.

DROP TABLE IF EXISTS normal_ids;CREATE TABLE normal_ids (    id INTEGER PRIMARY KEY,    note TEXT);INSERT INTO normal_ids(note) VALUES ('one'), ('two'), ('three');DELETE FROM normal_ids WHERE id=3;INSERT INTO normal_ids(note) VALUES ('after deleting current maximum');SELECT id, note FROM normal_ids ORDER BY id;

On a fresh table, the new row normally receives 3 again because the largest current rowid after the delete is 2. That behavior is valid and efficient. It also proves that an automatically generated integer key is not automatically a never-reused audit sequence.

AUTOINCREMENT changes one guarantee and adds work

SQLite's AUTOINCREMENT keyword is narrower than similarly named features in some server databases. It is allowed only with INTEGER PRIMARY KEY on ordinary rowid tables. It changes automatic rowid selection so previously used committed rowids are not reused automatically. SQLite tracks the historical high-water mark in an internal table named sqlite_sequence.

DROP TABLE IF EXISTS never_reuse_demo;CREATE TABLE never_reuse_demo (    id INTEGER PRIMARY KEY AUTOINCREMENT,    note TEXT);INSERT INTO never_reuse_demo(note) VALUES ('one'), ('two'), ('three');DELETE FROM never_reuse_demo WHERE id=3;INSERT INTO never_reuse_demo(note) VALUES ('after deleting 3');SELECT id, note FROM never_reuse_demo ORDER BY id;SELECT name, seq FROM sqlite_sequenceWHERE name='never_reuse_demo';

On a fresh database, the replacement row should receive 4 rather than 3. This is not a promise of consecutive values—gaps can still occur. The stronger non-reuse bookkeeping costs CPU, memory, disk space, and I/O, so official SQLite guidance says to avoid AUTOINCREMENT unless you specifically need its guarantee.

Lab: prove the rules with one disposable database

Use chapter03_rowid.db. Run each experiment, predict the result first, then inspect it.

sqlite3 chapter03_rowid.dbCREATE TABLE device_event (    event_id INTEGER PRIMARY KEY,    event_text TEXT NOT NULL);INSERT INTO device_event(event_text)VALUES ('registered'), ('checked'), ('serviced');SELECT event_id, rowid, _rowid_, oid, event_textFROM device_event;PRAGMA index_list('device_event');DELETE FROM device_event WHERE event_id=3;INSERT INTO device_event(event_text) VALUES ('replacement event');SELECT event_id, event_text FROM device_event ORDER BY event_id;SELECT last_insert_rowid() AS last_rowid_for_this_connection;

The four aliases should agree, index_list should not show a primary-key autoindex for the exact INTEGER PRIMARY KEY, and the deleted maximum value can be reused by ordinary allocation. last_insert_rowid() is connection state tied to a successful insert into a rowid table; Lesson 3 shows why that API concept differs for WITHOUT ROWID.

Failure patterns and safe corrections

Summary and bridge to WITHOUT ROWID

An ordinary SQLite table is normally organized by an internal signed 64-bit rowid. INTEGER PRIMARY KEY gives that key a stable declared name and usually needs no separate primary-key index. Other primary-key forms are different: in a rowid table they typically sit alongside the hidden rowid. Lesson 3 asks when a table whose real identity is a text or composite key should remove that hidden rowid entirely.

References

• SQLite — Rowid Tables.
• SQLite — CREATE TABLE: ROWIDs and INTEGER PRIMARY KEY.
• SQLite — AUTOINCREMENT.
• SQLite — Database File Format.
• SQLite — Last Insert Rowid.