PostgreSQL Error 23503: Foreign Key Violation

PostgreSQL SQLSTATE 23503 (foreign_key_violation) means a statement would leave a foreign-key relationship without a matching referenced row. It can happen when a child row refers to a parent that does not exist, when a parent row is deleted or its key is changed while child rows still refer to it, or when a foreign key is added to a table that already contains invalid references. PostgreSQL lists 23503 in its error-code appendix.

Start with the complete error detail and the named constraint. The constraint tells you which table and columns to inspect; the right fix depends on whether the failed statement changed the referencing table, the referenced table, or the constraint definition.

A child INSERT or UPDATE references a missing parent

If an INSERT or UPDATE sets a foreign-key value, check that the matching parent row exists in the same database and schema used by the application. For a foreign key from orders.customer_id to customers.customer_id, inspect the value reported in the error:

SELECT customer_id
FROM public.customers
WHERE customer_id = 42;

If no row is returned, verify the key value, the active database, and the intended parent table. Insert the parent first if it is genuinely missing, or correct the child value if it refers to the wrong parent. Do not insert a placeholder parent row just to suppress the error.

A parent DELETE or key UPDATE is blocked

The default foreign-key action is NO ACTION. Deleting a parent row or changing its referenced key fails while dependent child rows remain. Find those rows before deciding what should happen to them:

SELECT order_id, customer_id
FROM public.orders
WHERE customer_id = 42;

Keep the parent row if those child records must remain linked. If the application intends to remove or reassign them, do that explicitly in a transaction. A configured ON DELETE or ON UPDATE action can instead use CASCADE, SET NULL, or another policy; choose it based on the relationship’s meaning. CASCADE deletes or changes dependent data automatically, so it is not a general-purpose way to silence the error. See the PostgreSQL foreign-key guide and the foreign-key constraint documentation.

Adding a foreign key finds invalid existing rows

When ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY checks existing data, locate child rows whose non-null key has no parent match. For a single-column key:

SELECT o.order_id, o.customer_id
FROM public.orders AS o
LEFT JOIN public.customers AS c
  ON c.customer_id = o.customer_id
WHERE o.customer_id IS NOT NULL
  AND c.customer_id IS NULL;

Correct or remove the reported child rows only after confirming the intended data. For a composite foreign key, join on every corresponding key column and account for the constraint’s MATCH behavior; the default MATCH SIMPLE allows a referencing row to avoid a match when any of its referencing columns is NULL.

Inspect the foreign-key definition

Use the constraint name from the error to retrieve the definition on the referencing table:

SELECT
  conrelid::regclass AS referencing_table,
  confrelid::regclass AS referenced_table,
  conname,
  pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE contype = 'f'
  AND conrelid = 'public.orders'::regclass
  AND conname = 'orders_customer_id_fkey';

Replace the table and constraint names with those from your error. The result shows the referenced columns and any ON DELETE or ON UPDATE actions. In psql, \d public.orders also lists the table’s foreign keys.

Distinguish a foreign-key violation from other errors

23503 concerns a missing referenced row or a parent change blocked by dependent rows. 23505 (unique_violation) means a duplicate value conflicts with a unique key; see PostgreSQL Error 23505. For foreign-key syntax and design, see the PostgreSQL FOREIGN KEY tutorial or browse the PostgreSQL error troubleshooting index.