// SQLite error reference

SQLITE_CONSTRAINT_FOREIGNKEY: FOREIGN KEY constraint failed

A write would leave a child row pointing at a parent that doesn't exist, or delete a parent that still has children. Foreign keys must be switched on per connection.

Result code
SQLITE_CONSTRAINT_FOREIGNKEY
Name
787
Category
Extended result codes
Error message
FOREIGN KEY constraint failed

What it means

SQLITE_CONSTRAINT_FOREIGNKEY (787) means a foreign key check failed. SQLite only enforces foreign keys on connections that run PRAGMA foreign_keys = ON; it is off by default for backward compatibility, which is why the same insert can succeed in one tool and fail in another.

The message doesn't say which key failed. PRAGMA foreign_key_check lists every violating row.

Common causes

  • Inserting a child row whose parent id doesn't exist.
  • Deleting or updating a parent row that still has children, without ON DELETE CASCADE or SET NULL.
  • Loading tables in the wrong order: children before parents.
  • Deferred constraints (DEFERRABLE INITIALLY DEFERRED) failing at COMMIT.

How to fix it

  1. Find the rows that break a foreign key.

    PRAGMA foreign_key_check;
    PRAGMA foreign_key_list(orders);
  2. Insert parents before children, or delete children before parents.

  3. For bulk loads, defer checking to the end of the transaction.

    BEGIN;
    PRAGMA defer_foreign_keys = ON;
    -- load tables in any order
    COMMIT;
  4. If deleting a parent should remove its children, declare the action on the foreign key.

    CREATE TABLE orders (
        id          INTEGER PRIMARY KEY,
        customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE
    );

Reproduce it safely

PRAGMA foreign_keys = ON;
CREATE TABLE p (id INTEGER PRIMARY KEY);
CREATE TABLE c (pid INTEGER REFERENCES p(id));
INSERT INTO c VALUES (1);  -- Runtime error: FOREIGN KEY constraint failed (19)

Questions

Why are foreign keys not enforced in my SQLite database?

Enforcement is per connection and off by default. Each connection has to run PRAGMA foreign_keys = ON, and it cannot be changed inside a transaction.

Related errors

More extended 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.