sql-server - 两个外键引用一个表-ON UPDATE SET NULL不起作用

Question

我在 table 上有两个外键。假设表名为News，并且具有外键updatedById和createdById，它们都指向表userId中的Users。

现在，我想在删除用户时设置为NULL外键，但是当我尝试在该关系中设置ON DELETE SET NULL时，我得到:

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

我不明白为什么两个外键都不能设置为null？

Answer 1

Multiple Cascading Actions

The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree that contains no circular references. No table can appear more than one time in the list of all cascading referential actions that result from the DELETE or UPDATE. Also, the tree of cascading referential actions must not have more than one path to any specified table. Any branch of the tree is ended when it encounters a table for which NO ACTION has been specified or is the default.

在这种情况下，您可能需要考虑实现从逻辑上而不是物理上删除用户的功能(例如，通过在 Users表中引入“事件”或“已删除”标志字段)。这样，所有关系都会保持不变，并且可以进行追溯分析。

但是，如果仍然需要为两个FK都实现 ON DELETE SET NULL，则可以在 FOR DELETE表上使用 User触发器，如下所示:

CREATE TRIGGER Users_News_Delete_Trigger 
ON Users FOR DELETE
AS BEGIN
    UPDATE News SET createdById = NULL 
     WHERE createdById = DELETED.id;
    UPDATE News SET updatedById = NULL 
     WHERE updatedById = DELETED.id;
END