如何通过约束/索引实现：相同R1_id与R2_id仅对应唯一OnlyOneTypeAllowed_id？

要满足你的需求——允许同一(R1_id, R2_id, OnlyOneTypeAllowed_id)组合存在多行，但禁止同一(R1_id, R2_id)关联不同的OnlyOneTypeAllowed_id——可以通过触发器实现（无需创建新表、视图或函数），以下是具体方案：

适用数据库：SQL Server #

创建一个INSTEAD OF INSERT, UPDATE触发器，在数据插入/更新前检查是否违反规则，若违反则抛出错误并回滚操作：

CREATE TRIGGER trg_EnforceSingleTypePerR1R2
ON YourTableName -- 替换为你的表名
INSTEAD OF INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 检查是否存在冲突行：同一R1_id+R2_id对应不同的OnlyOneTypeAllowed_id
    IF EXISTS (
        SELECT 1
        FROM inserted i
        JOIN YourTableName t 
            ON i.R1_id = t.R1_id 
            AND i.R2_id = t.R2_id
        WHERE i.OnlyOneTypeAllowed_id <> t.OnlyOneTypeAllowed_id
    )
    BEGIN
        RAISERROR('错误：同一R1_id和R2_id组合只能对应唯一的OnlyOneTypeAllowed_id', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END

    -- 执行合法的插入/更新操作
    INSERT INTO YourTableName (R1_id, R2_id, OnlyOneTypeAllowed_id)
    SELECT R1_id, R2_id, OnlyOneTypeAllowed_id
    FROM inserted;
END;

为什么其他方案不适用？ #

• 普通唯一索引：
  • 若创建(R1_id, R2_id, OnlyOneTypeAllowed_id)的唯一索引，会阻止同一组合的多行插入，不符合你的需求；
  • 若创建(R1_id, R2_id)的唯一索引，会直接禁止同一组合的多行存在，同样不符合需求。
• 筛选索引：仅能针对特定值设置规则，无法覆盖所有(R1_id, R2_id)组合的全局约束。
• 分组统计：仅能用于事后查询检查，无法在数据插入/更新时实时强制约束。

适用数据库：PostgreSQL #

若使用PostgreSQL，可直接通过EXCLUDE约束实现（无需触发器）：

ALTER TABLE YourTableName -- 替换为你的表名
ADD CONSTRAINT chk_single_type_per_r1r2
EXCLUDE USING gist (
    R1_id WITH =,
    R2_id WITH =,
    OnlyOneTypeAllowed_id WITH <>
);

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