Ciclic Foreign Keys?

Hi:
Scenario:
-) I have four tables TableA, TableB, TableC and ProductTable.
-) TableA is the main header table of TableB, TableB contains a
reference to a 'Product' in table ProductTable.
-) TableB is, in turn, a header table of TableC, TableC contains a
reference to a 'Product' in table ProductTable.
-) I have a ForeignKey with a 'ON DELETE CASCADE' constraint from TableB
to TableA, so if the row in TableA is deleted, it also deletes TableB's
corresponding row(s).
-) I have a ForeignKey with a 'ON DELETE CASCADE' constraint from TableC
to TableB, so if the row in TableB is deleted, it also deletes TableC's
corresponding row(s).
Now I want to add 2 more ForeignKeys from TablaB and TableC to the
ProductTable with CASCADING DELETES, so if the product is deleted it
will automatically delete corresponding rows from TableB and TableC.
I can set the ForeignKey on *one* of the tables, but I can't set it on
both as the servers informs me that this would cause a cyclic action. I
can't see what the problem is. If TableB contains a reference to a
particular product (lets call it 'ProductX') and TableC contains a
reference to another product ('ProductY') there is not cyclic deletion.
Can anyone help me workout what the problem is.
TIA,
MartinH.The problem is that there are multiple cascade paths from ProductTable to
TableC. There isn't any declarative way to specify that the reference
between TableB and ProductTable and the reference between TableC and
ProductTable can never refer to the same product, so SQL Server must assume
that they can, and consequently prevents the referential action declaration.
It looks like you're going to have to use a trigger to implement the cascade
delete.
"Martin Hart" <martin.hartturner@.gmail.com> wrote in message
news:OCJ2eE2kFHA.320@.TK2MSFTNGP09.phx.gbl...
> Hi:
> Scenario:
> -) I have four tables TableA, TableB, TableC and ProductTable.
> -) TableA is the main header table of TableB, TableB contains a
> reference to a 'Product' in table ProductTable.
> -) TableB is, in turn, a header table of TableC, TableC contains a
> reference to a 'Product' in table ProductTable.
> -) I have a ForeignKey with a 'ON DELETE CASCADE' constraint from TableB
> to TableA, so if the row in TableA is deleted, it also deletes TableB's
> corresponding row(s).
> -) I have a ForeignKey with a 'ON DELETE CASCADE' constraint from TableC
> to TableB, so if the row in TableB is deleted, it also deletes TableC's
> corresponding row(s).
> Now I want to add 2 more ForeignKeys from TablaB and TableC to the
> ProductTable with CASCADING DELETES, so if the product is deleted it
> will automatically delete corresponding rows from TableB and TableC.
> I can set the ForeignKey on *one* of the tables, but I can't set it on
> both as the servers informs me that this would cause a cyclic action. I
> can't see what the problem is. If TableB contains a reference to a
> particular product (lets call it 'ProductX') and TableC contains a
> reference to another product ('ProductY') there is not cyclic deletion.
> Can anyone help me workout what the problem is.
> TIA,
> MartinH.|||Brian:
Thanks, I now understand why, and thanks to your suggestion 'how' I can
get around the problem.
Thanks again.
Martin.
Brian Selzer escribi:
> The problem is that there are multiple cascade paths from ProductTable to
> TableC. There isn't any declarative way to specify that the reference
> between TableB and ProductTable and the reference between TableC and
> ProductTable can never refer to the same product, so SQL Server must assum
e
> that they can, and consequently prevents the referential action declaratio
n.
> It looks like you're going to have to use a trigger to implement the casca
de
> delete.
>
> "Martin Hart" <martin.hartturner@.gmail.com> wrote in message
> news:OCJ2eE2kFHA.320@.TK2MSFTNGP09.phx.gbl...
>
>
>|||I prefer to avoid cascading referential actions whenever possible because
they introduce an additional level of complexity into deadlock minimization
and avoidance. It's better in my opinion to spell out the deletes or
updates, either in an instead of trigger or in a stored procededure. This
way I control the order in which locks are obtained, thus minimizing
deadlocks.
"Martin Hart" <martin.hartturner@.gmail.com> wrote in message
news:#f7DXN3kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Brian:
> Thanks, I now understand why, and thanks to your suggestion 'how' I can
> get around the problem.
> Thanks again.
> Martin.
> Brian Selzer escribi:
to
assume
declaration.
cascade