SQL Server同表两列引用同一表同列无法设置双更新级联如何解决

报错原因 #

SQL Server 为了避免级联操作出现执行顺序冲突、无限循环等异常，禁止同一张表的多个外键指向同一个父表主键时同时开启级联更新/删除。你当前场景下PROGRAM_MASTER的created_by、updated_by两个字段都指向USERS表的id主键，触发更新时会形成两条级联路径，因此被数据库引擎主动拦截报错。

可行解决方案 #

方案1：用触发器实现第二个字段的级联更新（生产环境最常用） #

该方案既可以保留外键的约束能力，也能实现和原生级联更新完全一致的效果：

1. 先创建updated_by的外键约束，关闭级联更新配置：

-- 可选：清理创建失败残留的约束
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE name = 'program_master_updated_by_foreign')
ALTER TABLE program_master DROP CONSTRAINT program_master_updated_by_foreign
GO

-- 创建关闭级联更新的外键
ALTER TABLE program_master 
ADD CONSTRAINT program_master_updated_by_foreign 
FOREIGN KEY (updated_by) REFERENCES users (id) 
ON UPDATE NO ACTION
-- 这里可以根据业务需求自定义ON DELETE的动作，比如NO ACTION或者SET NULL等
GO

2. 创建USERS表的更新触发器，手动同步updated_by字段的变更：

CREATE TRIGGER trg_users_id_update_cascade_updated_by
ON users
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    -- 仅当id字段被更新时才执行同步逻辑
    IF UPDATE(id)
    BEGIN
        UPDATE pm
        SET pm.updated_by = inserted.id
        FROM program_master pm
        INNER JOIN deleted ON pm.updated_by = deleted.id
        CROSS JOIN inserted
    END
END
GO

如果后续逻辑调整需要统一管理级联规则，也可以把created_by的级联更新也改成触发器实现，保持逻辑一致性。

方案2：业务层控制更新逻辑 #

如果你的业务场景中几乎不会修改USERS表的id字段，可以直接将两个外键的级联更新都设置为NO ACTION。需要修改用户id时，业务代码先手动更新PROGRAM_MASTER中所有关联的created_by和updated_by字段，再更新USERS表的id即可，不需要额外创建触发器。

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