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?

Selfabsorption

in your case i think that trigger on delete do what you want. look this CREATE TRIGGER – Bjork

I'd like to avoid creating trigger but that might be the only solution at the end of the day. I wanted to know if there is some DDL workaround to force sql server allow "ONDELETE SET NULL". And if it is not possible, why then? Because I can't see the reasons for disabling "ONDELETE SET NULL" be the same as for disabling "ONDELETE CASCADE". There are no cycles nor multiple cascade paths. I just want to be sure there is no logical reason before I post it to MS wishlist. – Selfabsorption

@matti i found this on delete set null – Bjork

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;

Unfailing

Can you cite a reference? – Bipetalous