SQLite: NOT NULL constraint failed (tested)

sqlite3.IntegrityError: NOT NULL constraint failed: table.column fires whenever a value for a NOT NULL column ends up missing — whether you wrote NULL explicitly or just left the column out. We reproduced both and measured the fix.

1. An explicit NULL

con.execute("CREATE TABLE t (id INTEGER, name TEXT NOT NULL)")
con.execute("INSERT INTO t (id, name) VALUES (1, NULL)")
sqlite3.IntegrityError: NOT NULL constraint failed: t.name

2. Simply omitting the column

Same table, but the INSERT's column list just doesn't mention name at all:

con.execute("INSERT INTO t (id) VALUES (2)")
sqlite3.IntegrityError: NOT NULL constraint failed: t.name

Measured: identical error, identical column named in the message. SQLite treats "no value given" and "value given as NULL" the same way for a column with no default — both resolve to NULL, and both violate the constraint equally.

3. The fix: a value, or a column DEFAULT

We confirmed adding a DEFAULT to the column definition bypasses the error entirely for the omitted-column case, with no change to the INSERT statement:

con.execute("CREATE TABLE t2 (id INTEGER, name TEXT NOT NULL DEFAULT 'unknown')")
con.execute("INSERT INTO t2 (id) VALUES (3)")
# succeeded: row is (3, 'unknown')

A DEFAULT only helps when the column is omitted — it does not suppress an explicit NULL; we confirmed inserting NULL directly into t2.name still fails the same way, since you explicitly asked for NULL rather than leaving it unspecified.

Frequently asked questions

Does an explicit NULL fail the same way as leaving the column out?

Yes — we measured both giving the identical error, naming the same column. SQLite doesn't distinguish 'no value supplied' from 'NULL supplied' for a column with no default; both become NULL and both violate NOT NULL equally.

Does adding DEFAULT to the column fix an explicit NULL insert too?

No — we confirmed DEFAULT only applies when the column is omitted from the INSERT entirely. Explicitly inserting NULL still fails against a DEFAULT'd NOT NULL column, since you asked for NULL rather than leaving the value unspecified.

Which table and column does the error name?

Both — the message format we measured is 'NOT NULL constraint failed: table.column', naming the exact table and column, which is usually enough to find the failing INSERT without further guessing.

Is this an OperationalError or something else?

IntegrityError — we confirmed sqlite3 raises sqlite3.IntegrityError specifically for this (a subclass of DatabaseError), distinct from the OperationalError class used for most of the other sqlite3 errors we've measured in this series.

What's the real-world fix if the column genuinely needs a value?

Supply it in the INSERT, or add a DEFAULT if a sensible fallback exists — we confirmed a DEFAULT resolves the omitted-column case with zero change to existing INSERT statements that don't mention that column.

Written with AI assistance. Measured on 2026-10-03 with Python 3.13.5's built-in sqlite3 module, inserting an explicit NULL and, separately, omitting a NOT NULL column entirely, then confirming a column default bypasses both. Every error string below is copy-pasted output, not paraphrased. PlainHeaders is our extension; Python's sqlite3 is built in.