SQLite Basics: Choose Natural Keys with WITHOUT ROWID

A stock record identified by warehouse and product already has a useful key. SQLite’s WITHOUT ROWID option lets a table use its declared primary key as its storage key rather than maintaining a separate hidden rowid. It is worth understanding before choosing a schema for small rows with text or composite keys.

WITHOUT ROWID requires SQLite 3.8.2 or later. It is not an automatic performance improvement: benchmark the actual workload and database size. These examples show behavior, not speed. WITHOUT ROWID compatibility and tradeoffs.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. Each invocation starts with a fresh in-memory main database and prints pipe-separated rows, showing missing values as NULL. It does not escape embedded pipes or newlines, so it is a teaching runner, not a general SQL shell or CSV exporter. Put each statement’s ending semicolon at the end of a line, as shown; do not put two complete statements on one line.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

The toy database disappears when each invocation closes its connection. The examples are independent and can be run in any order.

1. Look up stock by a composite primary key

Save as lookup-composite-stock.sql:

CREATE TABLE stock (
    warehouse TEXT NOT NULL,
    sku TEXT NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity >= 0),
    PRIMARY KEY (warehouse, sku)
) WITHOUT ROWID;
INSERT INTO stock VALUES
    ('east', 'pen', 12),
    ('east', 'pad', 7),
    ('west', 'pen', 5);
SELECT warehouse, sku, quantity FROM stock
WHERE warehouse = 'east'
ORDER BY sku;
UPDATE stock SET quantity = quantity - 2
WHERE warehouse = 'east' AND sku = 'pen';
SELECT quantity FROM stock
WHERE warehouse = 'east' AND sku = 'pen';

Run:

python3 run-sql.py lookup-composite-stock.sql

Output:

east|pad|7
east|pen|12
10

The pair of text columns identifies one stock record. The update changes east’s pens without touching west’s pens, and the second query confirms the remaining quantity. No extra integer identifier is needed for this toy contract.

The explicit ORDER BY still matters. A storage organization is not a promise about the order of SELECT results. Also notice that removing the hidden rowid does not remove ordinary constraints: NOT NULL, the composite primary key, and the nonnegative CHECK still express the table’s data rules.

2. Observe primary-key null and duplicate rejection

Save as check-natural-key-boundaries.sql:

CREATE TABLE translations (
    phrase TEXT,
    locale TEXT,
    text_value TEXT NOT NULL,
    PRIMARY KEY (phrase, locale)
) WITHOUT ROWID;
INSERT INTO translations VALUES ('save', 'en', 'Save');
INSERT OR IGNORE INTO translations VALUES (NULL, 'en', 'Missing key');
SELECT 'null inserted', changes();
INSERT OR IGNORE INTO translations VALUES ('save', 'en', 'Duplicate');
SELECT 'duplicate inserted', changes();
INSERT INTO translations VALUES ('save', 'fr', 'Enregistrer');
SELECT phrase, locale, text_value FROM translations
ORDER BY phrase, locale;

Run:

python3 run-sql.py check-natural-key-boundaries.sql

Output:

null inserted|0
duplicate inserted|0
save|en|Save
save|fr|Enregistrer

Both attempted invalid inserts report zero changed rows. The original English text remains, and a different locale is allowed. WITHOUT ROWID enforces non-null values in every primary-key column even though phrase and locale do not spell out NOT NULL in this declaration.

OR IGNORE is used only to keep this boundary demonstration running. An ordinary INSERT would raise a constraint error for either invalid row. Silently ignoring malformed input is usually a poor import policy because it can hide missing records; reject or report the problem explicitly in application code.

3. Use the declared key instead of last_insert_rowid

Save as read-explicit-without-rowid-key.sql:

CREATE TABLE ordinary_log (id INTEGER PRIMARY KEY, event TEXT);
INSERT INTO ordinary_log(id, event) VALUES (41, 'Started');
CREATE TABLE devices (
    id INTEGER PRIMARY KEY,
    label TEXT NOT NULL
) WITHOUT ROWID;
INSERT INTO devices(id, label) VALUES (7, 'Kitchen sensor');
SELECT 'device', id, label FROM devices WHERE id = 7;
SELECT 'connection rowid', last_insert_rowid();

Run:

python3 run-sql.py read-explicit-without-rowid-key.sql

Output:

device|7|Kitchen sensor
connection rowid|41

The device has the explicit key 7, while last_insert_rowid() still reports 41 from the ordinary table. Inserting into a WITHOUT ROWID table does not update that connection value. Treating it as the new device ID would therefore select the wrong record.

INTEGER PRIMARY KEY has no rowid-alias behavior in this table: provide the key rather than expecting an omitted value to generate one. AUTOINCREMENT is unsupported. If your design naturally wants automatically assigned integer IDs, an ordinary rowid table may be the simpler fit.

The WITHOUT ROWID reference lists further differences, including incremental BLOB I/O and update-hook limitations. The last_insert_rowid reference explains which successful inserts update that value. Review library assumptions before converting an existing table.

Clean up

In the same terminal, remove only these example files and the empty scratch directory:

rm -- run-sql.py lookup-composite-stock.sql check-natural-key-boundaries.sql read-explicit-without-rowid-key.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.