SQLite: CHECK constraint failed (tested)

sqlite3.IntegrityError: CHECK constraint failed: <expr> fires when a row violates a CHECK you defined on the table. We measured what the error message actually tells you, and one standard-SQL behavior that surprises people.

1. The error echoes the exact CHECK expression

con.execute("CREATE TABLE t (id INTEGER, age INTEGER CHECK(age >= 0))")
con.execute("INSERT INTO t (id, age) VALUES (1, -5)")
sqlite3.IntegrityError: CHECK constraint failed: age >= 0

Measured: unlike some of sqlite3's other constraint errors, this one doesn't just name the column — it reprints the literal CHECK expression from the table definition, which makes it one of the more immediately diagnosable errors in this series. A pattern-based CHECK behaves the same way:

con.execute('CREATE TABLE t2 (id INTEGER, email TEXT CHECK(email LIKE "%@%"))')
con.execute('INSERT INTO t2 (id, email) VALUES (1, "notanemail")')
sqlite3.IntegrityError: CHECK constraint failed: email LIKE "%@%"

2. NULL silently bypasses CHECK entirely

Using the same age INTEGER CHECK(age >= 0) column, we inserted NULL instead of a negative number:

con.execute("INSERT INTO t (id, age) VALUES (2, NULL)")
# no error — row stored as (2, None)

Measured: this is standard SQL CHECK semantics, not an SQLite quirk — a CHECK expression that evaluates to NULL (which comparing NULL to anything does) is treated as passing, not failing. If you need to actually require a value, you need a separate NOT NULL constraint on the same column; CHECK alone does not imply it.

3. CHECK can reference multiple columns in the same row

con.execute("CREATE TABLE t3 (lo INTEGER, hi INTEGER, CHECK(lo <= hi))")
con.execute("INSERT INTO t3 (lo, hi) VALUES (10, 5)")
sqlite3.IntegrityError: CHECK constraint failed: lo <= hi

This confirms CHECK isn't limited to validating one column against a constant; it can enforce a relationship between two or more columns in the same row.

4. The fix: read the expression in the error, and add NOT NULL if needed

Since the message already contains the exact failing expression, the fix is usually immediate: correct the value to satisfy that literal expression. If NULL values shouldn't be allowed to slip through unchecked, add NOT NULL explicitly — we confirmed CHECK alone does not reject NULL.

Frequently asked questions

Does the CHECK constraint error tell you which rule failed?

Yes — we measured that the error message reprints the exact CHECK expression from the table definition (e.g. 'age >= 0'), not just a generic message or the column name, making it one of the more directly actionable errors in this series.

Does inserting NULL violate a CHECK constraint?

No — we confirmed NULL silently bypasses CHECK entirely and the insert succeeds. This is standard SQL behavior (a NULL comparison evaluates to NULL, which CHECK treats as passing), not an SQLite-specific quirk. Add NOT NULL separately if NULL should be rejected too.

Can a CHECK constraint reference more than one column?

Yes — we tested CHECK(lo <= hi) across two columns in the same row and it failed correctly when lo was greater than hi, confirming CHECK isn't limited to single-column validation against a constant.

What exception class does sqlite3 raise for this?

IntegrityError — we confirmed it's the same exception class used for NOT NULL and UNIQUE violations, not OperationalError, which most of the other sqlite3 errors in this series raise instead.

Is a pattern-based CHECK (like LIKE) evaluated the same way as a numeric one?

Yes — we tested CHECK(email LIKE "%@%") and it failed identically to the numeric age example, with the exact LIKE expression echoed back in the error message.

Written with AI assistance. Measured on 2026-10-03 with Python 3.13.5's built-in sqlite3 module, using a single-column CHECK, a pattern-based CHECK, a NULL value against a CHECK'd column, and a multi-column CHECK. Every error string below is copy-pasted output, not paraphrased. PlainHeaders is our extension; Python's sqlite3 is built in.