// SQLite error reference

SQLITE_CONSTRAINT: constraint failed

An INSERT or UPDATE broke a constraint: UNIQUE, PRIMARY KEY, NOT NULL, CHECK or FOREIGN KEY. The message and extended code name which one.

Result code
SQLITE_CONSTRAINT
Name
19
Category
Primary result codes
Error message
constraint failed

What it means

SQLITE_CONSTRAINT (19) means a statement would have broken one of the table's rules, so SQLite stopped it. The generic text is constraint failed, but the message usually names the rule and column, such as UNIQUE constraint failed: users.email, and the extended code tells you the kind: SQLITE_CONSTRAINT_UNIQUE, _PRIMARYKEY, _NOTNULL, _CHECK, _FOREIGNKEY, _DATATYPE (STRICT tables) or _TRIGGER (a RAISE() in a trigger).

By default the failing statement is undone (conflict mode ABORT) but earlier statements in the same transaction are kept.

Common causes

  • A duplicate value in a primary key or unique column.
  • A NULL in a NOT NULL column, often because the insert left the column out and it has no default.
  • A value that fails a CHECK expression.
  • A child row whose parent doesn't exist, with PRAGMA foreign_keys = ON.
  • A value of the wrong type in a STRICT table.

How to fix it

  1. Read the message or the extended code to see which constraint failed, then look at the table definition.

    SELECT sql FROM sqlite_schema WHERE name = 'users';
  2. For duplicates you want to merge or skip, use an upsert or a conflict clause.

    INSERT INTO users (email, name) VALUES ('[email protected]', 'Ada')
    ON CONFLICT (email) DO UPDATE SET name = excluded.name;
  3. For the other constraints, fix the data you are writing: supply the missing value, insert the parent row first, or correct the value that fails the check.

Related errors

More primary result codes errors

The code and message text are from the SQLite result and error codes documentation. Browse the full SQLite error list or search every database error.

// fewer errors, faster fixes

Work on SQLite in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQLite and the other databases you use.