Databases · Senior · Question 35 of 75
How do you add a foreign key or NOT NULL check to a big table without blocking writes for a long time?
- 1Constraints can only be added to empty tables
- ✓Correct answer: Add the constraint as
NOT VALID(quick), then runVALIDATE CONSTRAINT, which checks existing rows with a weaker lock - 3Drop the table and recreate it with the constraint
Explanation
For NOT NULL, add a CHECK (col IS NOT NULL) NOT VALID, validate it, then SET NOT NULL, which can use the validated check and skip a full scan.