Post reply

SQL

  • November 30, 2012 at 1:21 pm

    #1564559

    Well, I thought this was an easy question, until I read the comments.

  • November 30, 2012 at 4:34 pm

    #1564600

    Rather late in getting around to attempt to answer this particular QOD and

    Correct answers: .. 30% (164)

    Incorrect answers: 70% (376)

    Total attempts: 540

    [Rant]

    IMHO the discussion of CASCADE options has certainly increased the benefit of the QOD for those who have read the discussion (comments), other than that, this particular QOD would be a GREAT example of what is NOT an acceptable submittal.

    [/Rant]

    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]

  • December 3, 2012 at 7:11 am

    #1564868

    Koen Verbeeck (11/30/2012)

    Got it wrong because you can update or delete rows in the primary tables if you specify ON CASCADE options.

    Next time do more homework please.

    I completely concur

    On CASCADE handles the first "correct answer". We can delete a row from the primary table if there is ON CASCADE settings in place to handle it.

    Can delete a row from primary table if there are related rows in secondary table,

    And if the Foreign Key column is Nullable, you can certainly insert rows that are not related to the primary table.

    Can insert a new row in secondary table if there are not related rows in primary table


    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • December 3, 2012 at 7:13 am

    #1564870

    dineshbabus (11/30/2012)

    I think he is expecting us to think in normal scenario.. We shud not think beyond that..

    My normal scenarios include Nullable foreign keys and DELETE CASCADE options.


    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • December 3, 2012 at 7:14 am

    #1564871

    Koen Verbeeck (11/30/2012)

    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.

    And columns when they are created unless NOT NULL is specified on a default install of SQL are nullable. So Nullable Foreign keys are the default 🙂


    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • December 3, 2012 at 7:18 am

    #1564873

    Hugo Kornelis (11/30/2012)

    Tom, I also have been trying to find when cascading referential integrity was introduced. Like you, I could not find anything conclusive, but I might very well have been SQL 2000.

    I'm pretty sure I remember using it in SQL 7.0, which was my first version of SQL I used in production. Spent a good 6 years using it too until the ERP we were using got upgraded to a version that supported SQL 2000. 6 months before the release of SQL 2005.

    Guess I'm getting old enough to confuse 2000 with 7. Perhaps it was triggers I used to mimic it. In either case it's been almost 8 years since I've seen 7.0 so who am I to argue? 🙂


    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • December 3, 2012 at 7:53 am

    #1564891

    Good question if only I had read the "not", oh well.

    Thanks,

    Lon

  • December 3, 2012 at 1:47 pm

    #1565036

    Thanks for the question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • December 4, 2012 at 3:52 am

    #1565201

    Saramilla.. Author oru paavamanu.. leave it.. Avanu vendi njan maapu chodikunnu.

  • December 4, 2012 at 6:48 am

    #1565290

    Jamsheer (12/4/2012)

    Saramilla.. Author oru paavamanu.. leave it.. Avanu vendi njan maapu chodikunnu.

    Isn't the very point of posting on a forum so that you can share your thoughts with others? No idea what language this is in, but I doubt most people on this forum know it.

    Hakim Ali
    www.sqlzen.com

  • December 4, 2012 at 6:51 am

    #1565294

    hakim.ali (12/4/2012)

    Jamsheer (12/4/2012)

    Saramilla.. Author oru paavamanu.. leave it.. Avanu vendi njan maapu chodikunnu.

    Isn't the very point of posting on a forum so that you can share your thoughts with others? No idea what language this is in, but I doubt most people on this forum know it.

    I'm with you Akim.

  • December 7, 2012 at 5:42 am

    #1566575

    I think I just got confused by the double negative aspect of the question -which statement is NOT true and the statement are full of Can and Cant - should have taken a bit longer to think them all through! 😀

  • December 27, 2012 at 9:04 am

    #1571902

    Just went wrong due to the NULL value possibilty. 🙁

    Some projects have already failed with such a lack of precision ... :w00t:

  • March 20, 2014 at 4:37 pm

    #1698914

    +1, but the answers "depends" on Cascades and nullability. This i completely correct (even if I don't like it)

    CREATE TABLE dbo.a

    (id INT PRIMARY KEY, NAMN VARCHAR(19))

    CREATE TABLE dbo.b

    (

    id INT NULL, value int

    )

    ALTER TABLE dbo.b WITH CHECK ADD CONSTRAINT fk FOREIGN KEY (id) REFERENCES dbo.a(id)

    INSERT INTO dbo.b (id, value) VALUES(NULL,1)

    By allowing null on the foreign key column, you'll end up with records without relation to parent table and it's generally a bad practice.

    /Håkan Winther
    MCITP:Database Developer 2008
    MCTS: SQL Server 2008, Implementation and Maintenance
    MCSE: Data Platform

Viewing 14 posts - 46 through 59 (of 59 total)

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