July 9, 2010 at 4:15 am
Hi,
I have a table called ContactTypes (ContactTypeId, TypeDescription). ContactTypeId is a primary key int that is not an Identity column.
I have another table called ContactTypeRelationship (RelationshipId (PK Identity), ParentTypeId, ChildContactTypeId).
There is a relationship between ParentTypeId (in ContactTypeRelationship) and ContactTypeId (in ContactType)
There is also a relationship between ChildTypeId (in ContactTypeRelationship) and ContactTypeId (in ContactType)
It seems I cannot have cascading updates on BOTH relationships.. I get the following error..
Msg 1785, Level 16, State 0, Line 1
Introducing FOREIGN KEY constraint 'FK_ContactTypeRelationship_ChildContactType' on table 'ContactTypeRelationship' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
I feel like I am doing something wrong as I cannot see how this situation would ' cycles or multiple cascade paths'...
Any advice or guidance much appreciated.
Regards,
OJ
July 9, 2010 at 7:52 am
From BO:
"Individual DELETE or UPDATE statements can start a series of cascading referential actions. For example, a database contains three tables: TableA, TableB, and TableC. A foreign key in TableB is defined with ON DELETE CASCADE against the primary key in TableA. A foreign key in TableC is defined with ON DELETE CASCADE against the primary key in TableB. If a DELETE statement deletes rows in TableA, the operation also deletes any rows in TableB that have foreign keys matching the deleted primary keys in TableA, and then deletes any rows in TableC that have foreign keys that match the deleted primary keys in TableB.
The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree that contains no circular references. No table can appear more than one time in the list of all cascading referential actions that result from the DELETE or UPDATE. Also, the tree of cascading referential actions must not have more than one path to any specified table. Any branch of the tree is ended when it encounters a table for which NO ACTION has been specified or is the default."
You just hit an inherent limit of SQL Server. One of the two relationships can cascade, but not both. There doesn't seem to be a way to do it the way you are trying to in this case.
July 9, 2010 at 7:59 am
hi Jeff,
thanks for the reply. I guess I need to get better at searching BOL.
w/regard to the issue, now that my original solution has been discounted, I guess I will revert to an identiy column in the ContactType table. The reason that I didn't want identity (if anyone cares!) is because I use an ORM to map certain tables to c# ENUMS and it is very handy to be able to control the enum value (ie the pk value).
Unless any gurus have a better suggestion? i am all ears...(literally, they are huge!!)
Again, thanks for the help Jeff...
Regards,
O
July 12, 2010 at 4:51 pm
Hi Joe,
You make a lot of assumptions about the influence I command on these things. The table is used to restrict the parentcontactid in the following table. It is used in a trigger....you should have a field day with this. With regard to my "useless narrative", I asked a question and got a vaid, relevant response.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[ContactType](
[ContactType_ID] [int] NOT NULL,
[TypeShortName] [varchar](64) NOT NULL,
[TypeDescription] [varchar](256) NOT NULL,
[rwversion] [timestamp] NOT NULL,
CONSTRAINT [PK_ContactType] PRIMARY KEY CLUSTERED
(
[ContactType_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]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ContactTypeRelationship](
[ContactTypeRelationshipId] [int] IDENTITY(1,1) NOT NULL,
[ParentTypeId] [int] NOT NULL,
[ChildTypeId] [int] NOT NULL,
[rwvesrion] [timestamp] NOT NULL,
CONSTRAINT [PK_ContactTypeRelationship] PRIMARY KEY CLUSTERED
(
[ContactTypeRelationshipId] 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].[ContactTypeRelationship] WITH CHECK ADD CONSTRAINT [FK_ContactTypeRelationship_ContactType] FOREIGN KEY([ChildTypeId])
REFERENCES [dbo].[ContactType] ([ContactType_ID])
GO
ALTER TABLE [dbo].[ContactTypeRelationship] CHECK CONSTRAINT [FK_ContactTypeRelationship_ContactType]
GO
ALTER TABLE [dbo].[ContactTypeRelationship] WITH CHECK ADD CONSTRAINT [FK_ContactTypeRelationship_ParentContactType] FOREIGN KEY([ParentTypeId])
REFERENCES [dbo].[ContactType] ([ContactType_ID])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[ContactTypeRelationship] CHECK CONSTRAINT [FK_ContactTypeRelationship_ParentContactType]
GO
GO
CREATE TABLE [dbo].[Contacts](
[Contacts_ID] [int] IDENTITY(1,1) NOT NULL,
[ContactType_ID] [int] NOT NULL,
[ParentContactId] [int] NULL,
[rwversion] [timestamp] NOT NULL,
CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED
(
[Contacts_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].[Contacts] WITH CHECK ADD CONSTRAINT [FK_Contacts_ContactType] FOREIGN KEY([ContactType_ID])
REFERENCES [dbo].[ContactType] ([ContactType_ID])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[Contacts] CHECK CONSTRAINT [FK_Contacts_ContactType]
GO
ALTER TABLE [dbo].[Contacts] WITH CHECK ADD CONSTRAINT [FK_Contacts_ParentContacts] FOREIGN KEY([ParentContactId])
REFERENCES [dbo].[Contacts] ([Contacts_ID])
GO
ALTER TABLE [dbo].[Contacts] CHECK CONSTRAINT [FK_Contacts_ParentContacts]
GO
ALTER TABLE [dbo].[Contacts] WITH CHECK ADD CONSTRAINT [CK_Contacts_ParentNotSelf] CHECK (([Contacts_ID]<>[ParentContactId]))
GO
ALTER TABLE [dbo].[Contacts] CHECK CONSTRAINT [CK_Contacts_ParentNotSelf]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[tr_ValidParentContactType]
ON [dbo].[Contacts]
FOR INSERT,UPDATE
AS
DECLARE @pcid int
SET @pcid = (SELECT ParentContactId FROM inserted)
IF @pcid IS NOT NULL
BEGIN
IF NOT EXISTS(
SELECT ctr.ContactTypeRelationshipId
FROM inserted AS c
INNER JOIN ContactTypeRelationship AS ctr ON c.ContactType_ID = ctr.ChildTypeId
INNER JOIN Contacts AS p ON ctr.ParentTypeId = p.ContactType_ID AND c.ParentContactId = p.Contacts_ID
WHERE p.Contacts_ID = @pcid)
BEGIN
ROLLBACK
--Use 16 to prevent further processing (ie abort the update/insert)
RAISERROR('The parent contact type is not valid for this Contact Type', 16, 1)
END
END
GO
Cheers,
O
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply