docs/reference/rules/constraint-missing-not-valid.md
Diagnostic Category: lint/safety/constraintMissingNotValid
Since: vnext
Sources:
Adding constraints without NOT VALID blocks all reads and writes.
When adding a CHECK or FOREIGN KEY constraint, PostgreSQL must validate all existing rows, which requires a full table scan. This blocks reads and writes for the duration.
Instead, add the constraint with NOT VALID first, then VALIDATE CONSTRAINT in a separate transaction. This allows reads and writes to continue while validation happens.
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address);
code-block.sql:1:1 lint/safety/constraintMissingNotValid ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
! Adding a constraint without NOT VALID will block reads and writes while validating existing rows.
> 1 │ ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address);
│ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2 │
i Add the constraint as NOT VALID in one transaction, then run VALIDATE CONSTRAINT in a separate transaction.
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID;
{
"linter": {
"rules": {
"safety": {
"constraintMissingNotValid": "error"
}
}
}
}