SQLite: NOT NULL constraint failed

What it means

The column is declared NOT NULL, and the row your INSERT or UPDATE would write has NULL in it. SQLite stops that statement and undoes what it changed; earlier statements in your transaction stay as they were.

The message names the table and column: NOT NULL constraint failed: customers.name. The result code is SQLITE_CONSTRAINT (19), extended code SQLITE_CONSTRAINT_NOTNULL (1299).

Common causes

The column was left out of the INSERT and has no DEFAULT, so SQLite fills it with NULL.
An explicit NULL. A variable in your code was empty (None, nil, null, an unset field from a form or an API). A column’s default applies only when you leave the column out; passing NULL on purpose still fails, even for a column with a default.
An UPDATE that sets NULL, often without meaning to: SET region = (SELECT …) where the subquery finds no row gives NULL.
A table rebuild meets old NULLs. To change a column, SQLite tools create a new table, copy the rows across and swap the names. If the new definition makes a column NOT NULL and old rows have NULL there, the copy fails, and the message names the new table: new_customers.phone, or new__app_model.<column> from Django’s migrations.

How to fix it

Find the empty value

Use the table and column from the message. For a single statement, print the values you bind right before running it. For a rebuild, find the rows that will fail:

SELECT id, name FROM customers WHERE phone IS NULL;

Supply the value, or leave the column out

Give the column a value, or, if it has a DEFAULT, leave it out of the column list so the default applies. To fall back on a value when your variable may be empty, use coalesce:

INSERT INTO orders (customer_id, total, status) VALUES (?, ?, coalesce(?, 'new'));

Guard subqueries in UPDATE

Give the subquery a fallback, such as the column’s current value:

UPDATE stores SET region = coalesce((SELECT name FROM regions WHERE regions.id = stores.region_id), region);

Fill NULLs before a rebuild

Either set the old rows first:

UPDATE customers SET phone = '' WHERE phone IS NULL;

or convert them while you copy:

INSERT INTO new_customers SELECT id, name, email, coalesce(phone, '') FROM customers;

In a framework’s migration, add a step that fills the column before the one that makes it NOT NULL, or give the field a default.

Know what OR REPLACE and OR IGNORE do here

With INSERT OR REPLACE, a NULL in a NOT NULL column that has a default is replaced by the default; without a default, the statement fails as usual. REPLACE also deletes any row that clashes on a unique key, so don’t use it only for this. INSERT OR IGNORE skips the row without an error, which hides the problem.

Adding a DEFAULT to an existing column isn’t possible with ALTER TABLE in SQLite; it takes a table rebuild (the 12 steps in SQLite’s documentation).

Reproduce it

macOS /usr/bin/sqlite3, SQLite 3.51.0, with customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT, …):

sqlite3 shop.db "INSERT INTO customers (email) VALUES ('linus@example.com');"

Error: stepping, NOT NULL constraint failed: customers.name (19)

The shell adds “Error: stepping,” and the result code; SQLite’s message is NOT NULL constraint failed: customers.name. An explicit NULL for name, and UPDATE customers SET name = NULL WHERE id = 2, failed the same way.

A column with a default, status TEXT NOT NULL DEFAULT 'new', given an explicit NULL:

Error: stepping, NOT NULL constraint failed: orders.status (19)

The same row with INSERT OR REPLACE was stored with status = new.

UPDATE stores SET region = (SELECT name FROM regions WHERE regions.id = stores.region_id), where one store’s region_id matched no region:

Error: stepping, NOT NULL constraint failed: stores.region (19)

A rebuild, copying customers (one row with a NULL phone) into new_customers, whose phone is NOT NULL:

Error: stepping, NOT NULL constraint failed: new_customers.phone (19)

With coalesce(phone, '') in the SELECT, both rows copied. The coalesce version of the UPDATE above kept the old value for the store with no match. Through Python’s sqlite3 module (SQLite 3.53.4), binding None for name raised sqlite3.IntegrityError with sqlite_errorname SQLITE_CONSTRAINT_NOTNULL.

In Inlet

Grid edits, including Set NULL, are staged until you commit (⌘S), and Review shows the exact SQL first, so you can see which INSERT or UPDATE leaves a required column empty. If SQLite refuses it, Inlet shows SQLite’s message with the table and column.

NOT NULL constraint failed