PostgreSQL约束需求：同一ValueA的记录需有且仅有一个ValueC为true

现有表结构如下：

Table A (
   ValueA string,
   ValueB int,
   ValueC boolean,
   Unique(valueA, valueB)
)

当前已实现约束：同一ValueA的所有记录中，ValueC为true的记录最多只能有一条。但还需补充核心约束：同一ValueA的记录集合中必须存在且仅存在一条ValueC为true的记录——即任何操作后，若某ValueA对应的记录里没有ValueC=true的条目，操作必须失败。

要求满足以下测试场景：

• 场景1：首次插入('abc', 1, true)，操作成功；
• 场景2：首次插入('abc', 1, false)，操作需失败（当前无法实现）；
• 场景3：先插入('abc', 1, true)，再插入('abc', 2, true)，操作失败（当前已实现）。

要实现完整约束，需结合部分唯一索引和触发器覆盖所有数据变更场景（插入、更新、删除）：

1. 强化"最多一条true记录"的约束 #

保留现有Unique(valueA, valueB)约束，同时创建部分唯一索引严格限制同一ValueA下ValueC=true的记录数不超过1：

CREATE UNIQUE INDEX idx_a_valuea_true ON A (ValueA) WHERE ValueC = true;

该索引直接满足场景3的需求，重复插入同一ValueA的true记录会被直接阻止。

2. 触发器确保"至少一条true记录" #

通过触发器函数，在每次数据变更后检查对应ValueA的true记录数是否为1，不符合则抛出异常。以下以PostgreSQL为例实现：

触发器函数 #

CREATE OR REPLACE FUNCTION enforce_valuec_true_rule()
RETURNS TRIGGER AS $$
BEGIN
    -- 检查当前操作涉及的ValueA对应的true记录数是否不等于1
    PERFORM 1 FROM A
    WHERE ValueA = COALESCE(NEW.ValueA, OLD.ValueA)
    GROUP BY ValueA
    HAVING COUNT(CASE WHEN ValueC = true THEN 1 END) != 1;

    IF FOUND THEN
        RAISE EXCEPTION '规则违反：每个ValueA必须且只能包含一条ValueC为true的记录';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

绑定触发器到表 #

-- 插入操作触发
CREATE TRIGGER a_after_insert_check
AFTER INSERT ON A
FOR EACH ROW
EXECUTE FUNCTION enforce_valuec_true_rule();

-- 更新操作触发（仅当ValueA或ValueC变更时）
CREATE TRIGGER a_after_update_check
AFTER UPDATE ON A
FOR EACH ROW
WHEN (OLD.ValueA != NEW.ValueA OR OLD.ValueC != NEW.ValueC)
EXECUTE FUNCTION enforce_valuec_true_rule();

-- 删除操作触发（防止删除唯一的true记录）
CREATE TRIGGER a_after_delete_check
AFTER DELETE ON A
FOR EACH ROW
EXECUTE FUNCTION enforce_valuec_true_rule();

• 场景1：首次插入('abc', 1, true)，abc对应的true记录数为1，符合约束，操作成功；
• 场景2：首次插入('abc', 1, false)，插入后abc对应的true记录数为0，触发器抛出异常，操作失败；
• 场景3：先插入('abc', 1, true)，再插入('abc', 2, true)，部分唯一索引直接阻止该插入，操作失败。

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