如何在第三列范围内强制两列值的跨列唯一性？

方法1：辅助表+触发器（直观可靠） #

通过维护辅助表记录每个column_scope下的非NULL值，利用唯一约束强制值的唯一性，再通过触发器同步主表与辅助表的数据。

步骤1：创建辅助表 #

CREATE TABLE scope_unique_values (
    column_scope text NOT NULL,
    value int NOT NULL,
    PRIMARY KEY (column_scope, value) -- 确保同一scope下值唯一
);

步骤2：编写触发器函数 #

负责主表数据变更时同步辅助表：

CREATE OR REPLACE FUNCTION sync_scope_values()
RETURNS TRIGGER AS $$
BEGIN
    -- 清理旧数据：移除辅助表中对应行的非NULL值
    IF TG_OP IN ('DELETE', 'UPDATE') THEN
        IF OLD.col1 IS NOT NULL THEN
            DELETE FROM scope_unique_values
            WHERE column_scope = OLD.column_scope AND value = OLD.col1;
        END IF;
        IF OLD.col2 IS NOT NULL THEN
            DELETE FROM scope_unique_values
            WHERE column_scope = OLD.column_scope AND value = OLD.col2;
        END IF;
    END IF;

    -- 写入新数据：将新行的非NULL值插入辅助表（唯一约束自动检查冲突）
    IF TG_OP IN ('INSERT', 'UPDATE') THEN
        IF NEW.col1 IS NOT NULL THEN
            INSERT INTO scope_unique_values (column_scope, value)
            VALUES (NEW.column_scope, NEW.col1);
        END IF;
        IF NEW.col2 IS NOT NULL THEN
            INSERT INTO scope_unique_values (column_scope, value)
            VALUES (NEW.column_scope, NEW.col2);
        END IF;
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

步骤3：绑定触发器到主表 #

CREATE TRIGGER trigger_sync_scope_values
AFTER INSERT OR UPDATE OR DELETE ON tbl
FOR EACH ROW EXECUTE FUNCTION sync_scope_values();

插入(scope_1, x, NULL)后再插(scope_1, NULL, x)，或插入(scope_1, x, y)后再插(scope_1, y, z)时，都会触发辅助表的唯一约束冲突，直接报错拦截。

方法2：EXCLUDE约束（无辅助表，简洁实现） #

利用PostgreSQL的EXCLUDE约束，结合数组与自定义操作符，直接在主表上实现跨列唯一性检查。

步骤1：创建数组交集判断操作符 #

CREATE OR REPLACE FUNCTION array_has_intersection(a int[], b int[])
RETURNS boolean AS $$
SELECT EXISTS (SELECT 1 FROM unnest(a) elem WHERE elem = ANY(b));
$$ LANGUAGE sql IMMUTABLE;

CREATE OPERATOR && (
    LEFTARG = int[],
    RIGHTARG = int[],
    PROCEDURE = array_has_intersection,
    COMMUTATOR = &&
);

步骤2：添加生成列与EXCLUDE约束 #

ALTER TABLE tbl ADD COLUMN non_null_values int[] GENERATED ALWAYS AS (
    ARRAY_REMOVE(ARRAY[col1, col2], NULL)
) STORED;

ALTER TABLE tbl ADD CONSTRAINT exclude_duplicate_values_in_scope
EXCLUDE USING gist (
    column_scope WITH =,
    non_null_values WITH &&
) WHERE (non_null_values <> '{}'::int[]);

该约束会检查：同一column_scope下，任意两行的非NULL值数组若存在交集（即有重复值），则触发冲突拦截。

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