SQLite ROWID: Hidden Primary Keys, Aliases, and WITHOUT ROWID

Every Table Has a Secret Column

Create a plain SQLite table and you've already got one column you didn't declare:

CREATE TABLE notes (
    title TEXT,
    body  TEXT
);

INSERT INTO notes (title, body) VALUES ('first', 'hello');
INSERT INTO notes (title, body) VALUES ('second', 'world');

SELECT rowid, title, body FROM notes;

That rowid column is real. SQLite assigns one to every row of every ordinary table, whether you ask for it or not. It's a 64-bit signed integer, unique within the table, and it's the actual key SQLite uses to find rows in its B-tree storage. Think of it as the table's spine — the index that keeps everything else organised.

You usually don't see it because SELECT * doesn't include it. You have to ask for it by name.

ROWID Has Three Aliases

Because rowid shows up so often in SQL written for other databases, SQLite accepts three names for the same column:

CREATE TABLE notes (title TEXT);
INSERT INTO notes VALUES ('a'), ('b'), ('c');

SELECT rowid, oid, _rowid_, title FROM notes;

rowid, oid, and _rowid_ all refer to the same hidden column. If you've declared a real column with one of those names, your column wins and the alias becomes unavailable — but that's the only catch. In day-to-day code, just write rowid.

INTEGER PRIMARY KEY Is the Magic Phrase

Here's the part that trips up everyone coming from other databases. If you declare a column as exactly INTEGER PRIMARY KEY, that column doesn't get stored separately — it becomes the rowid:

CREATE TABLE users (
    id   INTEGER PRIMARY KEY,
    name TEXT
);

INSERT INTO users (name) VALUES ('Ada'), ('Boris'), ('Rosa');

SELECT rowid, id, name FROM users;

rowid and id are the same column under two names. Inserts that omit id get an automatically chosen integer (typically max rowid + 1). This is why INTEGER PRIMARY KEY is the most efficient way to give a table an auto-incrementing key in SQLite — there's no extra column, no extra index, just the rowid itself.

The exact spelling matters. INT PRIMARY KEY is not the same — INT and INTEGER have different effects here:

CREATE TABLE a (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE b (id INT PRIMARY KEY, name TEXT);

INSERT INTO a (name) VALUES ('x');
INSERT INTO b (name) VALUES ('y');

SELECT rowid, id, name FROM a;
SELECT rowid, id, name FROM b;

In table a, id and rowid match. In table b, id is a normal column and rowid is the separate hidden integer. Worse, b.id doesn't auto-fill on insert — it's NULL until you set it. Stick with INTEGER PRIMARY KEY (the full word) when you want the alias behaviour.

Getting the ROWID of an Insert

After an INSERT, you often want to know the rowid that was just assigned — typically to link a child row to it. SQLite gives you last_insert_rowid():

CREATE TABLE posts (
    id    INTEGER PRIMARY KEY,
    title TEXT
);

INSERT INTO posts (title) VALUES ('first');
INSERT INTO posts (title) VALUES ('second');

SELECT last_insert_rowid();

The function returns the rowid of the most recent successful insert on the current connection. Most database drivers expose the same value as cursor.lastrowid or similar. The RETURNING clause (covered later) is another way to get it back as part of the insert itself.

ROWIDs Aren't Permanent

A row's rowid is stable while the row exists, but it's not a lifetime identifier. A VACUUM can renumber rowids, and if you delete a row the number can be reused by a future insert:

CREATE TABLE items (name TEXT);
INSERT INTO items VALUES ('a'), ('b'), ('c');

DELETE FROM items WHERE name = 'c';
INSERT INTO items VALUES ('d');

SELECT rowid, name FROM items;

Notice the new row may or may not reuse the old rowid depending on version and circumstance — the point is you can't rely on it being unique forever. If you need an identifier that survives across deletes, vacuums, and exports, declare your own INTEGER PRIMARY KEY column (which fixes the value to that row) and consider the AUTOINCREMENT keyword if you specifically need monotonically increasing values that are never reused.

WITHOUT ROWID Tables

Sometimes the rowid is overhead you don't want — most often when your real key isn't an integer. A table of cities keyed by name, for example, ends up with two structures: the rowid B-tree, and a separate index on name to enforce the primary key. WITHOUT ROWID collapses those into one:

CREATE TABLE cities (
    name       TEXT PRIMARY KEY,
    population INTEGER
) WITHOUT ROWID;

INSERT INTO cities VALUES ('Lisbon', 545000), ('Paris', 2102000);

SELECT * FROM cities WHERE name = 'Lisbon';
-- SELECT rowid FROM cities;   -- error: no such column: rowid

Now name is the actual storage key. Lookups by name skip a level of indirection, and the table is smaller. The trade-offs:

• No rowid, oid, or _rowid_ — those columns simply don't exist.
• last_insert_rowid() doesn't update for inserts on this table.
• Incremental BLOB I/O and a few replication features aren't available.
• The table must have a PRIMARY KEY declared.

WITHOUT ROWID is a measured optimisation, not a default. Reach for it when the primary key is non-integer and the table is large or write-heavy. For ordinary integer-keyed tables, the regular rowid layout is already optimal.

The Mental Model

Stripped to essentials:

• Every ordinary SQLite table has a hidden 64-bit integer key called rowid.
• INTEGER PRIMARY KEY (exact spelling) makes your column an alias for it.
• Use last_insert_rowid() to read what was just assigned.
• Rowids can be reused after deletes and renumbered by VACUUM.
• WITHOUT ROWID tables drop the hidden key and use your declared primary key directly — useful for non-integer keys, but they give up some features.

Most of the time you don't think about rowid at all. You declare id INTEGER PRIMARY KEY, let SQLite handle the numbering, and move on. The mechanics matter when you're tuning storage, reading existing schemas, or wondering why INT PRIMARY KEY behaves differently from INTEGER PRIMARY KEY.

Next: NOT NULL and DEFAULT

Now that the row's identity is settled, the next layer is making sure the rest of the columns hold sensible values. NOT NULL and DEFAULT are the two clauses that do most of that work — they're up next.