Cannot change the Delete Rule of a self referencing foreign-key-relationship to cascade delete I have the following table

[dbo].[myObj]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [fk_ParentID] [int] NULL
)


and I have a relationship from fk_ParentID to ID.

I want to change the Delete Rule in SQL Server Management Studio under the Window "Foreign-Key Relationships"->Insert and Update Specifications->Delete Rule to ON Delete Cascade, but it is not possible.

Can you tell me why, and how I can make this possible?
To further clarify this question, I've created a sample schema as such:

USE TempDB;

CREATE TABLE [dbo].[myObj]
(
    [ID] [int] NOT NULL CONSTRAINT PK_myObj PRIMARY KEY IDENTITY(1,1),
    [fk_ParentID] [int] NULL CONSTRAINT FK_myObj_ID FOREIGN KEY 
                    REFERENCES myObj (ID) ON DELETE CASCADE
);


When you attempt to execute this SQL, the following error is returned by SQL Server:


  Msg 1785, Level 16, State 0, Line 3
  Introducing FOREIGN KEY constraint 'FK_myObj_ID' on table 'myObj' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
  
  Msg 1750, Level 16, State 0, Line 3
  Could not create constraint. See previous errors.


The error message indicates that SQL Server is declining to create the foreign key cascade relationship since deleting a single row could result in all the rows being deleted (or perhaps just multiple rows, not all, depending on data in the table!).

To illustrate this, consider the following data:

INSERT INTO myObj (fk_ParentID) VALUES (NULL);
INSERT INTO myObj (fk_ParentID) VALUES (1);
INSERT INTO myObj (fk_ParentID) VALUES (2);
INSERT INTO myObj (fk_ParentID) VALUES (3);
INSERT INTO myObj (fk_ParentID) VALUES (4);
INSERT INTO myObj (fk_ParentID) VALUES (5);
INSERT INTO myObj (fk_ParentID) VALUES (6);
INSERT INTO myObj (fk_ParentID) VALUES (7);


Which looks like:



If I could define the ON DELETE CASCADE action on this table, executing the following code would delete all rows in the table:

DELETE FROM myObj WHERE ID = 1;


Deleting the row with ID=1 would cascade through the row with fk_ParentID=1 which would cascade delete through fk_ParentID 2, etc, etc.  Most likely this is not the effect you want.