PostgreSQL含子查询的检查约束替代实现方案求助

表结构 #

CREATE TABLE "Scheme"."TableName" (
    "ID"               serial4 NOT NULL,
    "ID-XyzPlist_fkey" int4    NOT NULL,
    "ID-Xyz_fkey"      int4    NOT NULL,
    CONSTRAINT "TableName_pkey" PRIMARY KEY ("ID"),
    CONSTRAINT "TableName_ID-XyzPlist_fkey_fkey" FOREIGN KEY ("ID-XyzPlist_fkey") REFERENCES ...,
    CONSTRAINT "TableName_ID-Xyz_fkey" FOREIGN KEY ("ID-Xyz_fkey") REFERENCES ...
);

业务规则 #

• 样本ID-Xyz_fkey可关联多个预定义属性ID-XyzPlist_fkey
• 当属性为ID-XyzPlist_fkey=35（标记为Garbage）时：
  • 该样本ID-Xyz_fkey不能再添加任何非35的属性记录
  • 添加(ID-Xyz_fkey, ID-XyzPlist_fkey)=(xxx,35)记录时，该样本必须无任何已有记录

问题说明 #

直接在CHECK约束中使用子查询的方案无法生效，因为PostgreSQL不支持CHECK约束包含跨表/跨行的子查询：

ALTER TABLE "Scheme"."TableName"
ADD CONSTRAINT "TableName_ListNULL_check"
CHECK (( "ID-XyzPlist_fkey" = 35) AND ("ID-Xyz_fkey" IN (SELECT "ID-Xyz_fkey" FROM "Scheme"."TableName" )));

解决方案：使用触发器+函数实现跨行约束 #

PostgreSQL中实现跨行的业务规则，需要通过触发器函数配合触发器来完成，具体步骤如下：

1. 创建触发器函数 #

该函数会在插入/更新记录前检查是否违反业务规则：

CREATE OR REPLACE FUNCTION "Scheme".check_garbage_constraint()
RETURNS TRIGGER AS $$
BEGIN
    -- 若当前插入/更新的是Garbage属性（35），检查样本是否已有非Garbage属性
    IF NEW."ID-XyzPlist_fkey" = 35 THEN
        IF EXISTS (
            SELECT 1 FROM "Scheme"."TableName"
            WHERE "ID-Xyz_fkey" = NEW."ID-Xyz_fkey"
            AND "ID-XyzPlist_fkey" != 35
        ) THEN
            RAISE EXCEPTION '样本 % 已存在非Garbage属性，无法添加Garbage属性', NEW."ID-Xyz_fkey";
        END IF;
    ELSE
        -- 若当前插入/更新的是非Garbage属性，检查样本是否已有Garbage属性
        IF EXISTS (
            SELECT 1 FROM "Scheme"."TableName"
            WHERE "ID-Xyz_fkey" = NEW."ID-Xyz_fkey"
            AND "ID-XyzPlist_fkey" = 35
        ) THEN
            RAISE EXCEPTION '样本 % 已存在Garbage属性，无法添加其他属性', NEW."ID-Xyz_fkey";
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 创建触发器 #

为插入和更新操作分别绑定触发器，确保每次操作都触发规则检查：

-- 插入记录前触发检查
CREATE TRIGGER "TableName_check_garbage_insert"
BEFORE INSERT ON "Scheme"."TableName"
FOR EACH ROW
EXECUTE FUNCTION "Scheme".check_garbage_constraint();

-- 更新记录前触发检查
CREATE TRIGGER "TableName_check_garbage_update"
BEFORE UPDATE ON "Scheme"."TableName"
FOR EACH ROW
EXECUTE FUNCTION "Scheme".check_garbage_constraint();

补充说明 #

• 触发器函数会在每条记录插入/更新前执行，确保业务规则被严格遵守
• 批量插入/更新操作也能生效（行级触发器逐行检查）
• 后续业务规则调整时，仅需修改触发器函数即可

内容的提问来源于stack exchange，提问作者StOMicha