Horton

Thread

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

    Erki Eessaar <erki.eessaar@taltech.ee> — 2025-09-30T08:56:27Z

    Hello,

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?

A reproducible test case is included below.

Best regards
Erki Eessaar
****************************************************

CREATE TABLE Service_status_type (service_status_type_code SMALLINT,
name VARCHAR(50) NOT NULL,
CONSTRAINT pk_service_status_type PRIMARY KEY (service_status_type_code),
CONSTRAINT ak_service_status_type_name UNIQUE (name));


CREATE TABLE Service (service_code INTEGER,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL,
service_status_type_code SMALLINT NOT NULL DEFAULT 1,
CONSTRAINT pk_service PRIMARY KEY (service_code),
CONSTRAINT ak_service_name UNIQUE (name),
CONSTRAINT chk_service_price CHECK(price>0) NOT ENFORCED,
CONSTRAINT fk_service_service_status_type FOREIGN KEY (service_status_type_code) REFERENCES Service_status_type (service_status_type_code) NOT ENFORCED);

INSERT INTO Service (service_code, name, price, service_status_type_code)
VALUES (1, 'Watch repair', -1, 2);
/*Succeeded*/

ALTER TABLE Service ALTER CONSTRAINT chk_service_price ENFORCED;
ERROR: cannot alter enforceability of constraint "chk_service_price" of relation "service"
/*Failed as expected*/

ALTER TABLE Service ALTER CONSTRAINT fk_service_service_status_type ENFORCED;
ERROR: insert or update on table "service" violates foreign key constraint "fk_service_service_status_type"
DETAIL: Key (service_status_type_code)=(2) is not present in table "service_status_type".
/*Failed as expected*/

DELETE FROM Service;

ALTER TABLE Service ALTER CONSTRAINT fk_service_service_status_type ENFORCED;
/*Succeeded*/

ALTER TABLE Service ALTER CONSTRAINT chk_service_price ENFORCED;
ERROR: cannot alter enforceability of constraint "chk_service_price" of relation "service"

Why does it fail?

ALTER TABLE Service DROP CONSTRAINT fk_service_service_status_type;
ALTER TABLE Service DROP CONSTRAINT chk_service_price;

ALTER TABLE Service ADD CONSTRAINT chk_service_price CHECK(price>0);
ALTER TABLE Service ADD CONSTRAINT fk_service_service_status_type FOREIGN KEY (service_status_type_code) REFERENCES Service_status_type (service_status_type_code);

ALTER TABLE Service ALTER CONSTRAINT fk_service_service_status_type NOT ENFORCED;
/*Succeeded*/

ALTER TABLE Service ALTER CONSTRAINT chk_service_price NOT ENFORCED;
ERROR:  cannot alter enforceability of constraint "chk_service_price" of relation "service"

Why does it fail?

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

    David Rowley <dgrowleyml@gmail.com> — 2025-09-30T11:36:21Z

    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

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

    Erki Eessaar <erki.eessaar@taltech.ee> — 2025-09-30T12:12:15Z

    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


________________________________
From: David Rowley <dgrowleyml@gmail.com>
Sent: Tuesday, September 30, 2025 14:36
To: Erki Eessaar <erki.eessaar@taltech.ee>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: 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 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

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

    David Rowley <dgrowleyml@gmail.com> — 2025-10-01T05:24:52Z

    On Wed, 1 Oct 2025 at 01:12, Erki Eessaar <erki.eessaar@taltech.ee> wrote:
> 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?

At least going by the commit messages, it seems to be intentional:

>From [1]:
Note that CHECK constraints do not currently support ALTER operations,
so changing the enforceability of an existing constraint isn't
possible without dropping and recreating it.  This could be added
later.

And for foreign keys in [2]:
Conversely, if a NOT ENFORCED
foreign key constraint is changed to ENFORCED, the necessary triggers
will be created, and the will be changed to VALID by performing
necessary validation.

> 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.

I think it would be good if the documents were to mention the
limitation with CHECK constraints. Otherwise, users are just going to
be left to discover this for themselves.

David

[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ca87c415e
[2] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=eec0040c4

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

    Erki Eessaar <erki.eessaar@taltech.ee> — 2025-10-01T09:34:01Z

    Hello

Thank you for finding those commit messages; that clarifies the current implementation perfectly.

I agree that adding this limitation to the documentation would be very helpful.

Looking ahead, I hope that support for altering  CHECK constraints can be added. Aligning their behavior with FOREIGN KEY  constraints would make the system more consistent and intuitive for users.

Best regards
Erki Eessaar

________________________________
From: David Rowley <dgrowleyml@gmail.com>
Sent: Wednesday, October 1, 2025 08:24
To: Erki Eessaar <erki.eessaar@taltech.ee>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: Potential bug: Enforcing/not enforcing a CHECK constraint fails on an empty table

On Wed, 1 Oct 2025 at 01:12, Erki Eessaar <erki.eessaar@taltech.ee> wrote:
> 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?

At least going by the commit messages, it seems to be intentional:

>From [1]:
Note that CHECK constraints do not currently support ALTER operations,
so changing the enforceability of an existing constraint isn't
possible without dropping and recreating it.  This could be added
later.

And for foreign keys in [2]:
Conversely, if a NOT ENFORCED
foreign key constraint is changed to ENFORCED, the necessary triggers
will be created, and the will be changed to VALID by performing
necessary validation.

> 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.

I think it would be good if the documents were to mention the
limitation with CHECK constraints. Otherwise, users are just going to
be left to discover this for themselves.

David

[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ca87c415e
[2] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=eec0040c4