CASCADE - 1

  • Ron McCullough

    SSC Guru

    Points: 63877

    Comments posted to this topic are about the item CASCADE - 1

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • demonfox

    SSCertifiable

    Points: 6289

    All that sql statemens were just for the final show 🙂

    Nice and easy !!

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • Dineshbabu

    Hall of Fame

    Points: 3220

    I expected there will be some trickery work will be there.. But it's easy and staright forward question..

    --
    Dineshbabu
    Desire to learn new things..

  • Lokesh Vij

    SSChampion

    Points: 10836

    Dineshbabu (3/19/2013)


    I expected there will be some trickery work will be there.. But it's easy and staright forward question..

    +1

    Nice and easy. Thanks Ron!

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    Good question.

    In real life scenario, we need to take care of cascade option.

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • demonfox

    SSCertifiable

    Points: 6289

    Danny Ocean (3/20/2013)


    Good question.

    In real life scenario, we need to take care of cascade option.

    I prefer not to have delete cascade on design .. to let the error display when the constraint doesn't allow it..

    well, delete cascase could be a better option for delete based on performance , but with proper indexing , I guess, basic deletes shouldn't be too intensive process ..

    and , it keeps of mistakes off the track 🙂 that's what constraints are for ...

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    demonfox (3/20/2013)


    Danny Ocean (3/20/2013)


    Good question.

    In real life scenario, we need to take care of cascade option.

    I prefer not to have delete cascade on design .. to let the error display when the constraint doesn't allow it..

    well, delete cascase could be a better option for delete based on performance , but with proper indexing , I guess, basic deletes shouldn't be too intensive process ..

    and , it keeps of mistakes off the track 🙂 that's what constraints are for ...

    :-):-):-)

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Nice basic question Ron, thanks!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • peter.row

    SSCarpal Tunnel

    Points: 4306

    Basic question designed to seem like a trick?

    This applies equally to SQL Server 2005 and 2008 not just 2008R2 and 2012.

  • Stuart Davies

    SSCoach

    Points: 18878

    Nice and clear question - thanks Ron

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a questionThere are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan I would never join a club that would allow me as a member - Groucho Marx

  • Carlo Romagnano

    SSC-Insane

    Points: 21987

    I do not like "[OrderID] [int] NULL," in the [OrderDetail],

    so you can insert orphan records despite the constraint.

    CREATE TABLE [dbo].[OrderDetail]

    (

    [OrderDetailID] [int] NOT NULL,

    [OrderID] [int] NULL,

    CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED ([OrderDetailID] ASC)

    )

    The rest is basic!

    😀

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Simply Nice, thank you for posting.

    (i knew this before, and just fews days ago i kind of helped a person online to fix this, so it was easy recall for me)

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    peter.row (3/20/2013)


    Basic question designed to seem like a trick?

    This applies equally to SQL Server 2005 and 2008 not just 2008R2 and 2012.

    also... including our old friend SQL SERVER 2000

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • TomThomson

    SSC Guru

    Points: 104773

    It's a nice clear question with a nice unambiguous answer.

    I'm a little bothered by the statement that on delete cascade means that if rows in several tables reference a key they will all be deleted; on delete cascade applies only to a single foreign key relationship, so can't influence the action on multiple referring tables; the foreign key relationships on other referencing tables might have SET NULL or SET DEFAULT instead of CASCADE and then the rows in those tables wouln't be deleted, or might even have NO ACTION in which case the delete wouldn't happen for any rows at all. ON CASCADE only means that referring rows in this table will be deleted if deletion of the target is successful.

    Carlo Romagnano (3/20/2013)


    I do not like "[OrderID] [int] NULL," in the [OrderDetail],

    so you can insert orphan records despite the constraint.

    It's a good idea to avoid NULL if possible, of course, but sometimes in the real world all the data isn't available when something is inserted and in that case a NULL may be needed - of course it does seem unlikely that the order of which the detail is part will be unknown, but I can easily dream up a zany ordering process in which it would almost always be unknown (and on reflection I can envisage such an ordering process as srather useful in some contexts) so I can't object to a nullable column here. Anyway, you can't insert orphan rows; you can insert rows which look like orphan rows, but they aren't orphan rows because an orphan row is one that has been orphaned - it had a parent once, but the parent has disappeared; so a reference constraint with on update cascade does prevent real orphan rows, it just doesn't prevent insertion of rows that look as if they are orphan rows.

    The thing I might complain about if someone suggested actually doing it is the structured orderdetailid field; I can't tell from the table definition that the orderdetail table isn't in 1NF, but I can tell from the insertion code that it definitely is not in 1NF (and just to placate the anti-null fundamentalists, I'll point out that this remains true if NULL isn't permitted in any of the columns). And of course a consequence of that structured column being the primary key is that it can never be null, which makes it an absolute nonsense that one of the components that go to make up the structured column is allowed to be null. But none of that is what the question is about.

    Tom

  • TomThomson

    SSC Guru

    Points: 104773

    demonfox (3/20/2013)


    Danny Ocean (3/20/2013)


    Good question.

    In real life scenario, we need to take care of cascade option.

    I prefer not to have delete cascade on design .. to let the error display when the constraint doesn't allow it..

    well, delete cascase could be a better option for delete based on performance , but with proper indexing , I guess, basic deletes shouldn't be too intensive process ..

    and , it keeps of mistakes off the track 🙂 that's what constraints are for ...

    No, constraints are to ensure that the schema enforces its own integrity and that you don't have to write code to enforce it. It means that you can't make mistakes that would lead to an invalid state of the schema, not that you can't make mistakes at all.

    Avoiding ON DELETE CASCADE is usually a mistake; it requires code to be written to do something the system could do for you - and that code costs development time and testing time and integration time and may contain bugs (for example sometimes deleting some rows in the referring table that it shouldn't delete, or sometimes not deleting all the referring rows that it should delete, or deleting all the referring rows but omitting deletion of the referred row) which can have highly undesirable results. If you are worried about bugs that delete the wrong order, will increasing the code size and complexity prevent those bugs or do nothing to stop them while introducing potentilly more bugs?

    If on the other hand you are worried about someone carelessly doing a manual delete on an order row that shouldn't be deleted, why do your procedures allow manual deletes? If you need manual deletes, is forcing people to use a much more error prone manual process (find the detail rows and delete them first, then delete the order row) instead of a simple manual process (just delete the order row and let the system take care of the rest) going to make them more likely or less likely to make mistakes? If you don't want certain things to be deleted all, why does anyone have delete permissions on them in the first place? Is it possible that in eschewing use of ON UPDATE CASCADE you are trying to fix operational issues involving people, which is usually (always, as far as Iknow) not something you can do in a computer program?

    Tom

Viewing 15 posts - 1 through 15 (of 54 total)

You must be logged in to reply to this topic. Login to reply