Why does a SQLite PRIMARY KEY column accept NULL?

PRIMARY KEY column that accepts NULL

Note: this works, but it is a documented trap or a cost the author may not have meant.

What happens

In a rowid table, a PRIMARY KEY column that is not INTEGER does not imply NOT NULL, a long-standing SQLite bug kept for compatibility, so NULL keys are accepted and the uniqueness the key promised is gone (every NULL is distinct). In a WITHOUT ROWID table the same NULL is refused at insert time instead, so the schema behaves differently depending on one table option.

Why it is dangerous on a populated table

SQLite holds one write lock for the whole database, so anything that rebuilds a table blocks every writer for as long as the copy takes, and that time grows with the table.

Fires on

CREATE TABLE orders (ref TEXT PRIMARY KEY, qty INTEGER);

The safe pattern

CREATE TABLE orders (ref TEXT PRIMARY KEY NOT NULL, qty INTEGER);

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

CREATE TABLE orders (ref TEXT, qty INTEGER, PRIMARY KEY (ref)) STRICT;

CREATE TABLE orders (ref TEXT PRIMARY KEY, qty INTEGER) STRICT;

CREATE TABLE orders (id INTEGER PRIMARY KEY, qty INTEGER) STRICT;

CREATE TABLE orders (ref TEXT PRIMARY KEY NOT NULL, qty INTEGER) STRICT;

CREATE TABLE orders (ref TEXT PRIMARY KEY, qty INTEGER) WITHOUT ROWID, STRICT;

How to check locally

npx bolvrk check migration.sql --engine=sqlite