In PostgreSQL 18, which constraints can be added NOT VALID, and how does a NOT VALID constraint differ from a NOT ENFORCED one?
answer
- two flags in pg_constraint
- are new writes checked?
- index-backed kinds excluded
- 18 adds not-null to the list
- VALIDATE refuses the unenforced
basics
~20 sIn PostgreSQL 18, FOREIGN KEY, CHECK and now NOT NULL constraints can be added NOT VALID: new writes are checked, old rows wait for VALIDATE CONSTRAINT. NOT ENFORCED, new in 18 for CHECK and FOREIGN KEY, checks nothing at all.
solid answer
~40 s`NOT VALID` skips the scan of existing rows but enforces the rule on every new insert and update; `VALIDATE CONSTRAINT` later checks the old rows under `SHARE UPDATE EXCLUSIVE` and sets `pg_constraint.convalidated` to true. It applies to `FOREIGN KEY`, `CHECK` and, since 18 catalogues not-null constraints, `NOT NULL`, but never to index-backed `UNIQUE`, `PRIMARY KEY` or `EXCLUDE`. `NOT ENFORCED`, new in 18 for `CHECK` and `FOREIGN KEY` only, is a declaration the server never checks: no row is verified, a foreign key gets no triggers, and both `conenforced` and `convalidated` are false. You cannot `VALIDATE` it. A foreign key can be switched on with `ALTER CONSTRAINT ... ENFORCED`, which checks every row inside an `ACCESS EXCLUSIVE` `ALTER TABLE`; a `CHECK` must be dropped and added again.
code
sql · 15 lines-- checked for new rows at once; old rows later
ALTER TABLE booking
ADD CONSTRAINT booking_max_8h
CHECK (upper(during) - lower(during) <= interval '8 hours') NOT VALID;
ALTER TABLE booking VALIDATE CONSTRAINT booking_max_8h;
-- 18: catalogued NOT NULL that can be NOT VALID
ALTER TABLE booking
ADD CONSTRAINT booking_booked_by_nn NOT NULL booked_by NOT VALID;
ALTER TABLE booking VALIDATE CONSTRAINT booking_booked_by_nn;
-- 18: declared, never checked
ALTER TABLE booking
ADD CONSTRAINT booking_booked_by_fk
FOREIGN KEY (booked_by) REFERENCES employee (employee_id) NOT ENFORCED;go deeper
Recall that NOT VALID skips checking existing rows but still checks new ones, and that VALIDATE CONSTRAINT finishes the job later.
Explain which kinds accept NOT VALID in 18 and why index-backed ones cannot, then how NOT ENFORCED differs in what gets checked and what pg_constraint records.
Show the operational traps: an invalid not-null blocking a primary key, ALTER CONSTRAINT ENFORCED scanning under ACCESS EXCLUSIVE, and auditing convalidated and conenforced across a schema.
Judge when a documentation-only constraint is acceptable versus a liability, and what process guarantees that NOT VALID constraints are actually validated rather than forgotten.
## Two flags, three states PostgreSQL 18 keeps two independent flags on each `pg_constraint` row: **`convalidated`** (have all existing rows been checked?) and **`conenforced`** (is the rule checked on writes at all?). Together they give three states worth knowing: | State | New and updated rows | Existing rows | `convalidated` | `conenforced` | Allowed for | |---|---|---|---|---|---| | normal | checked | checked when added | true | true | every kind | | `NOT VALID` | checked | not yet checked | false | true | `FOREIGN KEY`, `CHECK`, `NOT NULL` (18) | | `NOT ENFORCED` | not checked | never checked | false | false | `FOREIGN KEY`, `CHECK` (18) | ## NOT VALID: enforce now, prove the past later `ALTER TABLE ... ADD CONSTRAINT ... NOT VALID` skips the scan of existing rows, so the command commits quickly. From then on every insert and update is checked: a booking that breaks a `NOT VALID` `CHECK` fails with `23514`. `ALTER TABLE ... VALIDATE CONSTRAINT` later scans the old rows. It needs only a **`SHARE UPDATE EXCLUSIVE`** lock on the table, plus **`ROW SHARE`** on the referenced table for a foreign key, because concurrent writers are already being checked. If an old row violates the rule, validation fails; fix the rows and run it again. (The engine-neutral rollout story is the relational-constraints topic; the lock taken by the initial `ADD` is the schema-changes topic.) Which kinds accept `NOT VALID`: - **`FOREIGN KEY`** and **`CHECK`**, for many releases; in 18 also foreign keys on partitioned tables. - **`NOT NULL`**, new in 18. Not-null constraints now have their own `pg_constraint` rows (`contype = 'n'`), so they can be named and carry flags: `ADD CONSTRAINT booking_booked_by_nn NOT NULL booked_by NOT VALID`. - **Not** `UNIQUE`, `PRIMARY KEY` or `EXCLUDE`. These are enforced by an index, and building the index checks every row. The online path for `UNIQUE` and `PRIMARY KEY` is `CREATE UNIQUE INDEX CONCURRENTLY` followed by `ADD CONSTRAINT ... USING INDEX`; an exclusion constraint has no equivalent. Two side effects of the unvalidated state are easy to miss: 1. **The planner ignores an unvalidated `CHECK`.** Constraint exclusion uses only validated check constraints, so a `NOT VALID` check cannot prune anything yet. 2. **An invalid not-null constraint is half there.** It rejects new NULLs and psql's `\d` shows the column as `not null`, yet `ADD PRIMARY KEY` on that column fails ("marked NOT VALID, is incompatible with a primary key") until you validate it. `VALIDATE CONSTRAINT`, or `ALTER COLUMN ... SET NOT NULL`, does that by scanning. ## NOT ENFORCED: a declared rule nobody checks `NOT ENFORCED` is accepted only on `CHECK` and `FOREIGN KEY`; `UNIQUE`, `PRIMARY KEY`, `EXCLUDE` and `NOT NULL` reject it ("... constraints cannot be marked NOT ENFORCED"). The constraint is stored, and `pg_get_constraintdef()` prints it with `NOT ENFORCED`, so readers and tools see how the data is meant to relate. The server checks nothing: a not-enforced foreign key has no referential-integrity triggers at all, and correctness is the application's job. Typical uses are documenting a relationship to a table filled by a separate pipeline, or a rule too expensive to check on every write. ## Switching enforcement on - `VALIDATE CONSTRAINT` on a not-enforced constraint fails with "cannot validate NOT ENFORCED constraint": validation presumes enforcement. - For a **foreign key**, `ALTER TABLE ... ALTER CONSTRAINT name ENFORCED` creates the triggers and checks every existing row as part of that `ALTER TABLE`, which holds `ACCESS EXCLUSIVE` throughout. On a large table, add a fresh foreign key `NOT VALID`, `VALIDATE` it, then drop the not-enforced one. - For a **`CHECK`**, 18 cannot alter enforceability ("cannot alter enforceability of constraint"): drop it and add it again, preferably `NOT VALID` followed by `VALIDATE`. - Going the other way, `ALTER CONSTRAINT ... NOT ENFORCED` on a foreign key drops its triggers and marks it unvalidated again, so turning it back on later costs another full check of the table. ## Auditing a schema Because a not-enforced constraint is also recorded with `convalidated = false`, read both flags together: ```sql SELECT conrelid::regclass AS table_name, conname, contype, convalidated, conenforced, pg_get_constraintdef(oid) AS definition FROM pg_constraint WHERE NOT convalidated OR NOT conenforced ORDER BY 1, 2; ``` `conenforced AND NOT convalidated` is a validation still owed; `NOT conenforced` is a rule the database deliberately does not check.
- How do you list every PostgreSQL 18 constraint that still needs validation?Query `pg_constraint` for `conenforced AND NOT convalidated`, showing `conrelid::regclass`, `conname`, `contype` and `pg_get_constraintdef(oid)`. Filtering on `NOT convalidated` alone also returns `NOT ENFORCED` constraints, which are recorded as unvalidated but can never be validated, so keep the `conenforced` test. psql's `\d` also marks such constraints `NOT VALID`.
- What does an invalid NOT NULL constraint block in PostgreSQL 18?New NULLs are already rejected, and `\d` shows the column as `not null`, but the column is not trusted yet: `ADD PRIMARY KEY` on it fails, and so does turning it into an identity column, each with a hint to run `VALIDATE CONSTRAINT`. Validate it, or run `ALTER COLUMN ... SET NOT NULL`, which validates by scanning the table.
- How would you start enforcing a NOT ENFORCED foreign key on a 300-million-row PostgreSQL 18 bookings table?Avoid `ALTER CONSTRAINT ... ENFORCED`: it checks every row inside an `ALTER TABLE` holding `ACCESS EXCLUSIVE`. Instead add a second foreign key on the same columns with `NOT VALID`, which commits quickly and checks new writes, run `VALIDATE CONSTRAINT` under `SHARE UPDATE EXCLUSIVE`, and then drop the not-enforced declaration.
- A NOT VALID constraint lets new violating rows in until VALIDATE CONSTRAINT runs.
- UNIQUE constraints can be added NOT VALID to tolerate existing duplicates.
- VALIDATE CONSTRAINT on a NOT ENFORCED foreign key switches enforcement on.
- A NOT ENFORCED CHECK can be enabled later with ALTER CONSTRAINT ... ENFORCED.
- VALIDATE CONSTRAINT takes ACCESS EXCLUSIVE and blocks every write while it scans.
- Even in PostgreSQL 18, NOT NULL can only be added with a blocking full-scan SET NOT NULL.
Assumes PostgreSQL 18: NOT ENFORCED and pg_constraint.conenforced, catalogued NOT NULL constraints that accept NOT VALID, and NOT VALID foreign keys on partitioned tables are all new in 18.