ON DELETE SET NULL on self referencing relationship

Question

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.

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?

Answer 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.

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.

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;