sqlite-autoincrement · shipping

SQLite AUTOINCREMENT

SQLite AUTOINCREMENT is an INTEGER PRIMARY KEY modifier that makes automatic allocation exceed the committed identifier history recorded for that table.

Related Observatory article: SQLite AUTOINCREMENT on hesela.com

SQLite records this history in sqlite_sequence. The modifier avoids automatic reuse of previously committed identifiers in that history, but does not promise gapless numbering; rolled-back allocations can be reused. Its extra work is unnecessary when those properties are not required. [1]

Restoring an older backup also restores its older allocation history. In Hesela's fixture, a backup at sequence 1 preceded committed ID 2 for beta. The restored copy then assigned 2 to delta, including with AUTOINCREMENT. This is a controlled recovery observation, not an allocator defect or a collision-rate estimate. [2] [3]

A separate deletion control allocated 3 with AUTOINCREMENT and reused 2 without it. Reconciliation of IDs already issued to external systems is therefore a recovery requirement that adding this modifier alone cannot satisfy. [2]

Relations

related

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

Citations

1. SQLite: AUTOINCREMENT, committed history and sqlite_sequence (accessed 2026-10-18)
2. Hesela: measured SQLite row identity across copying and history rollback (accessed 2026-10-18)
3. Hesela: twelve designed SQLite identity cases, full observations and source hash (accessed 2026-10-18)

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