PRIMARY KEY and Rowid

Every table has a rowid (almost)

By default every SQLite table has an invisible rowid — a 64-bit integer the engine assigns to each row. It's how indexes and joins find rows internally. You can see it explicitly:

SELECT rowid, * FROM users;

When you declare a column INTEGER PRIMARY KEY (note: exactly that spelling, not INT PRIMARY KEY), SQLite makes that column an alias for rowid. The column becomes the rowid — no extra storage, no extra index, just a name for the integer SQLite was already maintaining.

Other primary key shapes:

Composite primary key — PRIMARY KEY (a, b) on multiple columns.
WITHOUT ROWID tables — useful for tables keyed by something other than an integer (e.g., a UUID); the column you declare is the actual storage key.

Warning: INT PRIMARY KEY ≠ INTEGER PRIMARY KEY. The first is a regular indexed column; only the exact word INTEGER activates the rowid-alias optimization. Easy bug to ship — your inserts work but performance silently regresses on large tables.

Code

Rowid alias — same column, two names·sql

CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO users(name) VALUES ('Alice'), ('Bob');

SELECT rowid, id, name FROM users;
-- 1 | 1 | Alice
-- 2 | 2 | Bob

-- They are literally the same column
SELECT rowid = id FROM users;
-- 1
-- 1

WITHOUT ROWID for non-integer primary keys·sql

CREATE TABLE sessions (
  session_id TEXT PRIMARY KEY,
  user_id    INTEGER NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
) WITHOUT ROWID;

-- The session_id is now the actual storage key.
-- For UUID-style tables this saves an entire B-tree level.

Exercise

Create three tables: one with INTEGER PRIMARY KEY, one with INT PRIMARY KEY, and one with WITHOUT ROWID on a TEXT key. Insert a few rows in each, then SELECT rowid, * from all three. Note how they differ. Then inspect pragma_index_list(table) on each — count how many internal indexes SQLite created for each shape.

Every table has a rowid (almost)

Code

External links

Exercise

Progress

Comments 0