SQL

  • Dineshbabu

    Hall of Fame

    Points: 3220

    How come you are saying Cascade is normal on update/delete. By default it will be No Action rite?

    --
    Dineshbabu
    Desire to learn new things..

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    dineshbabus (11/30/2012)


    How come you are saying Cascade is normal on update/delete. By default it will be No Action rite?

    Yes. But changing it is also normal.

    Just because the default prevents something, doesn't mean it cannot be done at all.

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

  • Dineshbabu

    Hall of Fame

    Points: 3220

    ok guyz lets come to a conclusion. Whether question is not clear or we ourselves getting confused?

    --
    Dineshbabu
    Desire to learn new things..

  • Toreador

    SSChampion

    Points: 11256

    Luckily I didn't not fail to get this wrong, but it wasn't easy due to all the double negatives.

    The correct answer to most of them is of course 'it depends' - if there are cascade deletes, triggers, etc then all of the actions are possible.

  • sipas

    Hall of Fame

    Points: 3249

    Toreador (11/30/2012)


    Luckily I didn't not fail to get this wrong, but it wasn't easy due to all the double negatives.

    The correct answer to most of them is of course 'it depends' - if there are cascade deletes, triggers, etc then all of the actions are possible.

    +1

    Do I not like this question - isn't part of the reason for enforcing referential integrity to be able to use the CASCADE option?

  • DugyC

    Hall of Fame

    Points: 3804

    As with other posters... I got this wrong because the author did not take into account CASCADE options.

    If he meant for CASCADE off as default he should have stated that.

    Taken the question literally, the correct answers are 4 and 5.

    Don't worry about my point, I've just taken one here instead... 😛

    _____________________________________________________________________
    [font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

  • Mighty

    SSCrazy Eights

    Points: 8785

    This would have been a basic question, if it would have had two create table statements to accompany it. Now it was unclear, and left much room for assumptions, which should be avoided.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Mighty (11/30/2012)


    This would have been a basic question, if it would have had two create table statements to accompany it. Now it was unclear, and left much room for assumptions, which should be avoided.

    +1000

    DDL statements would have solved a lot of the ambiguity.

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

  • call.copse

    SSCoach

    Points: 17181

    Mighty (11/30/2012)


    This would have been a basic question, if it would have had two create table statements to accompany it. Now it was unclear, and left much room for assumptions, which should be avoided.

    I can see the room for ambiguity, and a little more guidance might have been helpful - perhaps saying assuming field not nullable, no cascade, etc. but I didn't think it was too bad as I could see what he was driving at pretty quickly.

    Of course if I hadn't meant what I thought then I would have been getting right mardy too 😉

  • Thomas Abraham

    SSChampion

    Points: 10761

    +57 - Trying to not be as grandious as Koen's later +1000 🙂

    Knew when I saw the question I had very little chance to get it right, because none of the options mentioned CASCADE.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • (Bob Brown)

    SSCrazy

    Points: 2705

    Was very cautious in answering this QotD because I knew there were some land mines in it. Thanks, it made me review very closely the BOL on referential integrity.

  • Rich Mechaber

    SSChampion

    Points: 10935

    Answer says "We can't delete rows from the primary table if the secondary table contains referential rows. " but the submitter's own linked explanation correctly includes what happens with ON DELETE CASCADE:

    Delete Rules

    Referential integrity rules for deletion define what happens when an attempt is made to delete a row from the parent table. Three options exist:

    Restricted Delete — the deletion of the primary key row is not allowed if a foreign key value exists.

    Neutralizing Delete — all foreign key values equal to the primary key value of the row being deleted are set to null.

    Cascading Delete — all foreign key rows with a value equal to the primary key of the row about to be deleted are deleted as well.

    Rich

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Since there is already so much criticism for the question and the answer options, let me point out that this question at least is a whole lot better than the reference is cites.

    I have no idea why the question author did not look for a better reference. Like, for instance, Books Online. Instead, he chose to use an article that is 14 years old, and that is also inaccurate!


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Bob Razumich

    SSCrazy

    Points: 2036

    Hugo Kornelis (11/30/2012)


    ...he chose to use an article that is 14 years old, and that is also inaccurate!

    I noticed that too. Because I didn't have a clue, the article I used to get lucky and get it correct was this one:

    http://msdn.microsoft.com/en-us/library/aa902684(v=sql.80).aspx

    Granted, it is also old (from October, 2000) but it has more detail. I based my answers on the results for the NO ACTION (restrict) section.

  • ronmoses@gmail.com

    SSCarpal Tunnel

    Points: 4480

    I only got it wrong due to confusion over the meaning of the phrase "break the relationship". I took it to mean "sever the relationship" rather than "violate the relationship." Clearly two very different things, one of which can be done, the other not.

    ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

Viewing 15 posts - 16 through 30 (of 61 total)

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