Relationships problem in Enterprise Manager

microsoft.public.sqlserver.tools

15 Jun 2006 6:20 PM

Edward Diener

I am using Enterprise Manager for Sql Server 2000. In designing a
foreign key relationship for a table, let's call it table A, I have more
then one column acting as a foreign key for the same primary column in
another table, let's call it table B. While I can specify for one of
those foreign key columns in table A, which has the same column in
another table as its primary key, to Cascade Deletes and Updates,
successfully, when I try to also specify Cascade Delete and Updates for
a second of my foreign key columns, which has the same column in another
table as its primary key, I get an error saying that the second
relationship with the Cascade "may cause cycles or multiple cascade paths."

I do not understand why this should be happening. If the Cascade is
allowed for one of my columns, why does allowing it for more than one of
my columns which point to the same primary key column cause this
problem. Or is this just a bug in Sql Server 2000 which has been fixed
in later releases of the product.

16 Jun 2006 3:10 AM

Steve Kass

Edward,

I don't believe this is allowed in any edition of SQL Server. Suppose
you want to cascade deletes on B.parent, and both A.mother
and A.father reference B.parent. If you issued a single delete
on B that happend to delete the referenced parent value for
both A.mother and A.father in a single row, two separate
deleted rows would each cause a cascaded delete on A
for the same row. Handling two cascades to the same row
from one update would be tricky, and although the result for A
would be well-defined, it would be hard to distinguish it from a similar
cases that might not be well-defined, such as the case where
A.mother references two different columns in B with cascading
updates. In this case, if the two referenced rows were both
updated to different values, what should happen to A.mother?

Steve Kass
Drew University

Show quote

"Edward Diener" <ediener@no_spam_incomm.com> wrote in message
news:utiOShKkGHA.1552@TK2MSFTNGP03.phx.gbl...
>I am using Enterprise Manager for Sql Server 2000. In designing a foreign
>key relationship for a table, let's call it table A, I have more then one
>column acting as a foreign key for the same primary column in another
>table, let's call it table B. While I can specify for one of those foreign
>key columns in table A, which has the same column in another table as its
>primary key, to Cascade Deletes and Updates, successfully, when I try to
>also specify Cascade Delete and Updates for a second of my foreign key
>columns, which has the same column in another table as its primary key, I
>get an error saying that the second relationship with the Cascade "may
>cause cycles or multiple cascade paths."
>
> I do not understand why this should be happening. If the Cascade is
> allowed for one of my columns, why does allowing it for more than one of
> my columns which point to the same primary key column cause this problem.
> Or is this just a bug in Sql Server 2000 which has been fixed in later
> releases of the product.

16 Jun 2006 11:28 AM

Edward Diener

Steve Kass wrote:

> Edward,
>
> I don't believe this is allowed in any edition of SQL Server. Suppose
> you want to cascade deletes on B.parent, and both A.mother
> and A.father reference B.parent.

OK, that is my situation.

> If you issued a single delete
> on B that happend to delete the referenced parent value for
> both A.mother and A.father in a single row, two separate
> deleted rows would each cause a cascaded delete on A
> for the same row.

I do not follow this. In the first part you say that the single delete
of B will cause only a single row on A to be deleted since A.mother and
A.father both reference the value of B that is being deleted. That
sounds normal and the single row of A gets deleted also with cascaded
deletes. There is no problem doing that. Then you specify that two
separate rows of B being deleted could each cause the same row of A to
be deleted. Why is that a problem ? After the first row of B is deleted,
cascaded deletes looks to see what rows of A need to be deleted. It
finds the A row and deletes it. Then the next row of B is deleted and
once again cascaded deletes looks to find any row of A that needs to be
deleted. Since the row of A has already been deleted the first time, it
does not find it and moves on. Where is the problem ?

> Handling two cascades to the same row
> from one update would be tricky, and although the result for A
> would be well-defined, it would be hard to distinguish it from a similar
> cases that might not be well-defined, such as the case where
> A.mother references two different columns in B with cascading
> updates.

In my case it is that there are two different columns of A that
reference a single column of B, and never a case where a foreign key
references two different columns. Why would cascaded updates ever
present a problem in my scenario ?

Show quote

> In this case, if the two referenced rows were both
> updated to different values, what should happen to A.mother?
>
>
> Steve Kass
> Drew University
>
>
>
> "Edward Diener" <ediener@no_spam_incomm.com> wrote in message
> news:utiOShKkGHA.1552@TK2MSFTNGP03.phx.gbl...
>> I am using Enterprise Manager for Sql Server 2000. In designing a foreign
>> key relationship for a table, let's call it table A, I have more then one
>> column acting as a foreign key for the same primary column in another
>> table, let's call it table B. While I can specify for one of those foreign
>> key columns in table A, which has the same column in another table as its
>> primary key, to Cascade Deletes and Updates, successfully, when I try to
>> also specify Cascade Delete and Updates for a second of my foreign key
>> columns, which has the same column in another table as its primary key, I
>> get an error saying that the second relationship with the Cascade "may
>> cause cycles or multiple cascade paths."
>>
>> I do not understand why this should be happening. If the Cascade is
>> allowed for one of my columns, why does allowing it for more than one of
>> my columns which point to the same primary key column cause this problem.
>> Or is this just a bug in Sql Server 2000 which has been fixed in later
>> releases of the product.
>
>