The Subtle Shift: Understanding NOT VALID vs. NOT ENFORCED Constraints in PostgreSQL
PostgreSQL is famous for its data integrity, but as datasets grow into terabytes, the cost of maintaining that integrity can become a bottleneck. To solve this, PostgreSQL offers different "states" for constraints.
While many are familiar with NOT VALID, a new concept from the SQL:2023 standard has entered the conversation: NOT ENFORCED, which has been officially added to PostgreSQL 18. While they might sound similar, they serve very different roles in a database's lifecycle.
TL;DR
In PostgreSQL, constraints aren't always strictly "on" or "off". NOT VALID allows you to add a constraint to a massive table instantly without a long lock; it enforces the rule for new data while ignoring legacy data until you are ready to validate it. NOT ENFORCED tells PostgreSQL the constraint exists for documentation purposes (though you can enable enforcement later), but the engine won’t actually prevent anyone from breaking it. Note that as of PostgreSQL 18, NOT ENFORCED state is supported for CHECK and FOREIGN KEY constraints only.
Why NOT ENFORCED when we already have NOT VALID ?
To understand the need for NOT ENFORCED, we first have to look at the limitations of the existing NOT VALID feature.
- NOT VALID: This is a temporary migration state. When you add a constraint as NOT VALID, PostgreSQL says: "I won't check the old data right now (saving time), but I will strictly enforce this rule for every new row added from this point forward." Eventually, you are expected to run a validation scan to make the constraint fully active.
- NOT ENFORCED: This is a functional metadata state. You are telling PostgreSQL: "I want you to know this relationship exists for documentation or query optimization purposes, but do not check it for new or old data."
The Key Difference: NOT VALID still runs checks new data; NOT ENFORCED skips those checks entirely.
The Evolution of Foreign Keys: Skipping Triggers
One of the most significant architectural changes involves how Foreign Keys (FKs) behave when created as NOT ENFORCED.
CHECK constraints are relatively simple; they do not rely on other database objects to maintain data integrity. In contrast, standard Foreign Key constraints rely on internal triggers that execute on every INSERT, UPDATE, or DELETE.
When a Foreign Key is defined as NOT ENFORCED, PostgreSQL skips the creation of these internal triggers and simply sets the flags in the system catalog – much like a CHECK constraint. This results in a massive performance win for high-volume write operations, as the triggers are only recreated automatically if the constraint is later altered to ENFORCED.
Moving between 'Not Enforced' and 'Enforced' :
What happens when you want to shift the responsibility of integrity back to the database? Moving from an unenforced state to an enforced one requires a strict validation process.
1. Creating a Constraint as Not Enforced
You define the relationship without the performance penalty of triggers.
ALTER TABLE orders ADD CONSTRAINT fk_product
FOREIGN KEY (product_no) REFERENCES products(product_no)
NOT ENFORCED;2. Switching to Enforced (The Validation Scan)
When you decide the database should take over enforcement, you "flip the switch."
ALTER TABLE orders ALTER CONSTRAINT fk_product ENFORCED;During this alteration, validation will be performed. PostgreSQL cannot blindly trust data added while the constraint was unenforced; therefore, it performs a validation scan to ensure every row complies with the rule before the constraint is being set to enforced. For Foreign Key constraints, the required internal triggers are created to perform integrity checks, and a full scan is executed to perform the validation check. If a single invalid row is found, the command fails.
In PostgreSQL 18, this alteration currently applies only to Foreign Key constraints. Support for altering the enforceability of CHECK constraints is under active discussion and has been proposed for future versions on the PostgreSQL development mailing list.
3. Moving back to Not Enforced
If you need to perform a massive bulk load and want to disable the trigger overhead:
ALTER TABLE orders ALTER CONSTRAINT fk_product NOT ENFORCED;Summary: Which one should you use?
- Use NOT VALID if you intend to have the database strictly enforce the rules, but you need to add the constraint to a massive table without a long-term "Access Exclusive" lock.
- Use NOT ENFORCED if your application already manages data integrity and you want the performance benefits of skipping check or trigger execution. While the Query Optimizer does not currently use these constraints to speed up SELECT queries, defining them still serves as vital schema documentation and future-proofs your database for when planner support is eventually added.