Solving the SQL Server Multiple Cascade Path Issue with a Trigger

By:   |   Updated: 2012-08-07   |   Comments (24)   |   Related: > Database Design


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.

Database Diagram

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

Test Query Output
Next Steps
  • Read my previous tip on using the cascade delete option for foreign keys
  • Read here for other uses of INSTEAD OF triggers
  • Read here for more information regarding triggers


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ben Snaidero Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2012-08-07

Comments For This Article




Friday, July 26, 2024 - 6:27:17 AM - ADMIN Back To Top (92412)
I also have the same problem with multiple cascade deletes. Can I achieve this with an AFTER DELETE trigger? However, I am already using the AFTER DELETE trigger for another purpose. Can anyone help me with this issue?

Sunday, January 7, 2024 - 6:21:31 PM - Tim Back To Top (91838)
I cant belive this is still an issue over 10 years later, i just had an issue with
Table1 T1ID
Table2 T2ID
Table3 T1ID T2TID

Can't do cascade delete for both table 1 and 2 to 3, its not rocket science, there is no loop, i just want table 3 to be cleaned up if table1 record is deleted or if table 2 record is deleted, but have to resort to this workaround. MS really ... annoy me with their dumb SQL decisions.

Thursday, December 16, 2021 - 12:52:53 PM - IssacEinstein Back To Top (89598)
The above code doesn't fit the below schema.

-- 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

-- GrandGrandChild
CREATE TABLE dbo.GrandGrandChild
(
GrandGrandChildID BIGINT NOT NULL,
GrandChildID BIGINT NULL,
Child1ID BIGINT NULL,
Child2ID BIGINT NULL,
Data VARCHAR(10) NULL,
CONSTRAINT PK_GrandGrandChild
PRIMARY KEY CLUSTERED (GrandGrandChildID ASC)
);
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

--
ALTER TABLE dbo.GrandGrandChild WITH CHECK
ADD CONSTRAINT FK_GrandGrandChild_GrandChild
FOREIGN KEY (GrandChildID)
REFERENCES dbo.GrandChild (GrandChildID);
GO

ALTER TABLE dbo.GrandGrandChild WITH CHECK
ADD CONSTRAINT FK_GrandGrandChild_Child1
FOREIGN KEY (Child1ID)
REFERENCES dbo.Child1 (Child1ID);
GO

ALTER TABLE dbo.GrandGrandChild WITH CHECK
ADD CONSTRAINT FK_GrandGrandChild_Child2
FOREIGN KEY (Child2ID)
REFERENCES dbo.Child2 (Child2ID);
GO

Thursday, December 5, 2019 - 2:49:16 AM - Kasper Cottaar Back To Top (83291)

I don't know if it has to do with a update in MS SQL, but it seems that you do not require 3 triggers to fix this issue on the latest versions. You van keep the DELETE CASCADE constraints on the GrandChild table. You'll only need the Trigger on the Parent table.

Monday, March 12, 2018 - 1:55:24 PM - Ashish Back To Top (75390)

Useful article with easy to understand example. Thanks for posting it.

Tuesday, December 1, 2015 - 1:47:22 PM - Dean T Back To Top (40171)

Just want to clarify for others that this is not true - "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."

If you create this foreign key with DELETE NO ACTION, every time a DELETE statement is issued agains the Parent or Child2 tables you will receive an error message "The DELETE statement conflicted with the REFERENCE constraint...". NO ACTION means the DELETE statement will not result in any action i.e., the delete will not take place.

Thursday, April 25, 2013 - 12:28:12 PM - Paul Richmond Back To Top (23558)

Ben,

Thank you so much for a great idea.  I've run into this problem numerous times over the last few years while trying to add relational integrity to a legacy DB.  Cascade has been a wonderful tool in dealing with this until I ran into the multiple inheratency issue you describe.  I'm looking forward to implementing your solution.

Thanks again for your efforts on behalf of our community.

Paul

 

 

 

Friday, January 25, 2013 - 7:43:34 PM - dave Back To Top (21719)

Oh! I finally tried to deal with the recursivity myself. The idea was to deleted each folder independently (and in the right order) and let the trigger just deleting automatically the leaves. So I removed the complicated part at the end:

CREATE TRIGGER trig_del_folder            ON table_folder FOR DELETE AS
   DELETE FROM table_project          WHERE EXISTS (SELECT * FROM DELETED WHERE table_project.folder_id          = DELETED.folder_id)
   DELETE FROM table_sut              WHERE EXISTS (SELECT * FROM DELETED WHERE table_sut.folder_id              = DELETED.folder_id)
   DELETE FROM table_spec             WHERE EXISTS (SELECT * FROM DELETED WHERE table_spec.folder_id             = DELETED.folder_id)
   DELETE FROM table_test             WHERE EXISTS (SELECT * FROM DELETED WHERE table_test.folder_id             = DELETED.folder_id)
   DELETE FROM table_camp             WHERE EXISTS (SELECT * FROM DELETED WHERE table_camp.campaign_id           = DELETED.folder_id)
   DELETE FROM table_agent            WHERE EXISTS (SELECT * FROM DELETED WHERE table_agent.folder_id            = DELETED.folder_id)
   DELETE FROM table_usergroup_folder WHERE EXISTS (SELECT * FROM DELETED WHERE table_usergroup_folder.folder_id = DELETED.folder_id)
   DELETE FROM table_usergroup        WHERE EXISTS (SELECT * FROM DELETED WHERE table_usergroup.folder_id        = DELETED.folder_id)

... and STILL THE SAME PROBLEM!

So, actually the issue is not in the complicated part but in all the simple "DELETE FROM" I have at the begining! It looks like each DELETE FROM nested levels (of course you can have 4-5 nested level per table) are ***ulated so we reach the 32! If I remove a few of them it's ok.

Are there any solution in this case? I feel a bit screwwed here right?

Friday, January 25, 2013 - 7:01:20 PM - dave Back To Top (21718)

THANKS!

I'm trying to apply your advise:

CREATE TRIGGER trig_del_folder ON table_folder INSTEAD OF DELETE AS
   DELETE FROM table_project WHERE EXISTS (SELECT * FROM DELETED WHERE table_project.folder_id = DELETED.folder_id)
   ...
   
   ...
   IF ( 
       EXISTS(SELECT * FROM table_folder, DELETED 
              WHERE table_folder.folder_id_2 = DELETED.folder_id 
              AND table_folder.folder_id != table_folder.folder_id_2)  'just an additional protection to not delete the root node
       AND 
       ((SELECT TRIGGER_NESTLEVEL()) < 30)
       )
      DELETE FROM table_folder WHERE EXISTS (SELECT * FROM DELETED WHERE table_folder.folder_id_2 = DELETED.folder_id);

But this still returns a "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)."

... and even if there are only 2 or 3 levels of folders to be deleted recursively. So there must be an infinite loop somewhere but can't find it.

Any idea?

 

Thursday, January 24, 2013 - 7:37:33 PM - Eric Alter Back To Top (21699)

Dave,

 

I don't have all the specifics for your code worked out, but I've used the following type of solution inside triggers to prevent nesting (recursion). Your trigger code might go something like this:

CREATE TRIGGER trig_del_folder ON table_folder INSTEAD OF DELETE AS

  IF EXISTS(SELECT * FROM table_folder AS E JOIN DELETED AS D ON E.folder_id_2 = D.folder_id)

  AND

  IF TRIGGER_NESTLEVEL() < 2

    BEGIN

    {delete child records...delete parent records...etc.}

    END

  ELSE

    RETURN

Thursday, January 24, 2013 - 7:11:50 PM - dave Back To Top (21698) 
CREATE TRIGGER trig_del_folder ON table_folder FOR DELETE AS
   IF EXISTS(SELECT * FROM table_folder AS E JOIN DELETED AS D ON E.folder_id_2 = D.folder_id) 
      DELETE FROM table_folder 
      FROM table_folder AS E JOIN DELETED AS D ON E.folder_id_2 = D.folder_id
   DELETE FROM table_project WHERE EXISTS (SELECT * FROM DELETED WHERE table_project.folder_id = DELETED.folder_id)
   DELETE FROM table_folder WHERE EXISTS (SELECT * FROM DELETED WHERE table_folder.folder_id = DELETED.folder_id);

sounds to work but it unexpectedly stops after 2 levels... weird!

Thursday, January 24, 2013 - 5:37:49 PM - dave Back To Top (21696)

I have implemented successfully this solution but there is one case where it sounds it does not work: tables that refers to itself.

It's pretty common to have a table_folder with 2 columns: folder_id and folder_id_2 (parent folder id refering to a folder_id). Of course with some other tables such as table_project where projects are in some folders.

folder_id      folder_id_2

1                1

35              1

36              35

37              36

let's delete the folder 36...

 

With a trigger such as:

CREATE TRIGGER trig_del_folder ON table_folder INSTEAD OF DELETE AS
   DELETE FROM table_folder WHERE EXISTS (SELECT * FROM DELETED WHERE table_folder.folder_id_2 = DELETED.folder_id)
   DELETE FROM table_project WHERE EXISTS (SELECT * FROM DELETED WHERE table_project.folder_id = DELETED.folder_id)
   DELETE FROM table_folder WHERE EXISTS (SELECT * FROM DELETED WHERE table_folder.folder_id = DELETED.folder_id);

In this case when deleting a parent I was expecting all the childs to be deleted by the trigger (recursive first clause) unfortunately:

"Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)."

This has nothing to see with the number of child. On parent with no child will through the same error :((

Any idea to warkaround this?

Thursday, January 17, 2013 - 1:01:20 PM - Ben Snaidero Back To Top (21527)

Hi Eric

An AFTER trigger would not work since it would fire after the original delete and would fail since the record from the child table needs to be removed first.  Since the trigger code executes as a transaction when using an INSTEAD of trigger you will also have the benefit of the parent delete rolling back should any of the child deletes encounter an error and rollback.  You can test both of these scenarios by modifying my example above to use AFTER triggers.

Thanks for reading

Ben

 

Wednesday, January 16, 2013 - 4:21:05 PM - Eric Alter Back To Top (21492)

Ben-

Excellent article on the foreign key cascade conflict! And nice job keeping your scenario simple and to the point. As I am about to tackle a real-world project (using cascading updates) with these exact implications, I found your information to be very useful.

 

My one question to you, though, is: what is the advantage of using Instead Of triggers to handle the conflict instead of (pardon the pun) the more common AFTER triggers?

It seems to me that an AFTER UPDATE trigger would be able to execute an update on a child table successfully as well as an INSTEAD OF UPDATE trigger. You would also have the advantage that if the child-table update fails, the AFTER UPDATE trigger would also fail, maintaining the referential integrity.

 

Am I missing something?

Thanks!

Monday, December 10, 2012 - 3:41:11 PM - dave Back To Top (20853)

Too bad. Redoing ON DELETE CASCADE using triggers is working well on my side but it's a shame we have to do this.

Anyway, thanks for your thread. It has been a great help!

Monday, December 10, 2012 - 12:53:40 PM - Ben Snaidero Back To Top (20851)

@dave

Unfortunately this is also the case with SQL Server 2012

Thanks for reading

Ben 

Friday, December 7, 2012 - 6:20:59 PM - dave Back To Top (20815)

My app. work on Oracle and MySql with no problem but many issues of this kind on MsSql Server 2005. I'm implementing the solution with the triggers but I'm afraid perfs will suffer from this...

Is this resolved in MsSql Server 2012 by any chance?

Dave

 

 

Sunday, August 26, 2012 - 10:17:45 AM - Alex Back To Top (19230)

Oh and I too see nothing intrinsically wrong with the hypotherical model. I'm sure I've made table structures such as this. I certainly hit the cyclic cascades restriction frequently. It certainly does not mean the table structure is faulty in any way.

Sunday, August 26, 2012 - 10:14:21 AM - Alex Back To Top (19229)

Thanks for explaining Ben. Yeah I take your point. It is more consistent but I do think 3 cascaded constraints and 1 trigger is less to maintain and go wrong then 4 non-cascaded constraints and 4 triggers. As with so many things its a matter of weighing up the pros and cons and personal preference.

It's certainly useful to know of this method and you've explained it well, Thanks for that.

Friday, August 24, 2012 - 9:17:46 PM - DBAdmin Back To Top (19221)

I see nothing wrong with the model or data structure.

Pretend Parent is School. Pretend Child1 is Grade. Pretend Child2 is Subject. Only certain Grade and Subject combinations are valid for a given School. It seems very acceptable for the GrandChild table to be GradeSubject, defining which Grade and Subject combinations are valid. Do you have a better way to model the situation?

 

Friday, August 24, 2012 - 1:26:13 PM - Ben Snaidero Back To Top (19218)

Hi Alex,

The main reason I say to use only triggers and not mix triggers with constraints with CASCADE DELETE is simply for consistency sake.  In my experience, especially when looking at a database structure that someone else designed, I much prefer a consistent way of handling a given scenario.  Makes things much easier to follow and removes any confusion.

Thanks for reading

Ben.

Friday, August 24, 2012 - 1:14:32 PM - Ben Snaidero Back To Top (19216)

Hi Ben,

I'll agree with you that the schema in this article should probably be modified but it wasn't really meant to be so much a real world example, more just to illustrate the idea of using a trigger to solve this issue.  In the real world when you do come across this issue you'll probably have your cascade path going through mulitple tables but for the sake of simplicity in this article I wanted to have as simple a schema structure as possible to avoid complexity.

Thanks for reading

Ben.

Friday, August 24, 2012 - 9:21:04 AM - Ben Taylor Back To Top (19212)

Honestly, I have a problem with your data structure.

I can't imagine a need for a schema that has two child tables from the same parent, that have foreign key relationships to a grandchild.

My impression, without knowing all the specifics, but based on 30 years of relational database design, is that you have a schema that should be modified, rather than trying to find a work around for the way SQL Server constraints perform.

I appreciate the skill and techniques you demonstrate. However, I think you may be treating the symptoms rather than fixing the root problem.

 

Thanks for taking the time to share...write more stuff...you're good at explaining things.

Friday, August 24, 2012 - 5:38:30 AM - Alex Back To Top (19205)

You say "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." but my understanding/experience is that, even with Delete No Action, the constraint will be maintained: deletion from the parent won't be allowed if there are related child records. So, no orphan records - instead the delete would be refused.

Or is there some setting that allows orphaned records in a foreign key constraint that I'm not aware of? And if so, why would anyone ever do that?

Why not do the trigger as an After Delete (or better still After Update/Delete and then it could cascade updates too)? Why Instead Of unless you intend to rollback the delete, in which case DELETE NO ACTION is fine: it will do that.

You go on to say "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."

Why not leave three of them as contraints (solid, reliable, quick and easy) and just create one After Update/Delete trigger on one of the joins to the GrandChild? Much better surely.

I've not got many years experience with SQL but quite a bit and that's why I ask. This seems so not the way I would do it (have been doing it) I wan't to know what I'm missing (or put the point across if I'm right).














get free sql tips
agree to terms