Handling circular relationships

I have the following table relationships in a SQL Server 2000 database.
TableA
-> TableA_ID TableB
| TableB_ForeignKey --> TableB_ID <-
| |
| |
| |
| TableC |
| TableC_ID |
-- TableA_ForeignKey |
TableB_ForeignKey --
For proper maintenance, each of these relationships should be defined with
cascade delete, but SQL Server will not allow this because of the circular
nature of the relationships. SQL Server is happy if, say, the relationship
between TableC and TableB is not set up with cascade delete, but then
deletions from TableB run the risk of leaving orphans in TableC. What is the
best way of handling this situation? Thanks in advance for your assistance.
You can enforce the cascade delete between TableC and TableB via a trigger
(or to be consistent enforce cascade delete for all relationships via
triggers). That way you still enforce referential integrity through a
foreign key without the cascading option, and then use triggers to cascade
deletes.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Forgot to mention that you will have to use INSTEAD OF triggers because
constraints are checked before AFTER triggers are fired.
An alternative option would be to avoid using foreign key constraints and to
use only AFTER triggers to enforce both referential integrity (you will have
to manually check and rollback the transaction if referential integrity is
violated) and cascade deletes.
Plamen Ratchev
http://www.SQLStudio.com
|||This appearts to be a data modeling issue; I think that your relationships
are not properly defined. I'm betting that the proper FK on TableC should
be a single FK pointing to TableA (TableA_Id, TableB_Id), rather than two
separate FKs pointing to each of the other two tables -- but since you
haven't told us the domain/business purpose of these tables, it's impossible
to tell. Can you share some more information? Will the key combinations of
TableA_Id and TableB_Id in TableC really be different than in TableA?

Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Steve B." <SteveB@.discussions.microsoft.com> wrote in message
news:B707D7DC-713E-4759-A1D6-C25553BAF8DF@.microsoft.com...
>I have the following table relationships in a SQL Server 2000 database.
>
> TableA
> -> TableA_ID TableB
> | TableB_ForeignKey --> TableB_ID <-
> | |
> | |
> | |
> | TableC |
> | TableC_ID |
> -- TableA_ForeignKey |
> TableB_ForeignKey --
> For proper maintenance, each of these relationships should be defined with
> cascade delete, but SQL Server will not allow this because of the circular
> nature of the relationships. SQL Server is happy if, say, the relationship
> between TableC and TableB is not set up with cascade delete, but then
> deletions from TableB run the risk of leaving orphans in TableC. What is
> the
> best way of handling this situation? Thanks in advance for your
> assistance.
>
|||Adam - Thanks for the thoughtful response. It turns out that in this case,
yes, the TableB_Id in TableC really will be different than in the related
TableA record. The entities in TableB have relationships to each other, and
so the circular relationships result from the need to effectively join TableB
back on itself.
Regards,
Steve B.
"Adam Machanic" wrote:

> This appearts to be a data modeling issue; I think that your relationships
> are not properly defined. I'm betting that the proper FK on TableC should
> be a single FK pointing to TableA (TableA_Id, TableB_Id), rather than two
> separate FKs pointing to each of the other two tables -- but since you
> haven't told us the domain/business purpose of these tables, it's impossible
> to tell. Can you share some more information? Will the key combinations of
> TableA_Id and TableB_Id in TableC really be different than in TableA?
>
> --
> Adam Machanic
> SQL Server MVP - http://sqlblog.com
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "Steve B." <SteveB@.discussions.microsoft.com> wrote in message
> news:B707D7DC-713E-4759-A1D6-C25553BAF8DF@.microsoft.com...
>
|||Plamen - Many thanks for your assistance. I was thinking that a trigger might
the the answer here, but it is good to have that confirmed by a pro. Also,
the pointer to the INSTEAD OF trigger will save me much time. Thank you again!
Regards,
Steve B.
"Plamen Ratchev" wrote:

> Forgot to mention that you will have to use INSTEAD OF triggers because
> constraints are checked before AFTER triggers are fired.
> An alternative option would be to avoid using foreign key constraints and to
> use only AFTER triggers to enforce both referential integrity (you will have
> to manually check and rollback the transaction if referential integrity is
> violated) and cascade deletes.
> Plamen Ratchev
> http://www.SQLStudio.com
>
>