SQL Foreign Key Constraint Fails (tested)

A foreign key constraint links a column in one table to a primary key in another, so every value in the "child" column must already exist in the "parent" table. The error fires in two opposite situations: inserting a child row that points at a parent which doesn't exist, or deleting a parent row that a child still points at. Both are the database refusing to create an orphaned reference.

1. Reading the error: two directions, two messages

PostgreSQL's wording differs depending on which side of the relationship you violated:

-- inserting a child that points at a missing parent
ERROR:  insert or update on table "orders" violates foreign key constraint "orders_user_id_fkey"
DETAIL:  Key (user_id)=(999) is not present in table "users".

-- deleting a parent that a child still references
ERROR:  update or delete on table "users" violates foreign key constraint "orders_user_id_fkey" on table "orders"
DETAIL:  Key (id)=(1) is still referenced from table "orders".

Both name the same constraint (orders_user_id_fkey), but the first DETAIL says a key is not present (you're pointing at something that doesn't exist), and the second says a key is still referenced (something is still pointing at what you're trying to remove). Reading which of the two you have tells you immediately which table to fix: the child row in the first case, or the dependents in the second.

2. Reproduced: inserting into the child table

psql> INSERT INTO orders (user_id, amount) VALUES (999, 10);
ERROR:  insert or update on table "orders" violates foreign key constraint "orders_user_id_fkey"
DETAIL:  Key (user_id)=(999) is not present in table "users".

Fix: insert the parent row first, or correct the foreign key value to one that already exists. If this is happening inside application code, check for a race — something may be inserting the child before the parent's transaction has committed.

3. Reproduced: deleting from the parent table

psql> DELETE FROM users WHERE id = 1;
ERROR:  update or delete on table "users" violates foreign key constraint "orders_user_id_fkey" on table "orders"
DETAIL:  Key (id)=(1) is still referenced from table "orders".

Postgres refuses by default (ON DELETE NO ACTION, the default if you don't specify otherwise) rather than silently leaving orders rows pointing at a user that no longer exists.

4. Fix: decide what should happen to the children, then declare it

The constraint's ON DELETE clause controls this. We tested the two most common choices, each on its own table with a fresh row:

CASCADE — delete the children along with the parent:

CREATE TABLE orders2 (id SERIAL PRIMARY KEY, user_id INT REFERENCES users(id) ON DELETE CASCADE, amount NUMERIC);
-- insert a row for user 3, then:
psql> DELETE FROM users WHERE id = 3;
DELETE 1
-- the orders2 row for user 3 is gone too, no error

SET NULL — keep the child row, but blank out the reference:

CREATE TABLE orders3 (id SERIAL PRIMARY KEY, user_id INT REFERENCES users(id) ON DELETE SET NULL, amount NUMERIC);
-- insert a row for user 4, then:
psql> DELETE FROM users WHERE id = 4;
DELETE 1
psql> SELECT * FROM orders3;
 id | user_id | amount
----+---------+--------
  1 |       2 |     40
  2 |         |     40
(2 rows)

Row 2's user_id is now blank (NULL) instead of the deleted user's id, and the row still exists. Use CASCADE when the child data is meaningless without the parent (e.g. a user's saved addresses). Use SET NULL when the child should survive independently (e.g. an order history row shouldn't vanish just because the account was deleted) — but only if the column allows NULL.

Without either, the default behaviour (which produced the error in section 3) is usually right for data you cannot afford to silently delete or orphan — add CASCADE or SET NULL deliberately, not as a reflex to make the error go away.

5. Alternative: check before you delete

If you want to decide per-case rather than declare a blanket rule, check for dependents first and handle it in application logic:

psql> SELECT EXISTS(SELECT 1 FROM orders WHERE user_id = 2);
 exists
--------
 t
(1 row)

t means at least one order still references user 2 — deleting that user will fail (or cascade/null out orders, depending on the constraint) unless you handle those rows first.

6. Other databases (per their docs, not measured on this box)

MySQL (InnoDB) raises ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails for the insert case, and ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails for the delete case. SQLite raises a plain FOREIGN KEY constraint failed for both (foreign keys must also be turned on explicitly with PRAGMA foreign_keys = ON;, unlike Postgres and MySQL where they're always enforced). ON DELETE CASCADE/SET NULL syntax is the same across all three.

Written with AI assistance. Measured on 2026-10-03 with PostgreSQL 16.15 (official Docker image), using psql. Every error message below is copy-pasted output, not paraphrased. MySQL and SQLite enforce the same rule but phrase the error differently (per their docs, not measured here). PlainHeaders is our extension; PostgreSQL and Docker are free.