PostgreSQL中跨两表列创建Unique唯一约束的实现方案咨询

PostgreSQL本身不支持直接定义跨多张表的唯一约束，你可以根据业务场景选择以下两种方案实现需求：

方案1：物化视图+唯一约束（适合低写频率场景） #

无需修改原有表结构，实现逻辑简单，适合数据变更频率低的场景。

• 操作逻辑：创建关联两张表的物化视图，仅存储用于唯一性校验的MODEL.KEY和OWNERS.USER字段，给物化视图添加组合唯一约束，每次表数据变更后自动刷新物化视图，存在重复组合时会直接抛出错误阻断非法操作。
• 代码示例：

-- 创建关联两张表的物化视图
CREATE MATERIALIZED VIEW model_user_unique_check AS
SELECT m.KEY AS model_key, o.USER AS owner_user
FROM MODEL m
JOIN OWNERS o ON m.ID = o.MODEL_ID;

-- 给物化视图添加组合唯一约束
CREATE UNIQUE INDEX idx_unique_model_key_user ON model_user_unique_check (model_key, owner_user);

• 注意：需要给MODEL和OWNERS表绑定触发器，在INSERT/UPDATE/DELETE操作后自动刷新物化视图，无需手动操作；如果表数据量极大，刷新物化视图开销较高，高并发写场景不推荐该方案。

方案2：触发器函数实时校验（适合高并发写场景） #

行级校验性能更高，没有全表扫描开销，适合数据变更频率高的生产场景。

• 操作逻辑：自定义校验函数，在OWNERS表新增/修改记录、MODEL表修改KEY字段时触发校验，存在重复组合直接抛出异常回滚事务。
• 代码示例：

-- 1. 定义OWNERS表变更校验函数
CREATE OR REPLACE FUNCTION check_unique_model_key_user()
RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM MODEL m
        JOIN OWNERS o ON m.ID = o.MODEL_ID
        WHERE m.KEY = (SELECT KEY FROM MODEL WHERE ID = NEW.MODEL_ID)
          AND o.USER = NEW.USER
          AND o.ID != NEW.ID
    ) THEN
        RAISE EXCEPTION 'MODEL.KEY: % 和 OWNERS.USER: % 的组合已存在，违反唯一性约束', 
            (SELECT KEY FROM MODEL WHERE ID = NEW.MODEL_ID), NEW.USER;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 给OWNERS表绑定触发器，新增/修改关联字段时触发校验
CREATE TRIGGER trg_owners_check_unique
BEFORE INSERT OR UPDATE OF MODEL_ID, USER ON OWNERS
FOR EACH ROW EXECUTE FUNCTION check_unique_model_key_user();

-- 2. 定义MODEL表KEY字段变更校验函数，避免改KEY产生重复组合
CREATE OR REPLACE FUNCTION check_model_key_update()
RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM OWNERS o
        JOIN MODEL m ON o.MODEL_ID = m.ID
        WHERE m.KEY = NEW.KEY
          AND o.USER IN (SELECT USER FROM OWNERS WHERE MODEL_ID = OLD.ID)
    ) THEN
        RAISE EXCEPTION '修改MODEL.KEY为 % 会导致和已有OWNERS.USER的组合重复，违反唯一性约束', NEW.KEY;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 给MODEL表绑定触发器，修改KEY字段时触发校验
CREATE TRIGGER trg_model_key_check_unique
BEFORE UPDATE OF KEY ON MODEL
FOR EACH ROW EXECUTE FUNCTION check_model_key_update();

• 注意：所有校验逻辑会纳入当前事务，校验不通过自动回滚，不会产生脏数据。

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