Professional Web Applications Themes

Multiple Cascade Flaw in SQL Server 2K - Microsoft SQL / MS SQL Server

I keep running into this, and every time I do, it bugs me more. Example tables: Map (PK MapUID) Sector (PK SectorUID, FK MapUID) Boundary (PK BoundaryUID, FK MapUID) SectorBoundary (PK SectorBoundaryUID, FK SectorUID, FK BoundaryUID) To avoid confusion, this represents: "A map is divided into sectors, and each sector is connected to adjacent sectors by boundary lines. Sectors have an arbitrary number of boundary lines. Maps have an arbitrary number of sectors." Because each boundary divides exactly two sectors, I could have set it up this way instead: Boundary (PK BoundaryUID, FK Sector1UID, FK Sector2UID) ....but that makes the ...

  1. #1

    Default Multiple Cascade Flaw in SQL Server 2K

    I keep running into this, and every time I do, it bugs me more.

    Example tables:
    Map (PK MapUID)
    Sector (PK SectorUID, FK MapUID)
    Boundary (PK BoundaryUID, FK MapUID)
    SectorBoundary (PK SectorBoundaryUID, FK SectorUID, FK BoundaryUID)

    To avoid confusion, this represents: "A map is divided into sectors,
    and each sector is connected to adjacent sectors by boundary lines.
    Sectors have an arbitrary number of boundary lines. Maps have an
    arbitrary number of sectors."

    Because each boundary divides exactly two sectors, I could have set it
    up this way instead: Boundary (PK BoundaryUID, FK Sector1UID, FK
    Sector2UID)
    ....but that makes the problem even worse.

    Anyway, if you buy into referential integrity, (which I do) it is
    critical that the following two things both happen:

    1) Whenever a sector ID changes or is deleted, any related entry in
    the SectorBoundary junction table is also changed or deleted.

    2) Whenever a Boundary ID changes or is deleted, any related entry in
    the SectorBoundary junction table is also changed or deleted.

    3) Whenever a map ID changes or is deleted, any related entry in the
    Sector AND Boundary table is also changed or deleted.

    The above is critical IMHO because: Relying on programmers to not make
    mistakes in their code is ludicrous. Example: Programmer forgets to do
    the delete cascade when they remove a sector. Now you've got a bunch
    of boundary lines orphaned. Users complain about updates performing
    slowly and then you notice your table has a few million rows...

    I could use a lot of other examples...
    (Person, Friendship) - Even worse, because it's a PersonPerson
    junction table.
    (Publisher, Book, Author, BookAuthor) - If you remove or update the
    author, the book, or the publisher, the junction table better update
    in each case.

    It seems to me the limitation here (since this is not a cyclic graph)
    is that SQL Server 2k can't decide which update to perform first if
    both occur at the same time.

    If you delete a map, it deletes all the sectors and boundaries of the
    map. When it gets to SectorBoundary, SQL Server perhaps wouldn't know
    whether to perform the sector cascade first, or perform the boundary
    cascade first.

    Personally, I don't understand the issue. It's obviously not deleting
    both the Sector and Boundary table in the same instant. Hence,
    whichever it deletes first should end up deleting everything in
    SectorBoundary, and there's no conflict.?

    Anyway, this could be solved by giving a index to the FK relationships
    or a checkbox indicating you don't care what order it happens in.

    Don't get me wrong, I'm thankful to MS for adding cascades. But it
    seems like a simple enhancement would have made it so much more
    useful. Can anyone tell me why the aforementioned enhancement wouldn't
    work, and if/why there's a performance reason not to do it?

    I think multiple cascades are an integral part of relationships. Any
    time you have a junction table in a well-constrained data model,
    you're likely to run into this problem. In the meantime, I've resigned
    myself to using triggers on updates and deletes (which is a
    performance concern) and waiting for MS to provide a better solution.

    If anyone knows a different way to abstract the aforementioned
    relationship into a table diagram without losing any of the cascades,
    or a more efficient workaround than update triggers, please post!
    Travis Guest

  2. #2

    Default Re: Multiple Cascade Flaw in SQL Server 2K


    "Travis" <com> wrote in message
    news:google.com... 

    No it makes it better, and it's better design because it removes an unneeded
    table.

    But you will need to use instead of triggers for your cascading operations.

    Like this
    cascade updates (and all updates to primary keys) are best avoided so I
    left them out.



    drop table boundary
    drop table sector
    drop table map
    go

    create table map( id int primary key)
    create table sector(id int primary key,
    map_id int not null references map(id))
    create table boundary(id int primary key,
    sector_left int not null references sector(id),
    sector_right int not null references sector(id))



    go

    create trigger map_delete on map instead of delete
    as
    set nocount on

    delete from sector
    where map_id in (select id from deleted)

    delete from map
    where id in (select id from deleted)

    go

    create trigger sector_delete on sector instead of delete
    as
    set nocount on

    delete from boundary
    where sector_right in (select id from deleted)
    or sector_left in (select id from deleted)

    delete from sector
    where id in (select id from deleted)



    Then




    insert into map(id) values(1)
    insert into sector (id,map_id) values (1,1)
    insert into sector (id,map_id) values (2,1)
    insert into sector (id,map_id) values (3,1)
    insert into boundary(id,sector_left, sector_right) values (1,1,2)
    insert into boundary(id,sector_left, sector_right) values (1,2,3)


    delete from sector where id = 2

    select * from boundary



    David Guest

  3. #3

    Default Re: Multiple Cascade Flaw in SQL Server 2K

    Thanks for the response, David.

    I've been operating under the impression for quite some time that
    cascading referential integrity is more efficient than instead of
    triggers. However, when looking into it, the only thing on MSDN I
    could find was the following blurb:

    "Triggers can cascade changes through related tables in the database;
    however, these changes can be executed more efficiently through
    cascading referential integrity constraints."

    Quite frankly, I haven't found anything more to support this. I
    suppose now I'll have to do some comparisons of execution plans. :)

    However, with all things the same, I prefer the one-click effort of
    cascades rather than writing two triggers for every table.

    Anyway, I occasionally end up using triggers instead... for the
    reasons mentioned in my previous post. I'd still like to hear from
    Microsoft why they couldn't or shouldn't allow multiple cascades in
    acyclic trees in future versions of SQL Server.

    Travis
    Travis Guest

  4. #4

    Default Re: Multiple Cascade Flaw in SQL Server 2K

    >> I've been operating under the impression for quite some time that
    cascading referential integrity is more efficient than instead of
    triggers. <<

    Well, that is an implementation issue. However, DRI is standard and
    portable while triggers are not; DRI predicates can be used by the
    optimizer in a smart SQL engine and triggers cannot. The problem is
    that SQL Server is missing some of the power that exists in the DRI
    actions in Standard SQL, so you are limited to how you build your
    references.

    But they'll fix <<insert name of problem here>> in Yukon :)

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  5. #5

    Default Re: Multiple Cascade Flaw in SQL Server 2K


    "Travis" <com> wrote in message
    news:google.com... 

    With normal triggers you can't have both a cascading trigger and DRI.
    The key thing with "instead of" triggers is that you don't have to sacrifice
    DRI.

    I don't think there's any theoretical difficulty with implementing cascading
    DRI in these situations. Oracle supports it.

    Design-wise I think the reason for not implemeting cascading DRI here is:
    - complicates the implementation logic for the cascading operation.
    - relatively few designs require cascading dri in brancing or looping
    situations.
    - "instead of" triggers provide a good workaround for any shortcomings in
    cascading DRI.

    You'll find that Microsoft is very conservative with adding features to
    SQLServer.
    They usually only add functionality that is well understood and has proven
    its usefulness in other RDBMS systems.
    The result is that the RDBMS is mostly free of once gee-whiz features which
    have since become deadwood.

    Because of this, however, MS has a bad habit of putting in features in a
    half-assed way. The worst example I can think of is "Deferred Name
    Resolution", which created as many problems as it solved. In retrospect
    they should have bit the bullet and implemented a robust solution to the
    problem of compiled code referencing changing schema objects. But other
    "cautious" implementations like partitioned views, and IMO cascading DRI,
    succeed in meeting 80% of the need with 20% of the work.

    David





    David Guest

  6. #6

    Default Re: Multiple Cascade Flaw in SQL Server 2K

    It really helps if you post DDL instead of some personal pseudo-code. I
    also changed the data element names – that “UID” is a red flag that
    someone is thinking in OO terms and has a ed up relational model; I
    don’t know, but does cartography actually use the term “MapUID”?
    Otherwise, if UID is a postfix describing HOW something is represented
    instead of WHAT it is in the data model, then it is time to read
    ISO-11179 or a book on data modeling.

    Table names should be plural or collective nouns since they are sets and
    not scalars. But the specs are good.
     [/ref]
    adjacent sectors by boundary lines. Sectors have an arbitrary number of
    boundary lines. Maps have an arbitrary number of sectors.. each boundary
    divides exactly two sectors, <<

    Let’s start with maps since everything flows out of being on a map.

    CREATE TABLE Maps
    (map_nbr INTEGER NOT NULL PRIMARY KEY,
    ..);

    Sectors are subsets of maps, but are sector numbers unique? No DDL, so
    we have to guess. No surprises in the design here – standard two column
    key with PK-FK to the containing data element (see orders and order
    details, etc.)

    CREATE TABLE Sectors
    (map_nbr INTEGER NOT NULL
    REFERENCES Maps(map_nbr)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    sector_nbr INTEGER NOT NULL,
    ..
    PRIMARY KEY (map_nbr, sector_nbr));

    Boundaries are trickier and a lot of specs have to be assumed. The
    sectors that touch are on the same map. The sectors that touch are
    different. If a sector disappears, then the boundaries it had
    disappear. A boundary appears once in the table, and thus the table is
    all-key and therefore in 5NF.

    CREATE TABLE Boundaries
    (map_nbr INTEGER NOT NULL,
    sector_nbr_1 INTEGER NOT NULL,
    sector_nbr_2 INTEGER NOT NULL,
    FOREIGN KEY (map_nbr, sector_nbr_1)
    REFERENCES Sectors(map_nbr, sector_nbr)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    FOREIGN KEY (map_nbr, sector_nbr_2)
    REFERENCES Sectors(map_nbr, sector_nbr)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    CHECK (sector_nbr_1 <> sector_nbr_2),
    PRIMARY KEY (map_nbr, sector_nbr_1, sector_nbr_2));

    Does that express what you wanted?

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  7. #7

    Default Re: Multiple Cascade Flaw in SQL Server 2K

    David Browne (davidbaxterbrowne no potted com) writes: 

    Hear! Hear!



    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  8. #8

    Default Re: Multiple Cascade Flaw in SQL Server 2K

    Actually, Joe, I think I was right in posting pseudocode. The
    specifics of a data model in this discussion are unimportant, and most
    likely would have led to just as much tangential criticism as my
    pseudocode did. Anyone can right-click in Query yzer and
    copy-paste their DDL. Doing so would have turned those 4 lines of
    intentionally oversimplified model into 2 pages, not to mention
    possibly being a conflict re: intellectual property.

    Besides, I don't think that would have helped me get answers to my
    actual questions, which were: "Is there a better way than Instead of
    triggers?", and "Why would allowing multiple cascades in an acyclical
    graph be a problem?"

    I wasn't looking for help with my data model or my naming conventions,
    but I suppose it was inevitable that I got it. :) Since I haven't
    gotten any satisfying answers to my original question, and since we're
    on the subject...

    What does it matter what I name my non-semantic primary keys as long
    as I'm consistent? I actually had someone yell at me once for not
    naming them <tablename>SID and for not including the name of the table
    in every field in that table... and it's just silly, if you ask me.
    With regards to table names, I go back and forth. I used to use
    plurals, but for junction tables and in many queries, it actually
    detracts from the readability, if you ask me.

    For example: SectorBoundary does in fact describe a row in that table.
    SectorsBoundaries does not. SectorBoundaries describes the table, but
    leads to inconsistency in naming conventions, and I'd rather describe
    a row. Most people (inc. Microsoft) seem to use the following
    convention: They name the tables Sectors, Boundaries and
    SectorBoundary. Again, that feels inconsistent to me. In my opinion,
    it really doesn't matter. Maybe it would be nice if SQL allowed the
    following syntax: "insert sectorboundary ..." "delete sectorboundaries
    where ..."

    Nah. ;)

    PS I just reread the previous paragraph and saw I had written "inc
    Microsoft".
    If you're a MMORPG player, you know how scary a concept that is!
    Travis Guest

Similar Threads

  1. Dropdownlist Cascade
    By JimmyB4B in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 1
    Last Post: December 18th, 04:50 AM
  2. possible to DELETE CASCADE?
    By Miles Keaton in forum PostgreSQL / PGSQL
    Replies: 5
    Last Post: December 30th, 10:42 PM
  3. may cause cycles or multiple cascade paths
    By Andrew in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 19th, 09:06 AM
  4. Cascade Deletes
    By msnews.microsoft.com in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 15th, 01:07 AM

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