SQLite: UNIQUE constraint failed (tested)

sqlite3.IntegrityError: UNIQUE constraint failed: table.column fires when an insert or update would create a duplicate value in a column (or column combination) marked UNIQUE. We measured one standard-SQL exemption that surprises people, and the real non-erroring fix.

1. The basic case, and the exact column named

con.execute("CREATE TABLE t (id INTEGER, email TEXT UNIQUE)")
con.execute('INSERT INTO t (id, email) VALUES (1, "a@x.com")')
con.execute('INSERT INTO t (id, email) VALUES (2, "a@x.com")')
sqlite3.IntegrityError: UNIQUE constraint failed: t.email

2. NULL is exempt — you can insert it repeatedly

Using the same UNIQUE email column, we inserted NULL twice:

con.execute("INSERT INTO t (id, email) VALUES (3, NULL)")
con.execute("INSERT INTO t (id, email) VALUES (4, NULL)")
# both succeeded — no error

Measured: this is standard SQL semantics, not an SQLite quirk — NULL is never considered equal to another NULL for uniqueness purposes, so a UNIQUE column can hold any number of NULLs. If you actually need "at most one row with no email," UNIQUE alone doesn't enforce that; you'd need application logic or a partial index.

3. Composite UNIQUE names every column involved

con.execute("CREATE TABLE t2 (a INTEGER, b INTEGER, UNIQUE(a,b))")
con.execute("INSERT INTO t2 VALUES (1,1)")
con.execute("INSERT INTO t2 VALUES (1,2)")   # different combination: OK
con.execute("INSERT INTO t2 VALUES (1,1)")   # exact combo repeated
sqlite3.IntegrityError: UNIQUE constraint failed: t2.a, t2.b

The error lists both columns in the composite key, confirming it's the (a, b) pair that must be unique together, not either column individually.

4. The fix: ON CONFLICT upsert instead of insert-then-catch

Rather than catching the exception and issuing a separate UPDATE, we confirmed SQLite's upsert syntax handles both cases in one statement:

con.execute(\'\'\'INSERT INTO t (id, email) VALUES (5, "a@x.com")
    ON CONFLICT(email) DO UPDATE SET id=excluded.id\'\'\')
# succeeded with no error, row updated to id=5

This is the same syntax as PostgreSQL's (SQLite added it in 3.24+), so it's worth knowing even if you primarily work in Postgres.

Frequently asked questions

Can a UNIQUE column hold multiple NULL values?

Yes — we measured this directly: two separate NULL inserts into the same UNIQUE column both succeeded with zero error. NULL is never considered equal to another NULL for uniqueness, which is standard SQL behavior, not specific to SQLite.

How is a composite UNIQUE constraint's error different from a single-column one?

We confirmed the error lists every column in the composite key together (e.g. 't2.a, t2.b'), not just one — it's the combination of values across those columns that must be unique, and either column alone can repeat.

What's the non-error way to handle a UNIQUE conflict instead of catching the exception?

SQLite's ON CONFLICT upsert syntax — we tested INSERT ... ON CONFLICT(column) DO UPDATE SET ... and it updated the existing row with zero exception raised, in a single statement instead of insert-then-catch-then-update.

If I need to prevent more than one NULL, does UNIQUE do that?

No — we confirmed UNIQUE alone allows unlimited NULLs in that column. Enforcing 'at most one NULL' needs separate application logic or a partial unique index filtering out NULLs, not covered by a plain UNIQUE constraint.

Is this the same exception class as NOT NULL or CHECK violations?

Yes — we confirmed sqlite3 raises IntegrityError for all three (UNIQUE, NOT NULL, and CHECK violations), distinct from the OperationalError class most other sqlite3 errors in this series raise.

Written with AI assistance. Measured on 2026-10-03 with Python 3.13.5's built-in sqlite3 module, using a single-column UNIQUE, a composite two-column UNIQUE, repeated NULL inserts against a UNIQUE column, and an ON CONFLICT upsert. Every error string and result row below is copy-pasted output, not paraphrased. PlainHeaders is our extension; Python's sqlite3 is built in.