// PostgreSQL error reference

PostgreSQL 23503: foreign key constraint violation

A row references a parent key that doesn't exist, or you deleted or changed a parent row that child rows still reference.

SQLSTATE
23503
Name
foreign_key_violation
Category
Integrity Constraint Violation
Error message
insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(42) is not present in table "customers".

What it means

A foreign key says every value in the child column must exist in the referenced (parent) column. PostgreSQL raises 23503 in two directions: when you insert or update a child row whose key has no parent (is not present in table), and when you delete or update a parent row that child rows still point to (is still referenced from table).

The DETAIL line gives the key value and the other table, so you know which rows to look at.

Common causes

  • Inserting child rows before their parent rows, for example loading orders before customers.
  • A typo or stale id in the child row.
  • Deleting a parent row that still has children, without ON DELETE CASCADE or SET NULL on the foreign key.
  • Updating a parent's primary key while children reference the old value.
  • Two transactions: one inserts a child for a parent another just deleted.

How to fix it

  1. Find the missing parent or the remaining children.

    SELECT * FROM customers WHERE id = 42;
    SELECT * FROM orders WHERE customer_id = 42;
  2. Insert parents first, or load both tables in one transaction with the constraint deferred (it must be declared DEFERRABLE).

    ALTER TABLE orders ALTER CONSTRAINT orders_customer_id_fkey DEFERRABLE INITIALLY IMMEDIATE;
    BEGIN;
    SET CONSTRAINTS orders_customer_id_fkey DEFERRED;
    INSERT INTO orders (id, customer_id) VALUES (1, 42);
    INSERT INTO customers (id, name) VALUES (42, 'Ada');
    COMMIT;  -- checked here
  3. When deleting a parent, delete or reassign its children first, or change the foreign key's delete rule if that is the behavior you want.

    ALTER TABLE orders
        DROP CONSTRAINT orders_customer_id_fkey,
        ADD CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id)
            REFERENCES customers (id) ON DELETE CASCADE;
  4. Find orphans before adding a foreign key to existing data.

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

Reproduce it safely

CREATE TEMP TABLE p (id int PRIMARY KEY);
CREATE TEMP TABLE c (p_id int REFERENCES p (id));
INSERT INTO c VALUES (1);  -- ERROR: ... violates foreign key constraint "c_p_id_fkey"

Related errors

More integrity constraint violation errors

The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.

// fewer errors, faster fixes

Work on PostgreSQL in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.