AUTOINCREMENT vs Implicit Rowid

~12 min · schema, autoincrement, rowid

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Almost always: don't use AUTOINCREMENT

The keyword AUTOINCREMENT exists in SQLite, but its semantics are subtler than most developers expect — and slower than the default behavior.

Default INTEGER PRIMARY KEY — SQLite picks the next available rowid. If you delete rows, the engine may reuse the deleted ids.
INTEGER PRIMARY KEY AUTOINCREMENT — adds a guarantee: ids are monotonically increasing, never reused, even after deletes. Implemented via an internal sqlite_sequence table that tracks the highest-ever-used rowid.

The cost: every insert touches sqlite_sequence, which adds an extra page write. The benefit is rarely needed — most application code does not depend on the database to never reuse ids. If you need globally-unique ids, use UUIDs in a TEXT column with WITHOUT ROWID instead.

Warning: Once you commit to AUTOINCREMENT on a table you can't migrate away from it without the CTAS dance. Decide up-front whether you actually need the no-reuse guarantee.

Code

Default vs AUTOINCREMENT — observe the difference·sql

-- Default: ids may be reused after delete
CREATE TABLE a(id INTEGER PRIMARY KEY, n TEXT);
INSERT INTO a(n) VALUES ('one'), ('two'), ('three');
DELETE FROM a WHERE id = 3;
INSERT INTO a(n) VALUES ('four');
SELECT * FROM a;
-- 1 | one
-- 2 | two
-- 3 | four    <- reused!

-- AUTOINCREMENT: ids never reuse
CREATE TABLE b(id INTEGER PRIMARY KEY AUTOINCREMENT, n TEXT);
INSERT INTO b(n) VALUES ('one'), ('two'), ('three');
DELETE FROM b WHERE id = 3;
INSERT INTO b(n) VALUES ('four');
SELECT * FROM b;
-- 1 | one
-- 2 | two
-- 4 | four    <- skipped 3

External links

AUTOINCREMENT

Exercise

Run the demo above on your own machine. Then write a one-page note answering: in what application scenarios does the no-reuse guarantee matter? In which is it just overhead? Give a real example of each. Bonus: design a UUID-keyed table that gets the same effect via WITHOUT ROWID.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousDates, Booleans, and Practical Types Next →Schema Migrations and Versioning

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.