Re: Potential bug: Enforcing/not enforcing a CHECK constraint fails on an empty table

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
failseven when the table is empty. Interestingly, enforcing a FOREIGN KEY constraint under the same conditions succeeds
asexpected. I observed the same inconsistency in the reverse operation: changing an ENFORCED constraint to NOT ENFORCED
succeededfor 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
differencein 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