Adding Foreign Keys with NOT VALID and VALIDATE CONSTRAINT

The orders.customer_id column has always been “supposed to” reference customers.id, and after a data-quality incident the team wants the database to enforce it. The obvious statement — ALTER TABLE orders ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id) — ran in staging in two seconds. In production, orders has 180 million rows, and the statement holds SHARE ROW EXCLUSIVE locks on both tables while it checks every one of them. Inserts and updates on orders stop; so do writes to customers, which means sign-ups stop too. PostgreSQL’s answer is to add the key NOT VALID — enforced for new rows immediately, with no scan — and validate the existing rows in a second step that does not block writes. This guide covers the full sequence, including the orphan cleanup that validation will demand and the index most teams forget. It belongs to Adding Constraints Without Downtime.

Symptom / Error Signatures

These signs point to a blocking foreign-key addition, or to one about to fail:

• During a migration, pg_stat_activity shows sessions on both tables waiting with wait_event_type = 'Lock' behind ALTER TABLE orders ADD CONSTRAINT ... FOREIGN KEY.
• Writes to the parent table stall even though the migration only mentions the child table.
• Validation fails after scanning the whole table:

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

• After the key exists, deletes from customers become very slow, because each delete must search orders for referencing rows and there is no index on orders.customer_id.

Root Cause Analysis

A foreign key needs two guarantees: rows in the child table reference existing parent rows, and parent rows cannot be deleted or re-keyed while referenced. Adding one therefore locks both tables in SHARE ROW EXCLUSIVE mode, which conflicts with ROW EXCLUSIVE — the lock every INSERT, UPDATE and DELETE takes — and then, by default, checks every child row by looking up its parent. The lock is held until the check finishes.

NOT VALID changes only the second part. The constraint is created and its triggers start checking new and changed rows immediately, but existing rows are not scanned, so the locks are held for milliseconds. VALIDATE CONSTRAINT performs the scan later with weaker locks: SHARE UPDATE EXCLUSIVE on the child (compatible with ordinary DML) and ROW SHARE on the parent. Validation is also where orphans surface — rows that referenced missing parents before the constraint existed — and it fails on the first one it finds, after potentially long work.

Immediate Mitigation

1. If a plain ADD FOREIGN KEY is blocking now, cancel it. It is transactional and rolls back completely.

-- PostgreSQL · requires pg_signal_backend · safe: nothing is committed until the scan finishes
SELECT pid, now() - query_start AS running FROM pg_stat_activity
WHERE query ILIKE 'ALTER TABLE orders ADD CONSTRAINT%FOREIGN KEY%' AND state = 'active';
SELECT pg_cancel_backend(<pid>);

2. Find and fix orphans first. Run the check on a replica for large tables; decide per business rules whether to delete orphans, point them at a placeholder parent, or set the column to NULL.

-- PostgreSQL · read-only · anti-join finds child rows with no parent
SELECT o.customer_id, count(*) AS rows
FROM orders o
WHERE o.customer_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id)
GROUP BY o.customer_id
ORDER BY rows DESC
LIMIT 50;

3. Add the key as NOT VALID. New writes are checked from this point.

-- PostgreSQL · migration role · milliseconds of SHARE ROW EXCLUSIVE on both tables
-- WARNING: from COMMIT on, the application cannot write orphaned rows; confirm it never does.
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
COMMIT;
-- ROLLBACK PATH: ALTER TABLE orders DROP CONSTRAINT IF EXISTS orders_customer_fk;

4. Validate in a separate step. Run it outside peak hours if the table is very large, since it reads the whole child table and probes the parent’s primary key for every row.

-- PostgreSQL · migration role · SHARE UPDATE EXCLUSIVE; reads and writes continue
SET lock_timeout = '3s';
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;

Permanent Fix / Long-Term Pattern

Make the two-step form the house style for every foreign key on an existing table, with each step in its own migration so the brief exclusive lock and the long scan never share a transaction. Most migration tools support it directly: Rails has add_foreign_key ..., validate: false and validate_foreign_key, Django has AddConstraintNotValid and ValidateConstraint, and hand-written SQL works everywhere else — see Rails Active Record Migrations for the Rails spelling.

Always index the referencing column. PostgreSQL requires an index on the referenced key (the parent’s primary key has one) but not on the referencing column, and without it every delete or key update on the parent scans the child table while holding locks. Build it concurrently before or after adding the key:

-- PostgreSQL · must run outside a transaction · SHARE UPDATE EXCLUSIVE, writes continue
SET lock_timeout = '3s';
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_customer_id_idx ON orders (customer_id);
-- ROLLBACK PATH: DROP INDEX CONCURRENTLY IF EXISTS orders_customer_id_idx;

Choose ON DELETE behaviour deliberately. CASCADE on a large child table turns one parent delete into an unbounded child delete inside the same transaction; for big tables, RESTRICT (the default NO ACTION) plus an explicit, batched cleanup job is usually safer. Keep the constraint clean over time with the verification query below, and consider the ordering of adding keys during a table split, as covered in splitting a wide table into two.

Verification Checklist

• The orphan anti-join returns zero rows before VALIDATE runs.
• The NOT VALID addition and the VALIDATE step run in separate transactions, both with lock_timeout.
• SELECT convalidated FROM pg_constraint WHERE conname = 'orders_customer_fk' returns true.
• An index exists on orders.customer_id, confirmed valid in pg_index.
• Application errors with SQLSTATE 23503 stayed at zero after the constraint was added.

Frequently Asked Questions

Why does adding a foreign key to the child table block the parent table? Because the constraint also restricts the parent — referenced rows cannot be deleted or re-keyed — so PostgreSQL takes SHARE ROW EXCLUSIVE on both tables while creating it. With NOT VALID that lock is held only for milliseconds.

Are new rows checked while the constraint is NOT VALID? Yes. Enforcement starts as soon as the constraint is committed; only rows that existed beforehand are unchecked until validation.

Can validation run while the application is writing? Yes. VALIDATE CONSTRAINT takes SHARE UPDATE EXCLUSIVE on the child and ROW SHARE on the parent, both compatible with ordinary inserts, updates and deletes. It does conflict with other DDL and with VACUUM on the same table.

What if validation fails halfway through? It rolls back, and the constraint stays in place as NOT VALID, still protecting new writes. Fix the violating rows it reported and run VALIDATE CONSTRAINT again.