EC
23503
PostgreSQLERRORCommonIntegrity Constraint ViolationHIGH confidence

insert or update on table violates foreign key constraint

What this means

Postgres attempted to insert or update a row in a child table but the referenced value does not exist in the parent table. The referential integrity check runs after the heap write is staged and before it is committed, aborting the statement on failure.

Why it happens
  1. 1Inserting a row in the child table with a foreign key value that has no matching primary key in the parent table
  2. 2Updating a foreign key column to a value that does not exist in the parent table
  3. 3Loading child rows before parent rows during a data migration or bulk import
  4. 4Deleting or updating the parent row after the child was created without CASCADE configured
How to reproduce

An orders table references a customers table; an order is inserted for a non-existent customer.

trigger — this will error
trigger — this will error
CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  customer_id INT REFERENCES customers(id),
  amount      NUMERIC
);

INSERT INTO orders (customer_id, amount) VALUES (999, 49.99); -- triggers 23503

expected output

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

Fix 1

Ensure parent row exists before inserting child

WHEN When the parent record is legitimately missing and must be created first.

Ensure parent row exists before inserting child
INSERT INTO customers (id, name) VALUES (999, 'New Customer')
ON CONFLICT (id) DO NOTHING;

INSERT INTO orders (customer_id, amount) VALUES (999, 49.99);

Why this works

Foreign key checks in Postgres are enforced by a trigger attached to the child table (RI_FKey_check_ins). By inserting the parent row first within the same transaction (or a committed earlier transaction) the trigger finds the referenced tuple in the parent heap and allows the insert.

Fix 2

Defer the constraint within a transaction

WHEN When inserting parent and child rows in the same transaction and insertion order cannot be controlled (e.g. during migrations).

Defer the constraint within a transaction
ALTER TABLE orders
  DROP CONSTRAINT orders_customer_id_fkey,
  ADD CONSTRAINT orders_customer_id_fkey
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    DEFERRABLE INITIALLY DEFERRED;

BEGIN;
INSERT INTO orders (customer_id, amount) VALUES (999, 49.99);
INSERT INTO customers (id, name) VALUES (999, 'New Customer');
COMMIT; -- FK check runs here

Why this works

DEFERRABLE INITIALLY DEFERRED moves the referential integrity check from statement time to transaction commit time. Postgres queues the check via a deferred trigger and runs all queued checks during COMMIT, by which point the parent row exists.

What not to do

✕ Drop the foreign key constraint to silence the error

Eliminates referential integrity: orphaned child rows accumulate and JOIN queries return incorrect results.

Sources
Official documentation ↗

src/backend/utils/adt/ri_triggers.c — RI_FKey_check_ins()

Foreign Key Constraints ↗

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors