Problem
I am trying to use the ON DELETE CASCADE option when creating a foreign key on my database, but I get the following error:
Msg 1785, Level 16, State 0, Line 3 Introducing FOREIGN KEY constraint 'FK_Table' on table 'Table' 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.
This tip will look at how you can use triggers to replace the functionality you get from the ON DELETE CASCADE option of a foreign key constraint.
Solution
Let's setup a simple scenario to illustrate the issue. The following database diagram illustrates our table and foreign key layout. You can see from the diagram where we are going to have the issue of multiple cascade paths as there are two paths from the Parent table down to the GrandChild table.
And here is the DDL code we can use to setup this scenario in our database.
-- Table creation logic --parent table CREATE TABLE [dbo].[Parent]( [ParentID] [bigint] NOT NULL, [Data] [varchar](10) NOT NULL, CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED ([ParentID] ASC) ) GO -- child table 1 CREATE TABLE [dbo].[Child1]( [Child1ID] [bigint] NOT NULL, [ParentID] [bigint] NULL, [Data] [varchar](10) NULL, CONSTRAINT [PK_Child1] PRIMARY KEY CLUSTERED ([Child1ID] ASC) ) GO -- child table 2 CREATE TABLE [dbo].[Child2]( [Child2ID] [bigint] NOT NULL, [ParentID] [bigint] NULL, [Data] [varchar](10) NULL, CONSTRAINT [PK_Child2] PRIMARY KEY CLUSTERED ([Child2ID] ASC) ) GO -- grandchild table CREATE TABLE [dbo].[GrandChild]( [GrandChildID] [bigint] NOT NULL, [Child1ID] [bigint] NULL, [Child2ID] [bigint] NULL, [Data] [varchar](10) NULL, CONSTRAINT [PK_GrandChild] PRIMARY KEY CLUSTERED ([GrandChildID] ASC) ) GO -- foreign key constraint ALTER TABLE [dbo].[Child1] WITH CHECK ADD CONSTRAINT [FK_Child1_Parent] FOREIGN KEY([ParentID]) REFERENCES [dbo].[Parent] ([ParentID]) ON DELETE CASCADE GO -- foreign key constraint ALTER TABLE [dbo].[Child2] WITH CHECK ADD CONSTRAINT [FK_Child2_Parent] FOREIGN KEY([ParentID]) REFERENCES [dbo].[Parent] ([ParentID]) ON DELETE CASCADE GO -- foreign key constraint ALTER TABLE [dbo].[GrandChild] WITH CHECK ADD CONSTRAINT [FK_GrandChild_Child1] FOREIGN KEY([Child1ID]) REFERENCES [dbo].[Child1] ([Child1ID]) ON DELETE CASCADE GO -- foreign key constraint ALTER TABLE [dbo].[GrandChild] WITH CHECK ADD CONSTRAINT [FK_GrandChild_Child2] FOREIGN KEY([Child2ID]) REFERENCES [dbo].[Child2] ([Child2ID]) ON DELETE CASCADE GO
After executing the code above we get the following error and confirm the initial assumption we made after looking at the database diagram, that we have multiple paths down to the GrandChild table.
Msg 1785, Level 16, State 0, Line 3 Introducing FOREIGN KEY constraint 'FK_GrandChild_Child2' on table 'GrandChild' 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.
To get around this error by creating the foreign key with DELETE NO ACTION, we would end up with orphan records in the GrandChild table every time a DELETE statement is issued against the Parent or Child2 tables. Instead of doing this we are going to create an INSTEAD OF trigger in place of the DELETE CASCADE option. One caveat when using an INSTEAD OF trigger is that you can't have a table with both a DELETE CASCADE foreign key constraint and an INSTEAD OF trigger. If we try to create a trigger on the Child2 table as things are setup now we'll get the following error.
Msg 2113, Level 16, State 1, Procedure DELETE_Child2, Line 8 Cannot create INSTEAD OF DELETE or INSTEAD OF UPDATE TRIGGER 'DELETE_Child2' on table 'dbo.Child2'. This is because the table has a FOREIGN KEY with cascading DELETE or UPDATE.
To get around this restriction we have to remove the DELETE CASCADE option from the foreign key on the Child2 table before we can add the DELETE CASCADE option to the GrandChild tables foreign key and then add an INSTEAD OF trigger to the Parent table. I like to keep things consistent so having DELETE CASCADE on some of the tables foreign keys and using INSTEAD OF triggers on other tables becomes quite confusing and difficult to manage. Although this approach would work, I think a better solution is to remove the DELETE CASCADE option from all the foreign keys and simply use INSTEAD OF triggers on all of the tables to handle removing the child records. We can use the following code to remove the DELETE CASCADE option from all of the foreign key constraints.
-- drop constraints with DELETE CASCADE option ALTER TABLE [dbo].[Child1] DROP CONSTRAINT [FK_Child1_Parent] ALTER TABLE [dbo].[Child2] DROP CONSTRAINT [FK_Child2_Parent] ALTER TABLE [dbo].[GrandChild] DROP CONSTRAINT [FK_GrandChild_Child1] GO -- recreate all foreign keys without DELETE CASCADE option ALTER TABLE [dbo].[Child1] WITH CHECK ADD CONSTRAINT [FK_Child1_Parent] FOREIGN KEY([ParentID]) REFERENCES [dbo].[Parent] ([ParentID]) GO ALTER TABLE [dbo].[Child2] WITH CHECK ADD CONSTRAINT [FK_Child2_Parent] FOREIGN KEY([ParentID]) REFERENCES [dbo].[Parent] ([ParentID]) GO ALTER TABLE [dbo].[GrandChild] WITH CHECK ADD CONSTRAINT [FK_GrandChild_Child1] FOREIGN KEY([Child1ID]) REFERENCES [dbo].[Child1] ([Child1ID]) GO ALTER TABLE [dbo].[GrandChild] WITH CHECK ADD CONSTRAINT [FK_GrandChild_Child2] FOREIGN KEY([Child2ID]) REFERENCES [dbo].[Child2] ([Child2ID]) GO
Now that we no longer have any foreign keys with the DELETE CASCADE option set we can add INSTEAD OF triggers to each table to handle the deletion of the child records. Please take note of the fact that because we are using an INSTEAD OF trigger we also have to remove the records from the parent table itself after the records have been removed from the child tables. Here is the trigger code.
CREATE TRIGGER [DELETE_Parent] ON dbo.[Parent] INSTEAD OF DELETE AS BEGIN SET NOCOUNT ON; DELETE FROM [Child1] WHERE ParentID IN (SELECT ParentID FROM DELETED) DELETE FROM [Child2] WHERE ParentID IN (SELECT ParentID FROM DELETED) DELETE FROM [Parent] WHERE ParentID IN (SELECT ParentID FROM DELETED) END GO CREATE TRIGGER [DELETE_Child1] ON dbo.[Child1] INSTEAD OF DELETE AS BEGIN SET NOCOUNT ON; DELETE FROM [GrandChild] WHERE Child1ID IN (SELECT Child1ID FROM DELETED) DELETE FROM [Child1] WHERE Child1ID IN (SELECT Child1ID FROM DELETED) END GO CREATE TRIGGER [DELETE_Child2] ON dbo.[Child2] INSTEAD OF DELETE AS BEGIN SET NOCOUNT ON; DELETE FROM [GrandChild] WHERE Child2ID IN (SELECT Child2ID FROM DELETED) DELETE FROM [Child2] WHERE Child2ID IN (SELECT Child2ID FROM DELETED) END GO
To test that everything is working correctly we can run the following script and verify that all the child records are being removed as expected.
INSERT INTO Parent VALUES (1,'test') INSERT INTO Parent VALUES (2,'test') INSERT INTO Parent VALUES (3,'test') INSERT INTO Parent VALUES (4,'test') INSERT INTO Child1 VALUES (1,1,'test') INSERT INTO Child2 VALUES (10,2,'test') INSERT INTO Child1 VALUES (2,3,'test') INSERT INTO Child2 VALUES (11,4,'test') INSERT INTO GrandChild VALUES (1,1,null,'test') INSERT INTO GrandChild VALUES (2,null,10,'test') INSERT INTO GrandChild VALUES (3,2,null,'test') INSERT INTO GrandChild VALUES (4,null,11,'test') DELETE FROM Parent WHERE ParentID=1 DELETE FROM Parent WHERE ParentID=2
