Could you use a trigger to do one of the updates...?
-----Original Message-----
From: Ligda John
[mailto:mssqldba-ezmlmshield-x2810864.[Email address protected]
Sent: 30 May 2007 00:19
To: LazyDBA Discussion
Subject: 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
---------------------------------------------------------------------
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
This email and any files transmitted within it are private and confidential.
If you are not the intended recipient, this email and any attachments within it have been transmitted to you in error.
You are prohibited from using, copying, distributing or otherwise using the information contained within this email.
Nothing in this email message amounts to a contractual or legal commitment on the part of Optilan unless confirmed by a communication signed on behalf of the company.
Any email cannot be guaranteed to be secure, error free or free from viruses. Although every possible care is taken by Optilan, Optilan does not accept any liability whatsoever for any loss or damage which may be caused as a result of the transmission of this message by email.
MS Sql Server LazyDBA home page