multiple cascade

Hello,
I need to do a relation between 2 tables with this request (under SQL server
2005)
ALTER TABLE "dbo"."PrmBox"
ADD CONSTRAINT "FK_OrgSet_PrmBox" FOREIGN KEY ("n_oset")
REFERENCES "dbo"."OrgSet" ("n_oset") on update cascade on delete cascade
But I have the error : ... may cause cycles or multiple cascade paths.
SQL sever thinks there is a problem, but really there is no problem.
1) Is it possible to disabled this check ?
2) if no, I want to do this relation with a trigger, for the delete there is
no problem, but for the update I don't know how to do the relation between
the deleted and inserted tables to find the old data and the new data in the
table OrgSet.
ThanksAlan
CREATE TABLE GF --GRANDFATHERS
(
[ID] INT NOT NULL PRIMARY KEY,
[NAME]CHAR(1) NOT NULL
)
INSERT INTO GF VALUES (1,'A')
INSERT INTO GF VALUES (2,'B')
INSERT INTO GF VALUES (3,'C')
CREATE TABLE F --FATHERS
(
[ID] INT NOT NULL PRIMARY KEY,
GFID INT NOT NULL FOREIGN KEY REFERENCES GF([ID])ON DELETE NO ACTION ON
UPDATE NO ACTION,
[NAME]CHAR(2) NOT NULL
)
INSERT INTO F VALUES (1,1,'AA')
INSERT INTO F VALUES (2,1,'AA')
INSERT INTO F VALUES (3,2,'BB')
INSERT INTO F VALUES (4,2,'BB')
INSERT INTO F VALUES (5,2,'BB')
INSERT INTO F VALUES (6,3,'CC')
DELETE FROM GF WHERE [ID]=1--Cause the error to be thrown
ALTER TABLE F NOCHECK CONSTRAINT FK__F__GFID__54968AE5
--SP_HELPCONSTRAINT 'F'
DELETE FROM GF WHERE [ID]=1-- No error
SELECT * FROM F
DROP TABLE F
DROP TABLE GF
"Alan" <Alan@.discussions.microsoft.com> wrote in message
news:85A0DB79-CACA-4050-B80A-66015BC87D93@.microsoft.com...
> Hello,
> I need to do a relation between 2 tables with this request (under SQL
> server
> 2005)
> ALTER TABLE "dbo"."PrmBox"
> ADD CONSTRAINT "FK_OrgSet_PrmBox" FOREIGN KEY ("n_oset")
> REFERENCES "dbo"."OrgSet" ("n_oset") on update cascade on delete cascade
> But I have the error : ... may cause cycles or multiple cascade paths.
> SQL sever thinks there is a problem, but really there is no problem.
> 1) Is it possible to disabled this check ?
> 2) if no, I want to do this relation with a trigger, for the delete there
> is
> no problem, but for the update I don't know how to do the relation between
> the deleted and inserted tables to find the old data and the new data in
> the
> table OrgSet.
>
> Thanks|||There is a problem with the cascade, I need to change the value of the link
in the linked table (Father in your example).
"Uri Dimant" wrote:

> Alan
> CREATE TABLE GF --GRANDFATHERS
> (
> [ID] INT NOT NULL PRIMARY KEY,
> [NAME]CHAR(1) NOT NULL
> )
> INSERT INTO GF VALUES (1,'A')
> INSERT INTO GF VALUES (2,'B')
> INSERT INTO GF VALUES (3,'C')
> CREATE TABLE F --FATHERS
> (
> [ID] INT NOT NULL PRIMARY KEY,
> GFID INT NOT NULL FOREIGN KEY REFERENCES GF([ID])ON DELETE NO ACTION ON
> UPDATE NO ACTION,
> [NAME]CHAR(2) NOT NULL
> )
>
> INSERT INTO F VALUES (1,1,'AA')
> INSERT INTO F VALUES (2,1,'AA')
> INSERT INTO F VALUES (3,2,'BB')
> INSERT INTO F VALUES (4,2,'BB')
> INSERT INTO F VALUES (5,2,'BB')
> INSERT INTO F VALUES (6,3,'CC')
> DELETE FROM GF WHERE [ID]=1--Cause the error to be thrown
> ALTER TABLE F NOCHECK CONSTRAINT FK__F__GFID__54968AE5
> --SP_HELPCONSTRAINT 'F'
> DELETE FROM GF WHERE [ID]=1-- No error
> SELECT * FROM F
> DROP TABLE F
> DROP TABLE GF
>
>
>
> "Alan" <Alan@.discussions.microsoft.com> wrote in message
> news:85A0DB79-CACA-4050-B80A-66015BC87D93@.microsoft.com...
>
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.|||On Thu, 2 Feb 2006 02:04:26 -0800, Alan wrote:

>Hello,
>I need to do a relation between 2 tables with this request (under SQL serve
r
>2005)
>ALTER TABLE "dbo"."PrmBox"
> ADD CONSTRAINT "FK_OrgSet_PrmBox" FOREIGN KEY ("n_oset")
> REFERENCES "dbo"."OrgSet" ("n_oset") on update cascade on delete cascade
>But I have the error : ... may cause cycles or multiple cascade paths.
>SQL sever thinks there is a problem, but really there is no problem.
>1) Is it possible to disabled this check ?
Hi Alan,
No. I think that this limitation is a result of some limitation in the
internal mechanism used to perform cascading operations.

>2) if no, I want to do this relation with a trigger,
Make sure to use an INSTEAD OF trigger. Since constraints are checked
*bofore* executing a trigger, an AFTER trigger would never even be fired
on a DELETE or UPDATE that would violate the constraint.

>for the delete there is
>no problem, but for the update I don't know how to do the relation between
>the deleted and inserted tables to find the old data and the new data in th
e
>table OrgSet.
You can't - unless you have some other column (or combinations of
columns) that is certain to be unique and not to change. (An IDENTITY
column with a UNIQUE constraint would be great).
In an UPDATE trigger, there is no way to distinguish these two UPDATE
statements, unless other columns in the rows can be used.
UPDATE MyTable SET KeyColumn = 3 - KeyColumn WHERE KeyColumn IN (1, 2)
UPDATE MyTable SET KeyColumn = KeyColumn WHERE KeyColumn IN (1, 2)
In both cases, both the inserted and the deleted table will hold two
rows, with KeyColumn equal to 1 in one of the rows and 2 in the other
one.
If you can't correlate the rows by other columns, you'll have to limit
update operations on the primary key to single-row operations - start
the trigger with
IF UPDATE(KeyColumn)
AND ROWCOUNT > 1
BEGIN
RAISERROR (...)
ROLLBACK TRAN
RETURN
END
Hugo Kornelis, SQL Server MVP