SQLite: FOREIGN KEY constraint failed

Result code

SQLITE_CONSTRAINT (19); extended SQLITE_CONSTRAINT_FOREIGNKEY (787)

Product

SQLite library

Versions

SQLite 3.6.19 and later (foreign key enforcement); examples run on 3.39.2, shell output from 3.50.6

Category

Constraint Violations

Last reviewed

24 October 2026

What it means

This error means a write would leave a child row pointing at a parent row that does not exist, so SQLite rejected it; the message does not say which table or key.

A foreign key (REFERENCES customers (id)) requires every non-NULL value in the child column to exist in the parent key. SQLite checks it on every INSERT and UPDATE of the child table and on every DELETE or key UPDATE of the parent. An immediate constraint fails the statement at once; a DEFERRABLE INITIALLY DEFERRED constraint is checked at COMMIT, and the COMMIT fails while violations remain (the transaction stays open, so you can fix the data or roll back). Unlike the other constraint messages, this one names no table or column: use PRAGMA foreign_key_list and PRAGMA foreign_key_check to find it.

Foreign keys are off by default in SQLite, and the setting belongs to the connection, not the database file: each connection must run PRAGMA foreign_keys = ON (outside a transaction, where it is silently ignored). That is why the same code can fail in one tool and succeed in another, and why a database can already contain orphan rows that were written while enforcement was off. With DROP TABLE on a parent, SQLite first performs an implicit DELETE, so dropping a referenced table fails with this error too. If the parent key itself is not a PRIMARY KEY or UNIQUE column, SQLite reports a different error, "foreign key mismatch", instead.

Common causes

• A child row is inserted before its parent, or with a parent id that was mistyped, never created or belongs to another database.
• A parent row is deleted (or its key changed) while child rows still reference it and the foreign key has no ON DELETE or ON UPDATE action.
• DROP TABLE on a parent table that still has referencing rows.
• A bulk load or migration inserts tables in the wrong order, or a deferred constraint is still violated when the transaction commits.

Diagnose it

Read-only These statements only read the schema (sqlite_schema and read-only PRAGMA queries) or your own data. They do not change data, settings or the database file, so they are safe to run first.

PRAGMA foreign_keys;

PRAGMA foreign_key_list(orders);

PRAGMA foreign_key_check;
PRAGMA foreign_key_check(orders);

SELECT o.id, o.customer_id, o.total
FROM orders AS o
WHERE o.customer_id = 1;  -- the parent key you want to delete

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

BEGIN;
INSERT INTO customers (id, name) VALUES (99, 'Sara');
INSERT INTO orders (customer_id, total) VALUES (99, 10);
COMMIT;

BEGIN;
DELETE FROM orders WHERE customer_id = 1;
DELETE FROM customers WHERE id = 1;
COMMIT;

CREATE TABLE order_notes (
    id       INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
    note     TEXT
);

BEGIN;
PRAGMA defer_foreign_keys = ON;
INSERT INTO orders (customer_id, total) VALUES (100, 25);
INSERT INTO customers (id, name) VALUES (100, 'Ali');
COMMIT;

Examples

Inserting an order for a customer that does not exist

PRAGMA foreign_keys = ON;

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers (id),
    total       REAL
);

INSERT INTO orders (customer_id, total) VALUES (99, 10);  -- no customer 99

FOREIGN KEY constraint failed

Deleting a customer who still has orders

PRAGMA foreign_keys = ON;

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers (id), total REAL);
INSERT INTO customers (id, name) VALUES (1, 'Sara');
INSERT INTO orders (customer_id, total) VALUES (1, 10);

DELETE FROM customers WHERE id = 1;

FOREIGN KEY constraint failed

Dropping a parent table that is still referenced

PRAGMA foreign_keys = ON;

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers (id), total REAL);
INSERT INTO customers (id, name) VALUES (1, 'Sara');
INSERT INTO orders (customer_id, total) VALUES (1, 10);

DROP TABLE customers;  -- runs an implicit DELETE first

FOREIGN KEY constraint failed

A deferred foreign key fails at COMMIT

PRAGMA foreign_keys = ON;

CREATE TABLE orders (id INTEGER PRIMARY KEY, total REAL);
CREATE TABLE order_lines (
    id       INTEGER PRIMARY KEY,
    order_id INTEGER REFERENCES orders (id) DEFERRABLE INITIALLY DEFERRED
);

BEGIN;
INSERT INTO order_lines (order_id) VALUES (500);  -- accepted for now
COMMIT;  -- fails; the transaction is still open
ROLLBACK;

FOREIGN KEY constraint failed

How to prevent it

Turn foreign keys on in every connection your application opens, right after connecting and before any transaction (most frameworks have a connection hook or option for this). Then run PRAGMA foreign_key_check once on existing databases: rows written while enforcement was off are not checked retroactively when you turn it on.

Write parents before children and delete children before parents, inside one transaction. Index the child key columns (for example CREATE INDEX ix_orders_customer ON orders (customer_id)), because every parent delete or key change has to search the child table. Do not switch foreign keys off to make an error go away: SQLite will not re-check the rows written meanwhile, and the orphans surface later as missing joins and wrong totals.

Common questions

Learn more on CodeWithSQL

• Primary key vs foreign key
• SQL constraints explained

Official documentation

• SQLite foreign key support
• PRAGMA foreign_key_check
• SQLite result and error codes
• Quirks: foreign key enforcement is off by default

Last technically reviewed 24 October 2026; examples and diagnostics run on SQLite 3.39.2 (PHP PDO_SQLITE) and the sqlite3 shell 3.50.6. Messages are the text the SQLite library itself returns (sqlite3_errmsg) for the versions shown.