🚨 PostgreSQL Error Guide: 'violates foreign key constraint' — Fix

Overview

PostgreSQL raises a foreign key violation when a row references a parent key that doesn’t exist, or when deleting/updating a parent would strand child rows. The two directions produce two messages:

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(8831) is not present in table "customers".

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

The DETAIL line gives you the exact key value and both tables involved.

Symptoms

• An INSERT/UPDATE into a child table fails because the referenced parent row is missing.
• A DELETE or key UPDATE on a parent row fails because child rows still reference it.
• A bulk load or migration fails when tables are loaded in the wrong order.
• An ORM save/delete fails; cascading that the app expected isn’t configured in the database.
• Restoring a dump fails on constraint creation because of orphaned rows already present.

Common Root Causes

• Parent row doesn’t exist — the child references an id that was never inserted, was typo’d, or belongs to a different environment.
• Insert ordering — child rows inserted before their parents (common in bulk loads and fixtures).
• Parent deleted/updated with children still attached — and the FK has the default ON DELETE NO ACTION/RESTRICT rather than CASCADE or SET NULL.
• A prior transaction rolled back the parent insert but the child insert proceeded (in a different transaction).
• Type or collation mismatch between the FK column and the referenced key causing lookups to miss.
• Data imported out of a foreign key’s transactional scope, leaving orphans that a later ADD CONSTRAINT rejects.
• Race condition — the parent is deleted between the child’s read and its write under concurrent load.

Diagnostic Workflow

Read the DETAIL line to get the key value and direction (insert-side vs delete-side).

Inspect the constraint definition, including its ON DELETE / ON UPDATE action:

SELECT conname, confrelid::regclass AS parent, conrelid::regclass AS child,
       pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conname = 'orders_customer_id_fkey';

For an insert-side failure, confirm whether the parent key exists:

SELECT * FROM customers WHERE id = 8831;

For a delete-side failure, count the referencing children:

SELECT count(*) FROM orders WHERE customer_id = 8831;

Find all orphaned children (rows pointing at a non-existent parent) before adding a constraint:

SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL AND o.customer_id IS NOT NULL;

Check the column types match on both sides — a mismatch causes silent lookup misses:

SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE (table_name = 'orders' AND column_name = 'customer_id')
   OR (table_name = 'customers' AND column_name = 'id');

Example Root Cause Analysis

A nightly ETL job started failing on insert or update on table "line_items" violates foreign key constraint "line_items_order_id_fkey", with DETAIL: Key (order_id)=(55012) is not present in table "orders".

The job loaded line_items and orders from separate CSV files in parallel. Checking the parent:

SELECT * FROM orders WHERE id = 55012;
-- (0 rows)

Order 55012 existed in the source system but its row landed in a later CSV chunk than the line items that referenced it. Because the two loads ran concurrently and committed independently, line items for late-chunk orders were inserted before their parent orders existed.

The fix was to enforce ordering: load all orders to completion before loading any line_items, within a single transaction per batch. As an interim measure for the failing batch, the constraint was checked at end-of-load instead of per-row by declaring it deferrable and deferring it inside the transaction:

ALTER TABLE line_items
  ALTER CONSTRAINT line_items_order_id_fkey DEFERRABLE INITIALLY IMMEDIATE;

BEGIN;
SET CONSTRAINTS line_items_order_id_fkey DEFERRED;
-- load orders and line_items in any order within this transaction
COMMIT;   -- FK checked here, once both are present

Prevention Best Practices

• Insert parents before children, or load related tables inside one transaction and use a DEFERRABLE FK checked at commit.
• Choose the right referential action deliberately: ON DELETE CASCADE to remove children, ON DELETE SET NULL to detach them, or RESTRICT/NO ACTION to forbid deleting referenced parents.
• Index foreign key columns on the child side — Postgres does not do this automatically, and unindexed FKs make parent deletes and this check slow.
• Before adding a foreign key to existing data, run the LEFT JOIN orphan query and clean up, or add the constraint NOT VALID then VALIDATE CONSTRAINT after fixing orphans.
• Keep FK column types and collations identical on both sides of the reference.
• Handle the concurrent delete-vs-insert race with appropriate locking (SELECT ... FOR KEY SHARE on the parent) or retry logic.

Quick Command Reference

-- Show the constraint definition and its ON DELETE/UPDATE action
SELECT conname, pg_get_constraintdef(oid) FROM pg_constraint
WHERE conname = 'orders_customer_id_fkey';

-- Insert-side: does the parent exist?
SELECT * FROM customers WHERE id = 8831;

-- Delete-side: how many children still reference it?
SELECT count(*) FROM orders WHERE customer_id = 8831;

-- Find orphaned children before adding a constraint
SELECT o.* FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL AND o.customer_id IS NOT NULL;

-- Add FK to existing data without a full-table lock validation up front
ALTER TABLE orders ADD CONSTRAINT orders_customer_id_fkey
  FOREIGN KEY (customer_id) REFERENCES customers(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey;

Conclusion

Foreign key violations are Postgres protecting referential integrity, so the fix is almost always in the data or the ordering rather than the constraint. The DETAIL line tells you the direction: an insert-side failure means the parent is missing (fix ordering or insert the parent), while a delete-side failure means children still reference the row (cascade, detach, or delete children first). For bulk work, a DEFERRABLE constraint checked at commit removes ordering fragility, and indexing the child-side FK column keeps both the check and parent deletes fast. Clean up orphans before adding constraints, and pick your ON DELETE action on purpose.