rowid and INTEGER PRIMARY KEY

Most SQLite tables have an internal integer rowid. It’s how SQLite physically addresses each row: the table is stored as a tree keyed by rowid, so looking a row up by it is the fastest access path the engine has.

You can query this hidden column even though you never declared it:

SELECT rowid, title FROM notes;
-- 1|Plan the week

The alias rule

A column declared exactly as INTEGER PRIMARY KEY becomes an alias for that rowid. SQLite assigns the next available value when an insert omits the column.

That’s why our notes table gets ids 1, 2, 3 without any sequence or auto-increment setup:

INSERT INTO notes (title) VALUES ('Buy groceries');
SELECT id, rowid FROM notes WHERE title = 'Buy groceries';
-- 2|2

id and rowid return the same value because they are the same value. One column, two names.

The word “exactly” carries weight. INT PRIMARY KEY does not create the alias — only the full spelling INTEGER PRIMARY KEY does. This is one of SQLite’s sharpest edges: with INT, the column is an ordinary unique column and the table keeps a separate hidden rowid, which costs you the fast path and can leave the column NULL. If your ids behave strangely, check the declaration first.

Why you rarely need AUTOINCREMENT

You normally do not need AUTOINCREMENT. It changes reuse rules and adds overhead; use it only when old identifiers must never appear again.

Here’s the difference. Without AUTOINCREMENT, SQLite picks one more than the current largest rowid. If you delete the row with the highest id, its number can be handed out again to a future insert. With AUTOINCREMENT, SQLite records the highest value ever used in an internal sqlite_sequence table and never reissues it, at the cost of extra bookkeeping on every insert.

For most tables, reuse is harmless — the ids only need to be unique among live rows. It matters when identifiers escape the database: an id printed on an invoice, embedded in a URL, or synced to another system should never be recycled to mean a different record. That’s the case AUTOINCREMENT exists for.

After an insert, you can read the id SQLite just assigned:

SELECT last_insert_rowid();
-- 2

Every SQLite driver exposes this, and it’s the standard way to link freshly inserted rows to related tables.