Fix PostgreSQL 'insert or update violates foreign key constraint' Error

TL;DR

You're inserting or updating a row whose foreign key value doesn't exist in the referenced table. The parent record is missing, you're passing the wrong ID, or your inserts are in the wrong order. Fix: make sure the parent row exists first, then retry.

The Error

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".

Read the DETAIL line carefully — it does half the debugging for you. Here it says customer_id = 999 has no matching row in customers. That's your exact problem, right there.

Root Cause

PostgreSQL enforces referential integrity at write time. Every value you put into a foreign key column must already exist in the parent table. No exceptions — the insert is rejected outright if the parent row is missing.

This usually happens because of one of these:

The parent record was never created (customer wasn't registered before the order was placed)
Wrong ID being passed — off-by-one error, stale cached value, or a bug in your app logic
Batch inserts are in the wrong order — child rows land before parent rows
A migration or seed script skipped the parent table entirely
A parent row was deleted without ON DELETE CASCADE or ON DELETE SET NULL, leaving orphaned children

Step 1: Confirm the Missing Parent Row

Before touching anything, verify that the referenced ID actually exists:

-- Does customer 999 exist?
SELECT * FROM customers WHERE id = 999;

No rows returned? That's your problem. The parent record is gone — or was never created.

To see exactly how the constraint is defined, inspect it directly:

-- Quick way in psql
\d orders

-- Or query the system catalog
SELECT
  conname AS constraint_name,
  conrelid::regclass AS table_name,
  a.attname AS column_name,
  confrelid::regclass AS referenced_table,
  af.attname AS referenced_column
FROM pg_constraint c
JOIN pg_attribute a ON a.attnum = ANY(c.conkey) AND a.attrelid = c.conrelid
JOIN pg_attribute af ON af.attnum = ANY(c.confkey) AND af.attrelid = c.confrelid
WHERE c.contype = 'f'
  AND c.conrelid = 'orders'::regclass;

Fix Approaches

Fix 1: Insert the Parent Row First

Nine times out of ten, this is the fix. Create the missing parent record, then retry the child insert:

-- Create the customer first
INSERT INTO customers (id, name, email)
VALUES (999, 'Alice', 'alice@example.com');

-- Now the order insert will go through
INSERT INTO orders (id, customer_id, total)
VALUES (1, 999, 150.00);

Fix 2: Use the Correct Existing ID

Sometimes the parent record exists — you're just passing the wrong ID. Look it up first:

-- Find Alice's actual ID
SELECT id FROM customers WHERE email = 'alice@example.com';
-- Returns: 42

-- Use 42, not 999
INSERT INTO orders (customer_id, total)
VALUES (42, 150.00);

Never hardcode IDs across environments. A customer inserted in production will almost certainly have a different ID than in staging.

Fix 3: Wrap in a Transaction (for batch inserts)

Inserting a batch of related rows? Wrap everything in a transaction and put parents first:

BEGIN;

INSERT INTO customers (id, name) VALUES (100, 'Bob');
INSERT INTO orders (id, customer_id, total) VALUES (1, 100, 75.00);

COMMIT;

If either insert fails, the whole transaction rolls back. No orphaned rows, no partial state.

Fix 4: Temporarily Defer the Constraint (migrations only)

Bulk migrations sometimes make insert ordering impractical. If your constraint was created as DEFERRABLE, you can push the check to end-of-transaction:

-- Only works if the constraint is DEFERRABLE
BEGIN;
SET CONSTRAINTS orders_customer_id_fkey DEFERRED;

-- Insert in any order within this transaction
INSERT INTO orders (id, customer_id, total) VALUES (1, 100, 75.00);
INSERT INTO customers (id, name) VALUES (100, 'Bob');

COMMIT;  -- FK check happens here

If your constraint isn't deferrable yet, recreate it:

ALTER TABLE orders
  DROP CONSTRAINT orders_customer_id_fkey,
  ADD CONSTRAINT orders_customer_id_fkey
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    DEFERRABLE INITIALLY IMMEDIATE;

Fix 5: Disable the Constraint (last resort, migrations only)

Only reach for this during controlled migrations. Never in production application code:

-- Disable FK enforcement
ALTER TABLE orders DISABLE TRIGGER ALL;

-- ... bulk insert ...

-- Re-enable
ALTER TABLE orders ENABLE TRIGGER ALL;

-- Then immediately check for orphans
SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

Run that orphan check every time after re-enabling. Skip it and you'll have silent data integrity problems that surface at the worst possible moment.

Verification

Retry the insert and confirm the data landed correctly:

-- Retry
INSERT INTO orders (id, customer_id, total)
VALUES (1, 999, 150.00);

-- Verify with a join
SELECT o.id, o.total, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.id = 1;

A clean join result means referential integrity is intact.

Prevention

A few habits that keep this error away for good:

Insert in dependency order in seed scripts and migrations — parents always before children.
Define ON DELETE CASCADE or ON DELETE SET NULL when deleting parent rows is expected, so child rows are handled automatically instead of becoming orphans.
Use RETURNING id when inserting parent rows — capture the actual generated ID and pass it directly to child inserts.
In ORMs (SQLAlchemy, Hibernate, ActiveRecord), configure cascade options at the model level rather than juggling IDs manually.

-- Capture the generated ID with RETURNING
INSERT INTO customers (name, email)
VALUES ('Carol', 'carol@example.com')
RETURNING id;
-- Returns: id = 43

-- Use it immediately
INSERT INTO orders (customer_id, total)
VALUES (43, 200.00);