Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  Database Design and Application Architecture  Cascade Delete Cycle Error Work arounds

Author  Topic  JoeBuntu Starting Member 1 Post Posted - 2009-06-28 : 17:55:21 Here is the setup for my two tables. I am going to include the sql for them at the end of this post. Table BOM: BOM_Id, rFinished_ID, rComponent_ID, Quantity Table Materials: Part_ID, PartNumber, other fields ... my foreign keys are: BOM.rFinished_ID (Foreign) -> Materials.Part_ID (Primary) BOM.rComponent_ID (foreign) -> Materials.Part_ID (Primary) The problem is I can't set both foreign keys as cascade delete or mssql gives me and error referring to cycle paths. To solve this issue I created an INSTEAD OF DELETE trigger that does my necessary deletions. But- using this method It seems that I can't have ANY kind of cascading deletes hooked up to my Materials table if I am using the trigger. I have many tables that point to my Materials table that I would like to use a cascade delete on instead of adding delete statements to my trigger. Is there a better way to do this? It would be nice if I could use this trigger to handle the cascade deletes on just the Bill_Of_Materials table and then have some way to have the trigger resume a normal deletion so that I can use cascading deletes for all my other tables. Thanks in advance, below is the sql for the two tables: /****** Object: Table [dbo].[Bill_Of_Materials] Script Date: 06/28/2009 17:51:19 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Bill_Of_Materials]( [BOM_Id] [bigint] IDENTITY(1,1) NOT NULL, [rFinished_Id] [bigint] NOT NULL, [rComponent_Id] [bigint] NOT NULL, [Component_Qty] [bigint] NOT NULL, CONSTRAINT [PK_Bill_Of_Materials] PRIMARY KEY CLUSTERED ( [BOM_Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Bill_Of_Materials] WITH CHECK ADD CONSTRAINT [FK_Bill_Of_Materials_Finished] FOREIGN KEY([rFinished_Id]) REFERENCES [dbo].[Materials] ([Part_Id]) GO ALTER TABLE [dbo].[Bill_Of_Materials] CHECK CONSTRAINT [FK_Bill_Of_Materials_Finished] GO ALTER TABLE [dbo].[Bill_Of_Materials] WITH CHECK ADD CONSTRAINT [FK_Bill_Of_Materials_Materials_Component] FOREIGN KEY([rComponent_Id]) REFERENCES [dbo].[Materials] ([Part_Id]) GO ALTER TABLE [dbo].[Bill_Of_Materials] CHECK CONSTRAINT [FK_Bill_Of_Materials_Materials_Component] /****** Object: Table [dbo].[Materials] Script Date: 06/28/2009 17:53:08 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Materials]( [Part_Id] [bigint] IDENTITY(1,1) NOT NULL, [Part_Number] [nchar](20) NOT NULL, [Description] [nchar](30) NOT NULL, [rType] [bigint] NOT NULL, [PeicesPerCase] [bigint] NOT NULL, [EachesPerPeice] [bigint] NOT NULL, CONSTRAINT [PK_Materials] PRIMARY KEY CLUSTERED ( [Part_Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Materials] WITH CHECK ADD CONSTRAINT [FK_Materials_Material_Types] FOREIGN KEY([rType]) REFERENCES [dbo].[Material_Types] ([Type_Id]) GO ALTER TABLE [dbo].[Materials] CHECK CONSTRAINT [FK_Materials_Material_Types]