Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    65

    Unanswered: Whaaat? No cascading cascades??

    I may have misinterpreted the error I just got...but.. is it impossible in SQL Server to have a cascade delete FK constraint that leads to a table which has another cascade delete FK?? What's a workaround? Triggers?

  2. #2
    Join Date
    Jan 2004
    Posts
    65
    OK this makes no sense...

    I have a table cp_campaigns linked to cp_kits which is linked to cp_kit_items. (they're linked as you'd expect.. cp_kits has a campaign_id column that's a FK to the PK in cp_campaigns, cp_kit_items has a FK kit_id which points back to cp_kits).

    Now, in the relationship between cp_kits and cp_kit_items, I have a cascade delete.. so if you delete a kit, the kit items are also wiped out.

    But when I try to set a cascade delete between cp_campaigns and cp_kits (delete all kits when the corresponding campaign is deleted), I get this error (upon trying to save the diagram):

    'cp_campaigns' table saved successfully
    'cp_kits' table
    - Unable to create relationship 'FK_cp_kits_cp_campaigns'.
    ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing FOREIGN KEY constraint 'FK_cp_kits_cp_campaigns' on table 'cp_kits' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
    [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.

    Now here's the really weird part. I have a cp_elements table (which contains a campaign_id FK linked back to cp_campaigns), and a cp_orderables table which links back to cp_elements. Pretty much the same kind of relationship as the kits above, right? Well, VERY oddly, I am able to do cascade deletes on BOTH the relationship between campaigns & elements, AND elements & orderables!

    What the heck is going on? Is the error above caused by something else?? SQL errors are so cryptic sometimes...

    Thanks for any help.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hmm, are you sure you named them correctly? i just created a three-level hierarchy of tables with cascades, and no grief --
    Code:
    create table test_div
    ( id smallint not null primary key
    , divdescr char(3)
    )
    create table test_dept
    ( id smallint not null primary key
    , divid smallint not null 
         foreign key references test_div(id)
            on delete cascade
    , deptdescr char(3)
    )
    create table test_sect
    ( id smallint not null primary key
    , deptid smallint not null 
         foreign key references test_dept(id)
            on delete cascade
    , sectdescr char(3)
    )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2004
    Posts
    65
    What do you mean named correctly?

    And what does that error mean?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    named correctly means you aren't trying to reference the wrong table -- which, upon reflection, would be hard to do if you're doing this through some sort of gui interface (you mentioned a diagram)

    as far as the error goes, i'm not sure -- would have to see your entire set of tables and relationships to know more
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I suspect that you have a relationships established between these table that you are not aware of. You can get this error if a relationship exists between cp_campaigns and cp_kit_items, or if two of your tables are involved in a cascading relationship with another table. (Sounds perverted, doesn't it?) Try scripting out the three tables and see what shows up.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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