RE: ON DELETE CASCADE

RE: ON DELETE CASCADE

 

  

I had a feeling about that. But there really is only one path, from
users to funcam. When the primary key in users is updated funcam is
changed in two places at the end of the one path. (Now I'm just being
obstinate)

-----Original Message-----
From: Brendt Hess
[mailto:mssqldba-ezmlmshield-x31547310.[Email address protected]
Sent: Tuesday, May 29, 2007 4:11 PM
To: LazyDBA Discussion
Subject: RE: ON DELETE CASCADE

From http://support.microsoft.com/kb/321843

CAUSE
You receive this error message because in SQL Server, a table cannot
appear more than one time in a list of all the cascading referential
actions that are started by either a DELETE or an UPDATE statement. For
example, the tree of cascading referential actions *****must only have
one path to a particular table on the cascading referential actions
tree*****.

It's not a bug - it's a feature!



==+==+==+==+==+==+==+==+==+==+==+==+
Brendt W. Hess
Database Administrator, Motosport, Inc.
brendt.[Email address protected]
==+==+==+==+==+==+==+==+==+==+==+==+

-----Original Message-----
From: Ligda John
[mailto:mssqldba-ezmlmshield-x26263782.[Email address protected]
Sent: Tuesday, May 29, 2007 3:43 PM
To: LazyDBA Discussion
Subject: ON DELETE CASCADE

Why do I get the following error when I set up two foreign keys
constraints (two different columns) in the child tables which both
reference the same primary key column in the parent table? If the
parent primary key is changed or deleted the child table should be
updated or set to null in two columns. Is it possible?





USE DotNetNuke
go
ALTER TABLE dbo.FunCam ADD CONSTRAINT FK_FunCam_Reporting1_Users_UserID
FOREIGN KEY (Reporting1) REFERENCES dbo.Users (UserID) ON UPDATE
CASCADE go

USE DotNetNuke
go
ALTER TABLE dbo.FunCam ADD CONSTRAINT FK_FunCam_Reporting2_Users_UserID
FOREIGN KEY (Reporting2) REFERENCES dbo.Users (UserID) ON UPDATE
CASCADE go







15:39:34.521 DBMS OHSTG2SF -- Error: Introducing FOREIGN KEY
constraint 'FK_FunCam_Reporting2_Users_UserID' on table 'FunCam' may
cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or
ON UPDATE NO ACTION, or modify other FOREIGN KEY
constraints.(42000,1785), Batch 2 Line 1



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



MS Sql Server LazyDBA home page