Field guide 03

Add the foreign key now. Validate the history separately.

NOT VALID lets PostgreSQL enforce new writes without immediately proving every old row. That separates the schema change from the potentially long validation scan.

The risky shape

ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id)
  REFERENCES customers(id);
Release verdict: REVISE, or BLOCK on large busy tables without evidence.Immediate validation can scan existing rows and the foreign-key operation takes locks on both the referencing and referenced tables.

A staged pattern

1. Check existing violations

SELECT o.customer_id, count(*)
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
GROUP BY o.customer_id
LIMIT 100;

2. Add enforcement for new writes

SET lock_timeout = '2s';

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

3. Validate in an observed operation

ALTER TABLE orders
  VALIDATE CONSTRAINT orders_customer_fk;

Validation still does real work. Schedule it with table size, write activity, replication lag, long transactions, and cancellation criteria in view.

Index evidence

PostgreSQL does not automatically create an index on the referencing columns. Review whether deletes or key updates on the referenced table need an index on orders(customer_id), and build it with its own safe deployment plan.

Evidence gate

  • Both-table row counts, sizes, write rates, and long transactions are measured.
  • Existing orphan rows are counted and repaired or explicitly accepted.
  • Delete and update behavior is defined, including cascade effects.
  • The referencing-column index decision is supported by workload evidence.
  • Validation monitoring, cancellation, retry, and rollback ownership are assigned.

Rollback boundary

Before application behavior depends on the constraint, rollback can remove it after confirming that no deployment assumes enforcement. Once new code relies on rejected writes or cascade behavior, dropping the constraint changes application semantics and needs a coordinated rollback.

Primary references