NOT NULL constraint failed
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.
- 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
A row is inserted without providing a required NOT NULL column.
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 NULLexpected output
sqlite3.IntegrityError: NOT NULL constraint failed: orders.amount
Fix 1
Provide a DEFAULT value for the column
WHEN When a sensible default exists for the column.
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.0Why 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 When no sensible default exists.
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.
✕ Remove NOT NULL to silence the error
NOT NULL documents a business rule. Removing it allows nulls to enter the column, breaking downstream code that assumes the value is always present.
Extended code 1299 introduced.
sqlite3.h — SQLITE_CONSTRAINT_NOTNULL = 1299
SQLite CREATE TABLE constraints ↗Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev