Re: Potential bug: Enforcing/not enforcing a CHECK constraint fails on an empty table - Mailing list pgsql-bugs

Hello

>"NOT ENFORCED constraints can be useful as documentation if the actual checking of the constraint at run time is too expensive."

I understand this is the primary purpose. 

However, the inconsistency I'm pointing out is that this "documentation-only" state appears to be modifiable for FOREIGN KEY constraints (using ALTER TABLE ... ENFORCED), but not for CHECK constraints.

This leads to the core of my question: Is this difference in behavior intentional?

If NOT ENFORCED constraints are not meant to be altered after creation, then it seems the ability to enforce a foreign key is the unexpected behavior. If they are meant to be alterable, then the failure to enforce a check constraint seems to be the bug.

>"NOT VALID", which is what I suspect is what you're after:

Thank you for the suggestion. I am familiar with the NOT VALID state for deferring validation of existing data. My investigation, however, is focused specifically on the behavior of the NOT ENFORCED state.

Best regards

Erki Eessaar

On Tue, 30 Sept 2025 at 22:07, Erki Eessaar <erki.eessaar@taltech.ee> wrote:
> I'm testing the new NOT ENFORCED constraint feature in the PostgreSQL 18.0 version on Ubuntu.
>
> I have observed what appears to be a bug: attempting to enforce a CHECK constraint with ALTER TABLE ... ENFORCED fails even when the table is empty. Interestingly, enforcing a FOREIGN KEY constraint under the same conditions succeeds as expected. I observed the same inconsistency in the reverse operation: changing an ENFORCED constraint to NOT ENFORCED succeeded for a FOREIGN KEY, but the same action failed for a CHECK constraint.
>
> I couldn't find any mention of this specific behavior in the documentation for CREATE TABLE or ALTER TABLE. Is this difference in behavior between CHECK and FOREIGN KEY constraints intentional, or is it a bug?

If you'd looked at the ALTER TABLE documentation about NOT ENFORCED,
then I'm not surprised you landed here. It seems to say next to
nothing about what it is.

If you look in CREATE TABLE, you'll get more [1]. In particular, this part:

"NOT ENFORCED constraints can be useful as documentation if the actual
checking of the constraint at run time is too expensive."

That's meant to indicate that this is just a metadata-only constraint
to assist in description of intent. Maybe it could be made more clear
somehow. Maybe it should mention that this shouldn't be confused with
"NOT VALID", which is what I suspect is what you're after:

# create table a (a int, constraint a_chk check(a > 0) not valid);
CREATE TABLE
# insert into a values(1);
INSERT 0 1
# alter table a validate constraint a_chk;
ALTER TABLE

David

[1] https://www.postgresql.org/docs/current/sql-createtable.html