Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
At one level it does not appear as though you have a primary key on [dbo].[myObj]. However, even if you did, the other, deeper level is that you introduce multiple cascade paths. This question discusses this phenomenon. –  swasheck Oct 17 '12 at 14:42
add comment

1 Answer

up vote 1 down vote accepted

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:

sample data

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.

share|improve this answer
1  
What if this is exactly the effect wanted? –  ypercube Sep 19 '13 at 19:48
1  
Deleting previous comments. I'll leave the Fiddle that works in Postgres, Oracle and MySQL, in case you want to use it. –  ypercube Sep 19 '13 at 20:19
    
Deleting all rows is irrelevant -- there's nothing stopping you from doing so with a simple one-to-many relationship between two tables. The problem is just as the error message states -- circular cascading relationships and multiple cascade paths to the same table are disallowed. This could be handled using a recursive CTE in a delete trigger. –  Jon Seigel Sep 20 '13 at 1:50
1  
Honestly I don't know exactly why those relationships are disallowed except, presumably, for self-protection and performance optimizations. They're both technically possible, just disallowed. Does that help at all? –  Jon Seigel Sep 20 '13 at 2:08
2  
I'm sure there were internal design meetings when they made this decision. Having written a few table dependency traversal algorithms myself, I can say it's definitely helpful to eliminate those possibilities to reduce the complexity of the code needed to get the job done. While the argument for self-referential deletes does have some weight in my eyes, I like the disallowing of multiple cascade paths, because it almost always points to a table design issue. –  Jon Seigel Sep 20 '13 at 13:24
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.