Multiple Cascade Paths - Fails on SQL Server 2000

SQL Apprentice Question
This is an old issue, which I chose to ignore, since it was for a temporary
app with a handful of users (famous last words, I know).

The "multiple foreign keys" post today got me thinking about it, and I
figured it is worth looking for a solution.


I will post DDL at the end, but the theory is the main point here, so I hope
my description will suffice.


Please forgive the camel case naming convention, and the silly tbl prefixes.
I do not like it either, but it is a standard in our department.


tblParameter has two FK constraints.
One references tblReport.strReportID
One references tblLookupProcedures.strLookupProcedure
Both tblReport and tblLookupProcedures have a FK reference to
tblDatabaseConnections.strConnectionName


Ideally, all of these constraints have ON UPDATE CASCADE, but the second
constraint on tblParameter will error out with this message:


****
Introducing FOREIGN KEY constraint 'FK_tblParameter_tblLookupProcedures' on
table 'tblParameter' may cause cycles or multiple cascade paths. Specify ON
DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY
constraints.
****


I believe the error is caused because updates to the PK in
tblDatabaseConnections triggers updates to the FK in two tables, each of
which are referenced by tblParameter. Now the references are not on the
same columns, but SQL Server seems to think this could lead to circular
references.


The questions are:
1. Is this against RD theory, or is it just a quirk of SQL Server?
2. Is there a setting that will allow this?
3. What is the usual workaround? A trigger is the only thing that I could
think of.


Here is the DDL, scaled down to demonstrate the issue.


CREATE TABLE [dbo].[tblDatabaseConnections] (
[strConnectionName] [varchar] (10) PRIMARY KEY NOT NULL ,
[strDatabase] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[tblLookupProcedures] (
[strLookupProcedure] [varchar] (50) PRIMARY KEY NOT NULL ,
[strProcedureDescription] [varchar] (100) NOT NULL ,
[strConnectionName] [varchar] (10) NOT NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[tblReport] (
[strReportID] [varchar] (10) PRIMARY KEY NOT NULL ,
[strReportDescr] [varchar] (50) NULL ,
[strConnectionName] [varchar] (10) NOT NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[tblParameter] (
[strReportID] [varchar] (10) NOT NULL ,
[strParamID] [varchar] (20) NOT NULL ,
[strLookupProcedure] [varchar] (50) NOT NULL
PRIMARY KEY ([strReportID],[strParamID])
) ON [PRIMARY]
GO


ALTER TABLE [dbo].[tblLookupProcedures] ADD
CONSTRAINT [FK_tblLookupProcedures_tblDatabaseConnections] FOREIGN KEY
([strConnectionName]
) REFERENCES [dbo].[tblDatabaseConnections] ([strConnectionName]
) ON UPDATE CASCADE
GO


ALTER TABLE [dbo].[tblReport] ADD
CONSTRAINT [FK_tblReport_tblDatabaseConnections] FOREIGN KEY
([strConnectionName]
) REFERENCES [dbo].[tblDatabaseConnections] ([strConnectionName]
) ON UPDATE CASCADE
GO


ALTER TABLE [dbo].[tblParameter] ADD
CONSTRAINT [FK_tblParameter_tblReport] FOREIGN KEY
([strReportID]
) REFERENCES [dbo].[tblReport] ([strReportID]
) ON UPDATE CASCADE
GO


--This constraint fails due to a perceived cyclical reference
ALTER TABLE [dbo].[tblParameter] ADD CONSTRAINT
[FK_tblParameter_tblLookupProcedures] FOREIGN KEY
([strLookupProcedure]
) REFERENCES [dbo].[tblLookupProcedures]([strLookupProcedure]
) on update cascade
GO


-- if we remove on update cascade, the constraint gets created
ALTER TABLE [dbo].[tblParameter] ADD CONSTRAINT
[FK_tblParameter_tblLookupProcedures_NOCASCADE] FOREIGN KEY
([strLookupProcedure]
) REFERENCES [dbo].[tblLookupProcedures]([strLookupProcedure]
)
GO


drop table [dbo].[tblParameter];
drop table [dbo].[tblReport];
drop table [dbo].[tblLookupProcedures];
drop table [dbo].[tblDatabaseConnections];



Celko Answers
DB2 had this problem in a very early version. If you had cascade
chains like A-->B, B-->C and A-->C, the final values in C would be
whoever got there last to overwrite the value. Today DB2 has a pretty
good cycle detector and allows some things that it did not before.

The relational rule is that all possible cascade paths must leave the
DB in the same state when they finish and that state has to be validate
under all the constraints.


This is hard to implement in theory -- remember graph theory? So real
products give up at some point, using a combination of a graph and
constraints. SQL Server happens to quit very early :)