SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Can't put multiple relationships between 2 tables in MS SQL 2008

    I have a table (app_pages) that has 2 different foreign keys (fk_page_status_eid & fk_page_subdomain_eid), each of which share the same primary key in another table (app_entries).

    Both app_pages.fk_page_status_eid and app_pages.fk_page_subdomain_eid allow for NULL values.

    Well I can setup a relationship for 1, say between app_pages.fk_page_status_eid and app_entries.entry_id so that ON DELETE, the fk_page_status_eid is set to NULL.

    Upon saving the relationship, all is good. Now I attempt to do likewise with the other FK.

    I setup the second relationship between app_pages.fk_page_subdomain_eid and app_entries.entry_id so that ON DELETE, the fk_page_subdomain_eid is set to NULL.

    Well, when I go to save, I now get an error that states:

    Introducing FOREIGN KEY constraint 'FK_app_pages_app_entries1' on table 'app_pages' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
    Could not create constraint. See previous errors.

    This makes no sense to me because an 'ON UPDATE NO ACTION' exists already. In my mind, if someone deletes from the app_entries, it would set NULL in either fk_page_status_eid or fk_page_subdomain_eid accordingly, but that isn't how it is.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i've seen this error a few times on forums, but i'm afraid i don't remember if there is a solution

    at least the "multiple cascade paths" part of the error message is easy to understand, but why it has to be an error situation is not clear to me at all

    workaround: set both foreign keys to ON DELETE NO ACTION, to avoid the error message -- this will prevent deletion if the app_entries row has a child in either table, so you would trap the error in your code when it happens, issue manual deletes for the child tables, then delete the app_entries row again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I figure since the relationship is to a primary key that auto-increments, I could not set the FK's "ON UPDATE" to anything other than 'NO ACTION' because the DB won't let me just change a PK value willy nilly (makes sense)

    I like the idea of the DB either cutting ties to but leaving (set to NULL) rather that cascading a removal (but I can understand that usefulness as well).

    Out of curiosity, what good would a relationship be in where the ON UPDATE and ON DELETE are both set to 'NO ACTION'?

    That said, with some extra application coding, it should be easy enough to warn the user, and upon approval, remove/null the children before removing the parent. Something like "This record has a parent relationship to other database objects. Would you like to:

    Delete Parent & Set Children to NULL (when applicable)
    or
    Delete Parent & Set Children to Defaults (when applicable)
    or
    Delete Parent & all Related Child Objects

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by aaron.martone View Post
    Out of curiosity, what good would a relationship be in where the ON UPDATE and ON DELETE are both set to 'NO ACTION'?
    for those relationships where an orphan of a parent that doesn't exist or has assumed a new identity would be illogical



    Quote Originally Posted by aaron.martone View Post
    Would you like to:
    never ask this

    too confusing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    lol. Forget "Would you like to", use more "You're gonna" Got it!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •