PostgreSQL Error 23503

SQLSTATE

23503

Condition name

foreign_key_violation

Product

PostgreSQL

Versions

PostgreSQL 13 to 18 (examples run on PostgreSQL 18.6 in PGlite)

Category

Integrity Constraints

Last reviewed

24 October 2026

What it means

SQLSTATE 23503 means a row would point at a parent row that does not exist, or a parent row that other rows still point at would be deleted or changed, so PostgreSQL rejected the statement to keep the foreign key valid.

A foreign key says that every non-null value in the child columns must exist in the referenced (parent) key. PostgreSQL checks this from both sides and raises 23503 (foreign_key_violation) in two forms. The child side, "insert or update on table ... violates foreign key constraint", means the new or changed child row names a parent that is not there; DETAIL shows the value: Key (customer_id)=(99) is not present in table "customers". The parent side, "update or delete on table ... violates foreign key constraint ... on table ...", means you tried to delete a parent row, or change its key, while child rows still reference it; DETAIL ends with is still referenced from table "orders".

The two forms need opposite fixes, so read the first word of the message: "insert or update" is a missing parent, "update or delete" is a parent with children. The same error appears when ALTER TABLE ... ADD FOREIGN KEY finds existing orphan rows, and at COMMIT when the constraint is DEFERRABLE INITIALLY DEFERRED. Inside a transaction it aborts the transaction, so later statements fail with 25P02 until you ROLLBACK.

Common causes

• Child rows are inserted before their parent rows, for example an import that loads orders before customers, or two inserts run in the wrong order.
• The application sends an id that does not exist: a typo, a stale id from a cache, an id from another environment, or 0 used where NULL was meant.
• A DELETE removes a parent row that still has child rows, and the foreign key uses the default ON DELETE NO ACTION (or RESTRICT).
• An UPDATE changes a referenced primary key value, leaving its child rows pointing at the old value.
• A foreign key is added to a table that already contains orphan rows, so ALTER TABLE ... ADD FOREIGN KEY fails while checking them.

Diagnose it

Read-only These queries only read the system catalogs (pg_catalog, information_schema) and statistics views such as pg_stat_activity and pg_locks. They do not change data, roles or settings, so they are safe to run first.

SELECT conname,
       conrelid::regclass  AS child_table,
       confrelid::regclass AS parent_table,
       pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE contype = 'f'
  AND confrelid = 'customers'::regclass
ORDER BY conname;

SELECT *
FROM customers
WHERE id = 99;  -- the value from the DETAIL line

SELECT id, customer_id
FROM orders
WHERE customer_id = 1;  -- the key from "is still referenced"

SELECT o.id, o.customer_id
FROM orders AS o
WHERE o.customer_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM customers AS c WHERE c.id = o.customer_id);

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

BEGIN;
INSERT INTO customers (id, name) VALUES (99, 'Ali');
INSERT INTO orders (id, customer_id, total) VALUES (11, 99, 25);
COMMIT;

BEGIN;
DELETE FROM orders WHERE customer_id = 1;
DELETE FROM customers WHERE id = 1;
COMMIT;

BEGIN;
ALTER TABLE order_lines DROP CONSTRAINT order_lines_order_id_fkey;
ALTER TABLE order_lines
    ADD CONSTRAINT order_lines_order_id_fkey
    FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE;
COMMIT;

ALTER TABLE orders
    ADD CONSTRAINT orders_customer_fk
    FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;

Examples

Inserting an order for a customer that does not exist

CREATE TABLE customers (id int PRIMARY KEY, name text);
CREATE TABLE orders (
    id          int PRIMARY KEY,
    customer_id int REFERENCES customers (id),
    total       numeric
);

INSERT INTO customers VALUES (1, 'Sara');
INSERT INTO orders VALUES (10, 99, 5);

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

Deleting a customer who still has orders

Changing the parent key with UPDATE customers SET id = 2 WHERE id = 1 gives exactly the same message, because the old key is still referenced.

CREATE TABLE customers (id int PRIMARY KEY, name text);
CREATE TABLE orders (
    id          int PRIMARY KEY,
    customer_id int REFERENCES customers (id),
    total       numeric
);

INSERT INTO customers VALUES (1, 'Sara');
INSERT INTO orders VALUES (10, 1, 5);

DELETE FROM customers WHERE id = 1;

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

Adding a foreign key to a table with orphan rows

CREATE TABLE customers (id int PRIMARY KEY, name text);
CREATE TABLE orders (id int, customer_id int);
INSERT INTO orders VALUES (1, 7);

ALTER TABLE orders
    ADD CONSTRAINT orders_customer_fk
    FOREIGN KEY (customer_id) REFERENCES customers (id);

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

A deferred foreign key fails at COMMIT, not at the INSERT

The INSERT succeeds and the error is raised by COMMIT, which then rolls the whole transaction back. Inserting customer 5 anywhere before COMMIT would have made it succeed.

CREATE TABLE customers (id int PRIMARY KEY);
CREATE TABLE orders (
    id          int,
    customer_id int REFERENCES customers DEFERRABLE INITIALLY DEFERRED
);

BEGIN;
INSERT INTO orders VALUES (1, 5);  -- accepted for now
COMMIT;                            -- the check runs here

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

How to prevent it

Load and write data in dependency order: parents before children on insert, children before parents on delete. Decide the ON DELETE rule for every foreign key when you design the table (NO ACTION, CASCADE, SET NULL) instead of discovering it when a delete fails.

Index the referencing columns yourself: PostgreSQL indexes the referenced key but not the child columns, and without an index every parent delete or key change scans the child table. In application code, handle SQLSTATE 23503 and turn it into a clear message such as "this customer still has orders", and never try to work around the constraint by disabling triggers; that leaves orphan rows the database can no longer trust.

Common questions

Learn more on CodeWithSQL

• Primary key vs foreign key
• SQL constraints explained

Official documentation

• PostgreSQL error codes (Appendix A)
• Constraints: foreign keys
• Tutorial: foreign keys
• ALTER TABLE (NOT VALID and VALIDATE CONSTRAINT)

Last technically reviewed 24 October 2026; examples and diagnostics run on PostgreSQL 18.6 (PGlite 0.5.11). SQL-level examples were run in PGlite (PostgreSQL compiled to WebAssembly); examples that need a running server are shown as documented in the PostgreSQL documentation.