WITHOUT ROWID Tables and Composite Primary Keys

Learning outcomes

A rowid table is a good default, especially when the table already has an INTEGER PRIMARY KEY. But some data is naturally identified by a text key or a composite key. SQLite's WITHOUT ROWID table form lets that declared primary key become the table's organizing key instead of keeping a separate hidden rowid.

The motivating case: identity is already composite

Suppose FieldNotes records which tags are assigned to each device. The logical identity of an assignment is the pair (device_id, tag). No separate numeric assignment ID is required by the requirements.

CREATE TABLE device_tag_rowid (    device_id INTEGER NOT NULL,    tag       TEXT NOT NULL,    PRIMARY KEY (device_id, tag));

Because this is an ordinary rowid table, SQLite still uses a hidden integer rowid as the table b-tree key. The declared composite PRIMARY KEY is enforced by separate uniqueness structure. Logically that is correct, but physically it means the table has identity information in two places: hidden rowid for storage lookup and the composite key for relational identity.

WITHOUT ROWID makes the declared primary key the storage key

Add WITHOUT ROWID after the closing parenthesis. The logical columns and queries remain familiar, but the table is organized using its declared PRIMARY KEY.

CREATE TABLE device_tag_wr (    device_id INTEGER NOT NULL,    tag       TEXT NOT NULL,    PRIMARY KEY (device_id, tag)) WITHOUT ROWID;

Official SQLite documentation describes WITHOUT ROWID as an optimization, not a new capability. It can reduce duplicated key storage or improve locality for some non-integer/composite-key tables. But wider keys also change b-tree fan-out, so the same choice can be neutral or harmful for another workload.

Inspect both table forms side by side

Do not infer table form from the column list alone. PRAGMA table_list reports a wr flag, and index metadata reveals different primary-key organization.

DROP TABLE IF EXISTS device_tag_rowid;DROP TABLE IF EXISTS device_tag_wr;CREATE TABLE device_tag_rowid (    device_id INTEGER NOT NULL,    tag       TEXT NOT NULL,    PRIMARY KEY (device_id, tag));CREATE TABLE device_tag_wr (    device_id INTEGER NOT NULL,    tag       TEXT NOT NULL,    PRIMARY KEY (device_id, tag)) WITHOUT ROWID;PRAGMA table_list('device_tag_rowid');PRAGMA table_list('device_tag_wr');PRAGMA index_list('device_tag_rowid');PRAGMA index_list('device_tag_wr');SELECT type, name, tbl_name, sqlFROM sqlite_schemaWHERE tbl_name IN ('device_tag_rowid','device_tag_wr')ORDER BY tbl_name, type, name;

In a modern SQLite build, table_list reports wr=0 for the ordinary table and wr=1 for the WITHOUT ROWID table. The exact index listing is metadata, not a performance benchmark; the key lesson is that the storage organization differs even though the logical primary key is the same.

WITHOUT ROWID has deliberate restrictions and API differences

Removing rowid means features that fundamentally depend on rowid cannot work the same way. These are not defects; they are consequences of choosing a different table organization.

Prove that the logical answers stay the same

Insert identical data into both tables. The point of WITHOUT ROWID is not to change relational meaning.

INSERT INTO device_tag_rowid(device_id, tag) VALUES  (1, 'pump'),  (1, 'critical'),  (2, 'fan');INSERT INTO device_tag_wr(device_id, tag)SELECT device_id, tag FROM device_tag_rowid;SELECT device_id, tagFROM device_tag_rowidORDER BY device_id, tag;SELECT device_id, tagFROM device_tag_wrORDER BY device_id, tag;-- This succeeds only on the ordinary rowid table:SELECT rowid, device_id, tagFROM device_tag_rowidORDER BY rowid;-- Expect an error: no such column: rowidSELECT rowid, device_id, tagFROM device_tag_wr;

Both first queries return the same three logical pairs. The final query fails because device_tag_wr has no hidden rowid. That failure is useful evidence that the table form is genuinely different.

