ON DELETE SET NULL for self reference

Question

I have a table with one primary key and one foreign key referencing the same table primary key . that is, parents and children in the same table. In sql sever, there are three options for a delete rule. But you can set the deletion rule "NO ACTION" . I understand that setting "cascade" delete not possible due to loops and chaining. But why are other options not allowed? Especially the "SET NULL" one.

Now I have to do it manually. I have to find the child entries and set the foreign key to null. After that I can remove the parent. Why is it impossible to establish this rule?

Answer 1

Since it cannot perform two actions in the same table together:

- remove the parent.

- update children.

A mutation table is a table that is modified using an UPDATE, DELETE, or INSERT statement, or a table that can be updated with the effects of the DELETE CASCADE constraint.

you can overcome this manually by creating a procedure that holds the parent key to delete the entry and set the children to 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;