insert or update on table violates foreign key constraint

What it means

A foreign key links a column in a child table (invoices.customer_id) to a key in a parent table (customers.id). PostgreSQL checks it in both directions, and the error comes in two forms.

On the child, the value you’re writing has no matching parent:

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

On the parent, you’re deleting a row, or changing its key, while child rows still reference it:

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

Either way the whole statement is rejected and nothing it touched is changed. A NULL in the child column passes the check (unless the column is NOT NULL); only non-null values must match.

Common causes

1. Inserting in the wrong order. Children are loaded before their parents: an import that does tables alphabetically, a fixture file, two services writing at once.
2. A wrong or stale id: a typo, an id from another environment, a parent deleted a moment earlier, or an id from the application that was never saved.
3. Deleting a parent that still has children, with the default ON DELETE NO ACTION.
4. Changing a primary key value that other tables reference.
5. Adding a foreign key to existing data that already has orphans. ALTER TABLE … ADD FOREIGN KEY checks every row and fails with the first form of the error.
6. Two tables that reference each other, where neither row can go in first.

How to fix it

Insert parents first

Create the parent row, then the child. For imports, load tables in dependency order: parents, then the tables that reference them. If you can’t control the order, see deferred constraints below.

Find the missing parents

To see which child values have no parent:

SELECT p.*
FROM payments p
LEFT JOIN invoices i ON i.id = p.invoice_id
WHERE p.invoice_id IS NOT NULL AND i.id IS NULL;

Then add the missing parents, set those children’s column to NULL, or delete them.

Delete or move the children first

Before deleting a parent, deal with what points at it:

DELETE FROM invoices WHERE customer_id = 1;
DELETE FROM customers WHERE id = 1;

To find which tables reference a parent:

SELECT conname, conrelid::regclass AS child, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE contype = 'f' AND confrelid = 'customers'::regclass;

Choose what a delete should do

If deleting a parent should always remove or detach its children, say so in the constraint:

ALTER TABLE invoices
  DROP CONSTRAINT invoices_customer_id_fkey,
  ADD CONSTRAINT invoices_customer_id_fkey
    FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE SET NULL;

ON DELETE CASCADE deletes the children with the parent; ON DELETE SET NULL keeps them with an empty reference. Use CASCADE only when the children mean nothing without the parent. Adding the constraint again checks every row; see adding a foreign key for the locks it takes on a busy table.

TRUNCATE on a referenced table fails with a different error, cannot truncate a table referenced in a foreign key constraint; truncate both tables in one statement, or use TRUNCATE … CASCADE, which empties every referencing table too.

Defer the check to commit

When rows must go in out of order, or two tables reference each other, make the constraint deferrable and defer it inside the transaction. It’s then checked at COMMIT:

ALTER TABLE invoices ALTER CONSTRAINT invoices_customer_id_fkey DEFERRABLE INITIALLY IMMEDIATE;

BEGIN;
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO invoices VALUES (12, 3, 40);
INSERT INTO customers VALUES (3, 'Linus');
COMMIT;

Adding a foreign key to dirty data

Add it as NOT VALID so new writes are checked straight away, clean up the old orphans with the query above, then validate:

ALTER TABLE payments ADD CONSTRAINT payments_invoice_id_fkey
  FOREIGN KEY (invoice_id) REFERENCES invoices (id) NOT VALID;
ALTER TABLE payments VALIDATE CONSTRAINT payments_invoice_id_fkey;

VALIDATE fails with this same error while any orphan remains.

Reproduce it

On PostgreSQL 18.6:

CREATE TABLE customers (id int PRIMARY KEY, name text);
CREATE TABLE invoices (id int PRIMARY KEY, customer_id int REFERENCES customers (id), total numeric);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Grace');
INSERT INTO invoices VALUES (10, 1, 100);

INSERT INTO invoices VALUES (11, 99, 50);
DELETE FROM customers WHERE id = 1;
TRUNCATE customers;

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

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

ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "invoices" references "customers".
HINT:  Truncate table "invoices" at the same time, or use TRUNCATE ... CASCADE.

UPDATE customers SET id = 100 WHERE id = 1 gives the same error as the delete. Inserting an invoice with a NULL customer succeeds, and DELETE FROM customers WHERE id = 2 succeeds because nothing references customer 2. After switching the constraint to ON DELETE SET NULL, deleting customer 1 succeeds and invoice 10 keeps its row with an empty customer_id.

Adding a foreign key over an orphan (payments.invoice_id = 77, no invoice 77):

ERROR:  insert or update on table "payments" violates foreign key constraint "payments_invoice_id_fkey"
DETAIL:  Key (invoice_id)=(77) is not present in table "invoices".

PostgreSQL 14, 15, 16 and 17 print the same messages.

In Inlet

The ER diagram shows which tables reference which, so you can see the order rows have to go in and come out. Grid edits are staged and saved together in one transaction when you commit (⌘S), and Review shows the exact SQL first. When a statement fails, Inlet shows the error at the position the server reports; with your own Anthropic API key, Ask Claude (⌘L) can fix it, sending the schema, the SQL and the error, never rows.