locked
Cascade for multiple columns (SS 2005) RRS feed

  • Question

  • Is it possible to use all the cascading abilities of a foreign key with multiple independent columns?  For example, say I have a table of cities (each with its own integer ID), and another table used to denote if there is a direct road connecting any two cities.  So, the second table has only two columns, City1_ID and City2_ID, and the existence of a pair of cities in this table denotes a connecting road exists.  But, I need to have cascading updates so that if either City1 or City2's ID is changed in the main table, this change is updated to the ID's in this road table.  Similarly, if either ID is deleted, then the relationship should be deleted.  I can do this easily enough for the City1_ID by establishing a foreign key between this column and the ID column of the main table.  I don't see how to do it for both ID's in the desired manner.  Any idea how to go about this?
    Wednesday, September 16, 2009 2:23 PM

Answers

  • Btw, setting two foreign keys between the tables is not allowed unless I disable cascading updates and deletes for at least one of the keys, which nullifies what I'm trying to accomplish.

    (The error given is 'may cause cycles or multiple cascade paths', but I don't really see how what I'm doing would create any unending loop, so I'm not sure what it's talking about.)

    Wednesday, September 16, 2009 2:35 PM

All replies

  • Btw, setting two foreign keys between the tables is not allowed unless I disable cascading updates and deletes for at least one of the keys, which nullifies what I'm trying to accomplish.

    (The error given is 'may cause cycles or multiple cascade paths', but I don't really see how what I'm doing would create any unending loop, so I'm not sure what it's talking about.)

    Wednesday, September 16, 2009 2:35 PM
  • Still an issue?

    Thanks!


    Ed Price (a.k.a User Ed), SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Tuesday, April 30, 2013 5:36 AM