Professional Web Applications Themes

may cause cycles or multiple cascade paths - Microsoft SQL / MS SQL Server

Dear all, This still appears "broken" at SQL2000 SP3. Is there a non-trigger work around? Create Table ForKey ( MyID int not null identity primary key, MyText varchar(30) ) go Create Table Refs ( SomeID int not null primary key, RefID1 int not null foreign key references ForKey(MyID) on update cascade on delete cascade, RefID2 int not null foreign key references ForKey(MyID) on update cascade on delete cascade ) Server: Msg 1785, Level 16, State 1, Line 2 Introducing FOREIGN KEY constraint 'FK__Refs__RefID2__1BFD2C07' on table 'Refs' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON ...

  1. #1

    Default may cause cycles or multiple cascade paths

    Dear all,

    This still appears "broken" at SQL2000 SP3. Is there a non-trigger work around?

    Create Table ForKey
    (
    MyID int not null identity primary key,
    MyText varchar(30)
    )
    go

    Create Table Refs
    (
    SomeID int not null primary key,
    RefID1 int not null foreign key references ForKey(MyID) on update cascade on delete cascade,
    RefID2 int not null foreign key references ForKey(MyID) on update cascade on delete cascade
    )

    Server: Msg 1785, Level 16, State 1, Line 2
    Introducing FOREIGN KEY constraint 'FK__Refs__RefID2__1BFD2C07' on table 'Refs' may cause cycles or multiple
    cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
    Server: Msg 1750, Level 16, State 1, Line 2
    Could not create constraint. See previous errors.


    Andrew Guest

  2. #2

    Default Re: may cause cycles or multiple cascade paths

    David,

    Perhaps I should have spelt out the "feature". You can't have two foreign keys from one table on to
    another. This is not a dodgy requirement of a non-normalised database. Consider for example
    a CreatedByUser and a LastUpdatedUser column in a table, both referencing a Users table,
    ONLY DIFFERENT ROWS as per the example in MS page:

    http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q321/8/43.asp&NoWebContent=1

    If it was a feature, the words " this behaviour is by design " would appear somewhere on the page.
    So I'll stand by my "broken" assertion. Unless a "feature" is a bug thats NotWorthIt/TooHard to fix?

    Still I suppose ( grudgingly ) it's better than Oracle's "mutating table" offal. I just did not want to have
    to go back to triggers instead of DRI. I do not think your response addresses this.

    Oh well, triggers are my friend

    Regards
    AJ

    "David Portas" <org> wrote in message
    news:phx.gbl... 
    > around?
    > The error message is a feature rather than a bug.
    >
    > Without knowing more about your requirement it's difficult to advise. Maybe
    > you should drop the two foreign keys from the Refs table and put them in a
    > joining table:
    >
    > CREATE TABLE JoiningTable (someid INTEGER NOT NULL REFERENCES Refs(someid)
    > ON UPDATE CASCADE ON DELETE CASCADE, myid INTEGER NOT NULL REFERENCES
    > ForKey(myid) ON UPDATE CASCADE ON DELETE CASCADE, PRIMARY KEY (someid,myid))
    >
    > --
    > David Portas
    > ------------
    > Please reply only to the newsgroup
    > --
    >
    >[/ref]


    Andrew Guest

  3. #3

    Default Re: may cause cycles or multiple cascade paths

    BOL says "No table can appear more than once in the list of all cascading
    referential actions that result from the DELETE or UPDATE." (see Cascading
    Referential Integrity Constraints) so I guess it's a doented feature (as
    if that helps!)

    Probably overkill for this purpose, but possibly:

    CREATE TABLE Sometable (someid INTEGER PRIMARY KEY)

    CREATE TABLE Users (userid INTEGER PRIMARY KEY)

    CREATE TABLE UserModifications (someid INTEGER NOT NULL REFERENCES
    Sometable(someid) ON UPDATE CASCADE ON DELETE CASCADE, useraction CHAR(1)
    NOT NULL CHECK (useraction IN ('C','U' /* Created/Updated */ )), userid
    INTEGER NOT NULL REFERENCES Users(userid) ON UPDATE CASCADE ON DELETE
    CASCADE, moddate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY
    (someid,useraction))

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

Similar Threads

  1. Paths on Multiple Instances
    By Lupus 23 in forum Coldfusion Server Administration
    Replies: 1
    Last Post: December 16th, 05:45 PM
  2. Merge/Join multiple Paths
    By ebeard in forum Macromedia Freehand
    Replies: 12
    Last Post: March 11th, 04:01 AM
  3. Creating Accurate Multiple Paths
    By Baker535 in forum Macromedia Freehand
    Replies: 4
    Last Post: February 3rd, 09:48 AM
  4. Multiple paths to automatic handles?
    By asetyylisalisyyli in forum Macromedia Freehand
    Replies: 1
    Last Post: February 12th, 12:52 AM
  5. Replies: 4
    Last Post: October 9th, 08:21 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139