🚨 Postgres Error: 'update or delete on table violates foreign key constraint' — Cause, Fix, and Troubleshooting Guide

Overview

This error fires on the parent side of a foreign key. You tried to delete a parent row (or change its referenced key), but child rows still point at it, and the constraint has no ON DELETE/ON UPDATE action that would clean them up.

ERROR:  update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"
DETAIL:  Key (id)=(42) is still referenced from table "orders".

The DETAIL line names the parent key value (id = 42) and the child table (orders) that still references it. By default a foreign key is ON DELETE NO ACTION, so Postgres blocks the operation rather than orphaning the children. Contrast this with the child-side error insert or update ... violates foreign key constraint, which fires when a child points at a parent that does not exist.

Symptoms

• A DELETE FROM customers WHERE id = 42 or an UPDATE of a referenced primary key fails with the message above.
• The DETAIL line reports Key (...) is still referenced from table "...".
• Deleting a “leaf” row works, but deleting a row that other tables reference fails.

DELETE FROM customers WHERE id = 42;

ERROR:  update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"
DETAIL:  Key (id)=(42) is still referenced from table "orders".

Common Root Causes

1. Child rows still reference the parent

The most common cause: orders still has rows whose customer_id = 42.

SELECT id, customer_id, status
FROM orders
WHERE customer_id = 42;

  id   | customer_id | status
-------+-------------+---------
 90311 |          42 | shipped
 90544 |          42 | paid
(2 rows)

2. The foreign key has no ON DELETE CASCADE or SET NULL

With the default NO ACTION, the parent cannot be removed while children exist; the constraint does not clean up for you.

3. Wrong deletion order

Deleting the parent before its children (instead of children first) triggers the block on any batch or manual cleanup.

4. Updating a referenced primary key

Changing customers.id from 42 to something else is treated the same as deleting 42: the old value is still referenced, so the update is rejected.

How to diagnose

Step 1: Find the referencing children

Query the child table directly using the key value from the DETAIL line.

SELECT count(*) AS referencing_rows
FROM orders
WHERE customer_id = 42;

If this returns anything above 0, those rows are what block the parent operation.

Step 2: Inspect the foreign key’s delete/update action

\d orders in psql shows the FK, or query pg_constraint for the machine-readable action codes.

SELECT conname,
       confdeltype,   -- a = NO ACTION, r = RESTRICT, c = CASCADE, n = SET NULL, d = SET DEFAULT
       confupdtype
FROM pg_constraint
WHERE conname = 'orders_customer_id_fkey';

        conname          | confdeltype | confupdtype
-------------------------+-------------+-------------
 orders_customer_id_fkey | a           | a
(1 row)

confdeltype = a (NO ACTION) confirms nothing will auto-clean the children.

Step 3: Enumerate every table that references the parent

A parent can be referenced by more than one child table; list them all before deleting.

SELECT conrelid::regclass AS child_table, conname
FROM pg_constraint
WHERE confrelid = 'customers'::regclass
  AND contype = 'f';

Fixes

Delete or repoint the children first

Remove the child rows (or move them to another parent) before deleting the parent.

DELETE FROM orders WHERE customer_id = 42;
DELETE FROM customers WHERE id = 42;

To reassign instead of delete:

UPDATE orders SET customer_id = 7 WHERE customer_id = 42;
DELETE FROM customers WHERE id = 42;

Add ON DELETE CASCADE or SET NULL to the FK

If children should follow the parent automatically, redefine the constraint. CASCADE deletes the children; SET NULL keeps them but clears the reference.

ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey;
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_id_fkey
  FOREIGN KEY (customer_id) REFERENCES customers (id)
  ON DELETE CASCADE;

Use a deferrable constraint for complex batch operations

A deferrable constraint checks at commit time, so intermediate states within one transaction are allowed.

ALTER TABLE orders
  ALTER CONSTRAINT orders_customer_id_fkey DEFERRABLE INITIALLY DEFERRED;

Adopt a soft-delete pattern

Instead of physically deleting referenced parents, mark them inactive so history and references stay intact.

ALTER TABLE customers ADD COLUMN deleted_at timestamptz;
UPDATE customers SET deleted_at = now() WHERE id = 42;

What to watch out for

• ON DELETE CASCADE will silently delete child rows across every cascading table — confirm you actually want that data gone.
• ON DELETE SET NULL requires the child FK column to be nullable; it fails against a NOT NULL column.
• Deferring a constraint only relaxes when it is checked, not whether — the commit still fails if children remain unresolved.
• Multiple child tables may reference the same parent; fix every one the pg_constraint query lists, not just the one named in the first error.
• Updating a primary key that is referenced elsewhere is usually a design smell; prefer a stable surrogate key that never changes.

Related

• Postgres Error: ‘insert or update on table violates foreign key constraint’
• Postgres Error: ‘null value violates not-null constraint’
• Postgres Error: ‘relation does not exist’