SQL Server单表两列关联同一外键时ON DELETE SET NULL报错解决

现有两张表，users为用户表，notizen_aktionen表的creation_user_id和last_user_id字段均关联users.user_id作为外键，期望删除用户时这两个字段自动设为NULL。该逻辑在MySQL中可正常运行，但在SQL Server 2019中创建表时触发错误：

Introducing FOREIGN KEY constraint '%.*ls' on table '%.*ls' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

表结构SQL如下：

CREATE TABLE users 
(
    user_id integer IDENTITY(1,1) NOT NULL,
    username varchar(50) NOT NULL,
    PRIMARY KEY(user_id)
);

CREATE TABLE notizen_aktionen 
(
    notiz_aktion_id INTEGER IDENTITY(1,1),
    notiz_id INTEGER NOT NULL,
    aktion_id INTEGER NOT NULL,
    creation_user_id INTEGER DEFAULT NULL,
    description TEXT NOT NULL DEFAULT '',
    status_id INTEGER NOT NULL,
    last_user_id INTEGER DEFAULT NULL,
    PRIMARY KEY(notiz_aktion_id),
    FOREIGN KEY (creation_user_id) 
         REFERENCES users(user_id) ON DELETE SET NULL,
    FOREIGN KEY (last_user_id) 
         REFERENCES users(user_id) ON DELETE SET NULL,
);

SQL Server不允许同一子表上存在多条指向同一父表的级联操作路径，这类场景会被判定为潜在的循环或多路径冲突，因此无法直接通过外键的ON DELETE SET NULL实现需求。可以通过触发器替代外键的级联逻辑：

1. 修改外键定义，移除级联操作 #

先创建不带级联删除规则的外键：

CREATE TABLE users 
(
    user_id integer IDENTITY(1,1) NOT NULL,
    username varchar(50) NOT NULL,
    PRIMARY KEY(user_id)
);

CREATE TABLE notizen_aktionen 
(
    notiz_aktion_id INTEGER IDENTITY(1,1),
    notiz_id INTEGER NOT NULL,
    aktion_id INTEGER NOT NULL,
    creation_user_id INTEGER DEFAULT NULL,
    description TEXT NOT NULL DEFAULT '',
    status_id INTEGER NOT NULL,
    last_user_id INTEGER DEFAULT NULL,
    PRIMARY KEY(notiz_aktion_id),
    FOREIGN KEY (creation_user_id) 
         REFERENCES users(user_id) ON DELETE NO ACTION,
    FOREIGN KEY (last_user_id) 
         REFERENCES users(user_id) ON DELETE NO ACTION,
);

2. 创建AFTER DELETE触发器 #

在users表上创建触发器，当用户被删除时，自动将notizen_aktionen中关联的两个字段设为NULL：

CREATE TRIGGER trg_users_delete_update_notizen_aktionen
ON users
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- 批量更新关联字段为NULL
    UPDATE notizen_aktionen
    SET 
        creation_user_id = CASE WHEN creation_user_id IN (SELECT user_id FROM DELETED) THEN NULL ELSE creation_user_id END,
        last_user_id = CASE WHEN last_user_id IN (SELECT user_id FROM DELETED) THEN NULL ELSE last_user_id END
    WHERE 
        creation_user_id IN (SELECT user_id FROM DELETED)
        OR last_user_id IN (SELECT user_id FROM DELETED);
END;

逻辑说明 #

触发器会在users表执行DELETE操作后触发，批量处理notizen_aktionen中所有关联被删除用户的记录，将对应的creation_user_id和last_user_id设为NULL，完全替代原本期望的外键级联逻辑。

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