locked
Multiple cascade paths in simple 3-table design. Need help getting rid of this (error 1785). RRS feed

  • Question

  • My scenario is rather simple, I think:

    TableA(ID, ...)

    TableB(ID, A_ID foreign key to TableA.ID with cascade on update, ...)

    TableC(ID, B_ID foreign key to TableB.ID with cascade on update, A_ID foreign key to TableA.ID with cascade on update, ...)

    Just like that.  3 tables defining 3 different object types.  Objects of type B may (or may not) be related to an object A (TableB.A_ID is nullable); objects of type C will always be related to an object of type B (TableC.B_ID is non- nullable) while they may also be related to an object of type A (TableC.A_ID is nullable).

    SQL Server 2012 RTM (I'll be later migrating this to SQL Azure) has no problems creating tables A and B, but it refuses to create table C with error 1785.  It claims that it is unable to create the FOREIGN KEY constraint for field TableC.A_ID because of potential cycles or multiple cascading paths.  I can see that a change in the ID of object A may trigger changes in more than one table, but since when is this a No-No?  I know it has something to do with FOREIGN KEY on TableB.A_ID or TableC.B_ID (or both?) but I just can't see this clearly.

    Can someone explain this to me?  Is there a solution/workaround to what I want to do?  My business logic, in a nutshell, is this:

    A:  An owner.

    B:  A container of objects of type C.  A container may or may not be owned by an owner (A).

    C:  An asset.  Assets will always be contained in some container (B), but there's one shared container, and in that shared container I still need to know if assets (C) have an owner (A).  The owner in C is redundant if the container (B) containing it has an owner (A), but again:  This is not always the case.  I can have C's owned by A's in a shared container (B) -a B with no owner (A)-.

    Did i explain clearly?  Let me know if I did not.


    Jose R. MCP
    Code Samples

    Monday, July 9, 2012 6:04 PM

Answers

  • Cascade operations are very picky, you can't even cascade twice from the same table to another table:

    create table A
    (
     AId int PRIMARY KEY
    )
    CREATE TABLE B
    (
     BId int PRIMARY KEY,
     AId int FOREIGN KEY References A (AId) ON UPDATE CASCADE,
     AnotherAId int FOREIGN KEY References A (AId) ON UPDATE CASCADE
    )

    Msg 1785, Level 16, State 0, Line 5
    Introducing FOREIGN KEY constraint 'FK__B__AnotherAId__173876EA' on table 'B' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
    Msg 1750, Level 16, State 0, Line 5
    Could not create constraint. See previous errors.

    In your case, it is

    drop table c,b,a
    go
    create table A
    (
     AId int PRIMARY KEY
    )
    CREATE TABLE B
    (
     BId int PRIMARY KEY,
     AId int FOREIGN KEY References A (AId) ON UPDATE CASCADE
    )
    CREATE TABLE C
    (
     CId int PRIMARY KEY
     ,BId int FOREIGN KEY References B (BId) ON UPDATE CASCADE
     ,AId int FOREIGN KEY References A (AId) ON UPDATE CASCADE
    )

    From http://msdn.microsoft.com/en-us/library/ms186973(SQL.105).aspx (I can't find it stated in 2012 docs, and I looked :)  "the tree of cascading referential actions must not have more than one path to any specified table. " You would have to remove any one of the update cascade settings to make it work. The way I interpret this is because an update to A would cascade to B, and that would cascade to C through B, and to C from A.

    In these cases, you generally need to just change to use a trigger and carefully control how the cascade behaves...


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    • Proposed as answer by Mike MMC Thursday, July 12, 2012 5:39 PM
    • Marked as answer by amber zhangEditor Monday, July 16, 2012 8:11 AM
    Tuesday, July 10, 2012 4:47 AM