cascading deletes
-
Hi I run what I thought was the most basic example of a table that allows a cascading delete: CREATE TABLE TEST ( ID tinyint NOT NULL, PARENT_ID tinyint NULL, CONSTRAINT PK PRIMARY KEY (ID), CONSTRAINT FK FOREIGN KEY (PARENT_ID) REFERENCES TEST(ID) ON DELETE CASCADE ) go I get the error: Introducing FOREIGN KEY constraint 'FK' on table 'TEST' 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, Server PEER170, Line 5 What am I doing wrong???
cheers, Neil
-
Hi I run what I thought was the most basic example of a table that allows a cascading delete: CREATE TABLE TEST ( ID tinyint NOT NULL, PARENT_ID tinyint NULL, CONSTRAINT PK PRIMARY KEY (ID), CONSTRAINT FK FOREIGN KEY (PARENT_ID) REFERENCES TEST(ID) ON DELETE CASCADE ) go I get the error: Introducing FOREIGN KEY constraint 'FK' on table 'TEST' 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, Server PEER170, Line 5 What am I doing wrong???
cheers, Neil
Since the foreign key references a field in the same table, specifying cascade delete has the potential to create cycles. Not sure how you would ever insert the first record in this table, unless Id = Parent_id, and clearly that row would create an infinite loop on cascade delete , trying to recursively delete itself...
-
Since the foreign key references a field in the same table, specifying cascade delete has the potential to create cycles. Not sure how you would ever insert the first record in this table, unless Id = Parent_id, and clearly that row would create an infinite loop on cascade delete , trying to recursively delete itself...
Thanks for that. So it is basically a limitation of SQL Server. The first row (a "top level" row) would be inserted with parent_id = NULL, therefore there would be no infinite recursion problem. But no worries, I can workaround it.
cheers, Neil