Self referencing Foreign Key SQL Server DeleteAction does nto work

I have one Table with a self referencing foreign key with DeleteAction set to Cascade, but when Parent is deleted,no children (direct or descendant) does not delete.

What a开发者_JAVA百科m I missing ?

You cannot use cascade delete on self referencing tables.

Check this link for possible solution.

For use delete cascade use following Create table script...

CREATE TABLE

test_child

( SUB1 INT, [NO] int, FOREIGN KEY ([NO]) REFERENCES Test_parent ON DELETE CASCADE )

Check This link for more information:

http://msdn.microsoft.com/en-us/library/ms186973.aspx