How do you add a foreign key or CHECK constraint to a huge table without a long lock?
Add it as NOT VALID, which skips the check of existing rows and only needs a brief lock, then run VALIDATE CONSTRAINT separately. Validation scans the table but only takes a weaker lock, so writes continue.
Explain it simply
Adding a rule to a full register normally means checking all old entries immediately, with the register locked. With NOT VALID you say "enforce this rule for new entries from now on, and I will audit the old ones later". The audit (VALIDATE) can be done calmly while normal work continues.
Example
-- Step 1: quick; new and changed rows are checked, old rows are not
ALTER TABLE orders
ADD CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
-- Step 2: scans existing rows; does not block normal reads and writes
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;
-- Same for CHECK
ALTER TABLE orders
ADD CONSTRAINT total_positive CHECK (total > 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT total_positive;If bad old rows exist, VALIDATE fails and tells you so; fix the data and run it again. The same trick works for a NOT NULL: add a validated CHECK (col IS NOT NULL) NOT VALID, validate it, and then SET NOT NULL (version 12 and later uses the check to skip the scan).
What to say in the interview
"I add the constraint NOT VALID so only a short lock is needed, then VALIDATE CONSTRAINT in a second step, which takes a lighter lock and lets traffic continue. It is the standard safe-migration pattern for big tables."
Common mistakes
- Forgetting to validate, leaving old rows unchecked and the constraint marked as not validated.