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

Overview

This error fires on the child side of a foreign key. You inserted or updated a child row whose referenced value does not exist in the parent table, so Postgres rejects it to keep referential integrity intact.

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

The DETAIL line is precise: the child key customer_id = 999 has no matching row in customers. This is the mirror image of the parent-side error update or delete ... violates foreign key constraint, which fires when you try to remove a parent that children still reference. Here the parent simply is not there.

Symptoms

• An INSERT INTO orders (...) or UPDATE orders SET customer_id = ... fails with the message above.
• The DETAIL line reports Key (...) is not present in table "...".
• The same insert succeeds for known-good customer IDs but fails for a specific or newly generated one.

INSERT INTO orders (customer_id, total_cents, status)
VALUES (999, 4200, 'paid');

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

Common Root Causes

1. The parent row does not exist (or is not committed yet)

Either customer 999 was never created, or it was inserted in another transaction that has not committed, so this transaction cannot see it.

SELECT id FROM customers WHERE id = 999;

 id
----
(0 rows)

2. Wrong or stale foreign-key value

The application passed an ID from the wrong table, an old cached value, or a placeholder like 0/999 that never mapped to a real customer.

3. Insert order — child before parent

A batch or ORM flush wrote the orders row before the customers row it depends on.

4. Type or value mismatch on the key

A subtle mismatch (whitespace in a text key, a uuid typed as text, a truncated integer) means the lookup finds no match even though a “similar” parent appears to exist.

5. Parent deleted concurrently

Another transaction deleted customer 999 between the time the app read it and the time it inserted the order.

How to diagnose

Step 1: Confirm the parent exists

Check the parent table for the exact key value from the DETAIL line.

SELECT id, email FROM customers WHERE id = 999;

If this returns zero rows, the reference is genuinely dangling and the insert is correct to fail.

Step 2: Inspect the foreign key definition

Verify which columns the constraint links so you are checking the right parent column.

SELECT conname,
       conrelid::regclass  AS child_table,
       confrelid::regclass AS parent_table,
       pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conname = 'orders_customer_id_fkey';

        conname          | child_table | parent_table |                    definition
-------------------------+-------------+--------------+---------------------------------------------------
 orders_customer_id_fkey | orders      | customers    | FOREIGN KEY (customer_id) REFERENCES customers(id)
(1 row)

Step 3: Verify insert ordering within the transaction

If parent and child are written together, confirm the parent INSERT runs and commits before the child. Test the exact sequence in one session:

BEGIN;
INSERT INTO customers (id, email) VALUES (999, 'buyer999@appdb.example');
INSERT INTO orders (customer_id, total_cents, status) VALUES (999, 4200, 'paid');
COMMIT;

If this succeeds while the app fails, the app is writing the child first.

Fixes

Insert (and commit) the parent first

Ensure the referenced customer exists before the order references it.

INSERT INTO customers (id, email)
VALUES (999, 'buyer999@appdb.example')
ON CONFLICT (id) DO NOTHING;

INSERT INTO orders (customer_id, total_cents, status)
VALUES (999, 4200, 'paid');

Correct the foreign-key value

If 999 was a mistake, use the real customer ID.

INSERT INTO orders (customer_id, total_cents, status)
VALUES (42, 4200, 'paid');

Use a deferrable constraint for circular or batch inserts

Deferring the check to commit time lets you insert interdependent rows in any order within one transaction.

ALTER TABLE orders
  ALTER CONSTRAINT orders_customer_id_fkey DEFERRABLE INITIALLY DEFERRED;

BEGIN;
INSERT INTO orders (customer_id, total_cents, status) VALUES (999, 4200, 'paid');
INSERT INTO customers (id, email) VALUES (999, 'buyer999@appdb.example');
COMMIT;  -- constraint checked here, both rows now present

Upsert the parent as part of the flow

When importing data where parents may or may not exist, upsert the parent then insert the child.

WITH parent AS (
  INSERT INTO customers (id, email)
  VALUES (999, 'buyer999@appdb.example')
  ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email
  RETURNING id
)
INSERT INTO orders (customer_id, total_cents, status)
SELECT id, 4200, 'paid' FROM parent;

What to watch out for

• An uncommitted parent in another transaction is invisible here; the parent must be committed (or written in the same transaction) before the child can reference it.
• DEFERRABLE INITIALLY DEFERRED only moves the check to commit — both rows must exist by COMMIT, or the whole transaction still rolls back.
• Watch for type mismatches on the key (text vs uuid, trailing whitespace); the row can look present yet not match.
• ON CONFLICT DO NOTHING on the parent avoids duplicate-key errors but make sure it does not mask a genuinely wrong ID.
• Under concurrency, wrap the read-parent-then-insert-child logic so a concurrent delete of the parent cannot slip between them.

Related

• Postgres Error: ‘there is no unique constraint matching given keys’
• Postgres Error: ‘null value violates not-null constraint’
• Postgres Error: ‘update or delete on table violates foreign key constraint’