Constraints

  • Thomas Abraham

    SSChampion

    Points: 10761

    Comments posted to this topic are about the item Constraints

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

  • M&M

    SSC-Insane

    Points: 21699

    Thanks, good question.

    M&M

  • CoolCodeShare

    SSCrazy

    Points: 2584

    Cool!!!

  • Håvard

    Mr or Mrs. 500

    Points: 569

    Thanks, a very good question!

  • TomThomson

    SSC Guru

    Points: 104773

    Very good question

    I shouldn't try to think this early!

    Tom

  • This was removed by the editor as SPAM

  • derek.colley

    SSCrazy Eights

    Points: 8040

    Thought this was straightforward but sadly not

    Surprised that the NULL was not caught in the check constraint...

    More coffee required.

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • nigel.

    SSChampion

    Points: 11627

    Had already had first coffee before attempting to answer this. 😀

    Good question, thanks

  • rfr.ferrari

    SSCertifiable

    Points: 6879

    good and easy question!!!

    thanks!


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • Britt Cluff

    SSCertifiable

    Points: 5083

    Thanks for the question. I was pretty sure the check constraint would allow the NULL value.

    http://brittcluff.blogspot.com/

  • Ernie Schlangen

    SSCrazy

    Points: 2382

    From http://msdn.microsoft.com/en-us/library/ms188258.aspx:

    CHECK constraints reject values that evaluate to FALSE. Because null values evaluate to UNKNOWN, their presence in expressions may override a constraint. For example, suppose you place a constraint on an int column MyColumn specifying that MyColumn can contain only the value 10 (MyColumn = 10). If you insert the value NULL into MyColumn, the Database Engine inserts NULL and does not return an error.

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    Good and easy Question !!!

    Thx

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

  • Mattrick

    Ten Centuries

    Points: 1176

    Great question. I got it right, but only because I have encountered this behavior before, and committed it to memory.

    Thanks,

    Matt

  • Rich Mechaber

    SSChampion

    Points: 10935

    Thanks for a good question. I got it right, but I thought that perhaps only one such NULL INSERT would be allowed. After reading the BOL entry and trying it out, I see that you can INSERT many such rows.

    Odd behavior, at least to me, as it seems the "correct" interpretation of this should be "only permit the INSERT if the constraint condition evaluates to TRUE."

    Anyone here know if this is an ANSI-compliant implementation? Is this how constraints work in MySQL, Oracle, DB2? Just curious....

    Learned something new and got my point!

    Rich

  • SkyBVI

    SSCoach

    Points: 15111

    I guess the answer is wrong

    It should be 1 and 2 only

    beacuse only the constraint is violated,

    the sql statement is terminated.

    it doesn't look for other statements...

    just goes out of loop.

    Just wat i think this, bcoz i encountered this before..

    Regards,

    Sushant

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

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