NOT NULL constraint failed

Category: Constraint ViolationVersions: SQLite 3.7.16+

What this means

SQLITE_CONSTRAINT_NOTNULL (extended code 1299) is raised when a NULL value is inserted or updated into a column declared NOT NULL. SQLite's type affinity system is flexible, but NOT NULL is a hard constraint enforced before any affinity conversion.

Why it happens

1Inserting a row without specifying a value for a NOT NULL column with no DEFAULT
2Explicitly inserting NULL into a NOT NULL column
3An UPDATE setting a NOT NULL column to NULL

How to reproduce

A row is inserted without providing a required NOT NULL column.

trigger — this will ERROR

import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL NOT NULL)')
conn.execute('INSERT INTO orders (id) VALUES (1)')  # amount is NULL

sqlite3.IntegrityError: NOT NULL constraint failed: orders.amount

Fix 1: Provide a DEFAULT value for the column

When a sensible default exists for the column.

fix

conn.execute('CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL NOT NULL DEFAULT 0.0)')
conn.execute('INSERT INTO orders (id) VALUES (1)')  # amount defaults to 0.0

Why this works

When a DEFAULT is defined and the column is omitted from the INSERT, SQLite uses the default value, satisfying the NOT NULL constraint without requiring the caller to always supply the value.

Fix 2: Always supply values for NOT NULL columns

When no sensible default exists.

fix

conn.execute('INSERT INTO orders (id, amount) VALUES (1, 99.95)')

Why this works

Explicitly providing the column value in the INSERT statement is the most direct solution. Use parameterised queries to ensure None (Python) is never accidentally bound to a NOT NULL parameter.

What not to do

✗

Remove NOT NULL to silence the error

Why it's wrong: NOT NULL documents a business rule. Removing it allows nulls to enter the column, breaking downstream code that assumes the value is always present.

Version notes

SQLite 3.7.16+Extended code 1299 introduced.

Sources

📚 Official docs: https://www.sqlite.org/rescode.html#constraint_notnull

🔧 Source ref: sqlite3.h — SQLITE_CONSTRAINT_NOTNULL = 1299

📖 Further reading: SQLite CREATE TABLE constraints

Confidence assessment

✅ HIGH confidence

Stable and well-documented.