sqlite-rowid · shipping · also SQLite ROWID

SQLite rowid

A SQLite rowid is the unique signed 64-bit integer key used to identify a row in an ordinary rowid table.

Related Observatory article: SQLite rowid on hesela.com

An unaliased rowid is not a durable application identifier because VACUUM may change it. An ordinary id INTEGER PRIMARY KEY column aliases it and makes that value explicit data. WITHOUT ROWID tables and virtual tables are outside this definition. [1]

In Hesela's two-row SQLite 3.45.1 fixture, VACUUM and a Python iterdump restore reassigned hidden IDs 2 and 3 to 1 and 2. Saved reference 2 selected gamma instead of beta despite intact declared data and a passing integrity_check. The explicit-key controls retained the mapping. [3] [4]

VACUUM INTO and the Online Backup API preserved the hidden IDs in the measured fixture. The pinned transfer implementation distinguishes the INTO path; similar command names do not establish equal identity behavior for all schemas or versions. [2] [4]

Check the exact declaration: INT PRIMARY KEY and column-level INTEGER PRIMARY KEY DESC do not alias rowid, unlike INTEGER PRIMARY KEY or its table-level DESC form. In four additional controls, all schemas reported a primary key, but only the aliases populated an omitted id. The full rows survived Python dump restoration. [5] [6]

Relations

related

sqlite-autoincrement, sqlite-online-backup, backup-verification, restore-test

Citations

1. SQLite: Rowid tables and persistence of unaliased rowids (accessed 2026-10-18)
2. SQLite version-3.45.1 insert.c: xferOptimization rowid selection, lines 2933-2947 (accessed 2026-10-18)
3. Hesela: measured SQLite row identity across copying and history rollback (accessed 2026-10-18)
4. Hesela: twelve designed SQLite identity cases, full observations and source hash (accessed 2026-10-18)
5. SQLite CREATE TABLE: exact type and DESC exception (accessed 2026-10-18)
6. Hesela: four measured primary-key spelling controls with complete rows and source hash (accessed 2026-10-18)

JSON: /data/terms/sqlite-rowid.json