SQLite rowid vs. Primary Key: What's the Difference?

Published 2026-09-03 · 6 min read

Unlike Postgres, where a primary key is just a unique, non-null constraint on whichever column you name, SQLite has a built-in, hidden row identifier called rowid that exists on almost every table whether you asked for it or not.

Every table (mostly) has a rowid

Create a plain table with no explicit primary key, and SQLite still gives every row a unique 64-bit rowid automatically, incrementing as rows are inserted:

CREATE TABLE notes (body TEXT);

INSERT INTO notes (body) VALUES ('first');
INSERT INTO notes (body) VALUES ('second');

SELECT rowid, body FROM notes;
-- rowid | body
-- 1     | first
-- 2     | second

You can query it explicitly, even though it's not one of the columns you declared. It's also available under the aliases _rowid_ and oid, in case one of those names collides with a real column.

INTEGER PRIMARY KEY becomes an alias for rowid

This is the part that surprises people: declare a column as exactly INTEGER PRIMARY KEY — that exact phrase, not BIGINT or a composite key — and SQLite doesn't create a separate value for it. That column becomes the rowid, just under your chosen name.

CREATE TABLE notes (
  id INTEGER PRIMARY KEY,
  body TEXT
);

INSERT INTO notes (body) VALUES ('first');
SELECT id, rowid FROM notes;
-- id | rowid
-- 1  | 1        (same value, same storage)

This is why INTEGER PRIMARY KEY auto-increments in SQLite without an explicit AUTOINCREMENT keyword — it's riding on the same mechanism that generates rowids for every insert. (AUTOINCREMENT exists as a separate, stricter option that guarantees rowids are never reused after a delete, at a small performance cost — most schemas don't need it.)

What breaks this: composite and non-integer keys

A primary key on a TEXT column, or a composite primary key across multiple columns, is a real, separate constraint — it does not alias the rowid. The table still has a rowid underneath, distinct from whatever you declared as the primary key.

CREATE TABLE order_items (
  order_id INTEGER,
  product_id INTEGER,
  quantity INTEGER,
  PRIMARY KEY (order_id, product_id)
);
-- This table has both a rowid AND a separate composite primary key

This matters for any tool — including a GUI client — that needs a stable way to identify "this exact row" for an update or delete. Without a single-column integer identity to use, the safest fallback is treating every column together as the row's identity.

WITHOUT ROWID: opting out entirely

Adding WITHOUT ROWID to a table definition removes the hidden rowid structure altogether and stores rows directly within the index built on the declared primary key. This can be more compact and faster for tables with a natural non-integer key — a product SKU, a country code — where you'd otherwise be storing both the rowid and a redundant unique index on that column.

CREATE TABLE countries (
  code TEXT PRIMARY KEY,
  name TEXT NOT NULL
) WITHOUT ROWID;

The trade-off: without rowid's implicit ordering, and without the fast integer lookups rowid provides, WITHOUT ROWID is a deliberate choice for specific schemas, not a default.

This exact distinction is why QuerySQL's table editor falls back to a composite identity — every column together — when it can't find a single-column rowid or primary key to key an update on. See the FAQ for how that plays out on the Postgres side too, where there's no rowid equivalent at all.