SQL Server Performance

Cyclic relationships

Discussion in 'General Developer Questions' started by Adriaan, Mar 14, 2005.

  1. Adriaan New Member

    SQL Server is a little iffy when you try to create FKs that it regards as "cyclic". I can understand SQL refusing to create a cyclic relationship with cascades - you could get into an infinite loop, so that's a very clear no-no in itself.

    The problem is that SQL Server seems to be checking sets of tables for cyclic relationships, and doesn't check whether the relationships actually start and end at the same column(s). If that is not the case, the way I understand it there is no infinite loop, and there should be no objection.

    In our system, we have three tables A, B, and C. Table A should have FK relationships with both B and C for two separate columns, with cascading updates from B and C towards A. Table B has a FK referring to table C, but the RK field in C for the RK in B does not cascade the updates to the FK field in A for the RK in C - so I wouldn't think that's a cyclic relationship.

    Still, if we already have two of the 3 relationships in place, SQL Server refuses to create the third. And that's the point where we need to "implement" the last cascading relationship in triggers.

    With all the suggestions to implement relationships rather than handling stuff in triggers, it would be nice if SQL could cooperate a little. Does anyone know if this will be solved in SQL 2005?

Share This Page