Composite PRIMARY KEY nullability exposes an important SQLite difference

Historical compatibility means an ordinary non-STRICT rowid table can allow NULL in many declared PRIMARY KEY forms unless you also declare NOT NULL. WITHOUT ROWID follows the standard expectation and rejects NULL for every primary-key column.

DROP TABLE IF EXISTS ordinary_key_quirk;DROP TABLE IF EXISTS strict_key_behavior;CREATE TABLE ordinary_key_quirk (    a TEXT,    b TEXT,    PRIMARY KEY (a,b));CREATE TABLE strict_key_behavior (    a TEXT,    b TEXT,    PRIMARY KEY (a,b)) WITHOUT ROWID;-- May succeed in the ordinary legacy-compatible table:INSERT INTO ordinary_key_quirk(a,b) VALUES (NULL, 'x');-- Fails because WITHOUT ROWID PK columns are NOT NULL:INSERT INTO strict_key_behavior(a,b) VALUES (NULL, 'x');

Do not exploit the legacy quirk. In production schemas, declare required key columns NOT NULL explicitly when that communicates the model, and let later chapters add STRICT where appropriate.

EXPLAIN QUERY PLAN is evidence, not a benchmark

You can ask SQLite how it plans a lookup, but plan text and cost decisions are version- and data-dependent. Use it to learn which key or index is chosen, not to claim a measured speedup.

EXPLAIN QUERY PLANSELECT tagFROM device_tag_rowidWHERE device_id=1 AND tag='critical';EXPLAIN QUERY PLANSELECT tagFROM device_tag_wrWHERE device_id=1 AND tag='critical';

Both queries should be able to use primary-key information for the exact pair. The descriptions may differ because one table has a separate uniqueness structure and the other is directly organized by the declared key. Chapter 10 teaches query-plan interpretation systematically; Chapter 11 examines file pages and b-trees.

Lab: choose table form from the access pattern

Create chapter03_without_rowid.db. Keep both variants so you can inspect them throughout later chapters.

sqlite3 chapter03_without_rowid.dbCREATE TABLE assignment_rowid (    device_code TEXT NOT NULL,    technician  TEXT NOT NULL,    role        TEXT NOT NULL,    PRIMARY KEY (device_code, technician));CREATE TABLE assignment_wr (    device_code TEXT NOT NULL,    technician  TEXT NOT NULL,    role        TEXT NOT NULL,    PRIMARY KEY (device_code, technician)) WITHOUT ROWID;INSERT INTO assignment_rowid VALUES ('PUMP-007','Leila','owner'), ('PUMP-007','Arman','reviewer'), ('FAN-014','Arman','owner');INSERT INTO assignment_wr SELECT * FROM assignment_rowid;PRAGMA table_list('assignment_rowid');PRAGMA table_list('assignment_wr');SELECT * FROM assignment_rowid ORDER BY device_code, technician;SELECT * FROM assignment_wr ORDER BY device_code, technician;EXPLAIN QUERY PLANSELECT role FROM assignment_wrWHERE device_code='PUMP-007' AND technician='Leila';

The logical query results must match. The design question is whether the composite key is stable, compact enough, and central enough to the workload that primary-key organization is attractive. Retain both versions for later measurement rather than declaring a winner now.

Summary and bridge to connection-scoped storage

WITHOUT ROWID is best understood as a storage organization choice for tables whose true key is not the ordinary integer rowid. It brings stricter primary-key nullability and removes rowid-dependent behaviors. With table identity understood, Lesson 4 changes dimensions: instead of asking how rows are keyed, it asks where the whole schema lives and how long it remains visible.

References

• SQLite — WITHOUT ROWID Tables.
• SQLite — Rowid Tables.
• SQLite — PRAGMA table_list / index metadata.
• SQLite — Last Insert Rowid.
• SQLite — EXPLAIN QUERY PLAN.