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?
