Foreign Key Violation Adding Constraint With Orphans

A foreign key is a promise that every referencing value already exists in the parent. When you run ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY in its normal (validating) form, PostgreSQL will not <a class="sev1-termlink" href="https://thesev1database.com/glossary/tuple/">record that promise until it has proven it is true for the current contents of the table, so it scans the whole table and checks each row against the parent.

The moment it finds a row that violates the promise, it raises foreign_key_violation (SQLSTATE 23503) and rolls the ALTER back -- the constraint is never added. The DETAIL line reports only the FIRST offending key it happened to reach, which is why fixing that one value and re-running just surfaces the next orphan. The validating scan also takes an ACCESS EXCLUSIVE-style share-row-exclusive lock for its entire duration, so on a large table the failed migration both blocks writers and wastes the whole scan.

The orphans exist in the first place precisely because there was no foreign key enforcing the relationship while those rows were written. The orphans exist in the first place precisely because there was no foreign key enforcing the relationship while those rows were written.

Free Tier -- Basic Approach

The error names only one bad value. A single anti-join -- orders LEFT JOIN customers where the parent IS NULL -- counts every row that will block the constraint, so you know how dirty the data is before you touch it.

SELECT count(*) AS orphaned_orders
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

orphaned_orders
-----------------
               5
(1 row)

Run the anti-join count against the table the migration failed on. orphaned_orders = 5 here; on a real table the number tells you whether this is a five-row cleanup or a thousands-row data problem.

The safe online pattern, in order: add the constraint NOT VALID (it enforces new writes at once and skips the up-front validating scan), confirm a fresh orphan write is already rejected, clean the existing orphans, then VALIDATE the constraint against the remaining rows.

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

ALTER TABLE

INSERT INTO orders(customer_id, amount) VALUES (2000000, 1.00);

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

DELETE FROM orders WHERE customer_id NOT IN (SELECT id FROM customers);
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey;

DELETE 5
ALTER TABLE

ALTER TABLE orders ADD CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customers(id) NOT VALID returns ALTER TABLE immediately. A new orphan INSERT (customer_id=2000000) is rejected with 23503 -- proof the constraint is enforced for new writes.

DELETE FROM orders WHERE customer_id NOT IN (SELECT id FROM customers) removes the 5 existing orphans (DELETE 5). ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey then checks the surviving rows under a weaker lock and returns ALTER TABLE.

Healthy state is three facts together: zero orphans remain, the constraint reports convalidated = t (fully trusted, not just NOT VALID), and a brand-new orphan write is rejected -- the data is clean and the foreign key is enforced both ways.

SELECT count(*) AS orphaned_orders
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

orphaned_orders
-----------------
               0
(1 row)

SELECT conname, convalidated
FROM pg_constraint
WHERE conrelid = 'orders'::regclass AND contype = 'f';

conname         | convalidated
-------------------------+--------------
 orders_customer_id_fkey | t
(1 row)

INSERT INTO orders(customer_id, amount) VALUES (3000000, 1.00);

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

The anti-join now returns orphaned_orders = 0. pg_constraint shows orders_customer_id_fkey with convalidated = t. A fresh orphan INSERT (customer_id=3000000) is rejected with the foreign-key-violation error, confirming enforcement.

📦 Version Matrix (PG 14--18)

Same mechanism on PostgreSQL 14, 15, 16, 17 and 18: with orphans present the validating ADD CONSTRAINT fails with "violates foreign key constraint"; after the orphans are deleted the identical ADD CONSTRAINT succeeds (ALTER TABLE).

Frequently Asked Questions

References

• ALTER TABLE -- ADD table_constraint [ NOT VALID ] and VALIDATE CONSTRAINT
• Constraints -- Foreign Keys
• PostgreSQL Error Codes (Class 23 -- Integrity Constraint Violation, 23503 foreign_key_violation)
• pg_constraint system catalog (convalidated column)