Post reply

Constraints

  • October 16, 2011 at 7:08 am

    #1395058

    Thanks, good question.

    M&M

  • October 17, 2011 at 12:02 am

    #1395139

    Cool!!!

  • October 17, 2011 at 1:45 am

    #1395166

    Thanks, a very good question!

  • October 17, 2011 at 1:49 am

    #1395167

    Very good question

    I shouldn't try to think this early!

    Tom

  • October 17, 2011 at 2:51 am

    #1395189

    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.

  • October 17, 2011 at 3:31 am

    #1395201

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

    Good question, thanks

    Nigel
    How to post questions

  • October 17, 2011 at 4:36 am

    #1395223

    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!

  • October 17, 2011 at 5:44 am

    #1395245

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

    http://brittcluff.blogspot.com/

  • October 17, 2011 at 5:48 am

    #1395248

    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.

  • October 17, 2011 at 6:03 am

    #1395252

    Good and easy Question !!!

    Thx

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

  • October 17, 2011 at 6:30 am

    #1395267

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

    Thanks,

    Matt

  • October 17, 2011 at 6:55 am

    #1395284

    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

  • October 17, 2011 at 7:07 am

    #1395293

    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

    Regards
    Sushant Kumar

  • October 17, 2011 at 7:20 am

    #1395303

    rmechaber (10/17/2011)

    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

    It seems that Oracle, at least, works the same way. From http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses002.htm: (bold added)

    A check constraint lets you specify a condition that each row in the table must satisfy. To satisfy the constraint, each row in the table must make the condition either TRUE or unknown (due to a null). When Oracle evaluates a check constraint condition for a particular row, any column names in the condition refer to the column values in that row.

  • October 17, 2011 at 7:24 am

    #1395305

    Good question and actually makes sense if you think about it. The check constraint is checking for values. From BOL

    CHECK constraints enforce domain integrity by limiting the values that are accepted by a column.

    The logic here is that the check constraint is flexible enough to allow for nulls (which the column does allow). All the check constraint evaluates is that IF there is a value it should comply to the rules established in the check constraint.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

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