ON DELETE在自引用关系上设置NULL

I have the table with one primary key and one foreign key referencing the same table primary key. i.e there are parents and childs in the same table. In sql sever there are three options for the delete rule. But it is only possible to set "NO ACTION" delete rule. I understand that it is not possible to set the "cascade" delete because of cycles and chaining. But why the other options are not allowed? Especially the "SET NULL" one.

我有一个主键和一个外键引用同一个表主键的表。即在同一张桌子上有父母和孩子。在sql sever中,删除规则有三个选项。但是只能设置“NO ACTION”删除规则。据我所知,由于循环和链接,无法设置“级联”删除。但为什么不允许其他选择呢?特别是“SET NULL”之一。

Right now I have to do this manually. I have to find the child records and set the foreign key on null. After that I can delete the parent. Why is it not possible to set the rule for it?

现在我必须手动完成此操作。我必须找到子记录并将外键设置为null。之后我可以删除父母。为什么不能为它设置规则?

1 个解决方案

#1

Because it cannot perform two actions on the same table together which are:

因为它不能在同一个表上一起执行两个动作,它们是:

-delete the parent.

- 删除父级。

-update the children.

- 孩子们。

A mutating table is a table that is being modified by an UPDATE, DELETE, or INSERT statement, or a table that might be updated by the effects of a DELETE CASCADE constraint.

变异表是由UPDATE,DELETE或INSERT语句修改的表,或者是可能由DELETE CASCADE约束的效果更新的表。

you can overcome doing it manually by creating a procedure that would hold the parent key to delete the record and set the children to NULL.

您可以通过创建一个过程来克服这一过程,该过程将保存父键以删除记录并将子项设置为NULL。

procedure(parent_id) --takes the id as a parameter

   update table set null where foreign_key = parent_id;

   delete from table where id = parent_id;

end;

#1