CHECKing Values

  • Comments posted to this topic are about the item CHECKing Values

  • This was removed by the editor as SPAM

  • Nice question. 2 rows with current identity value 3.

    Igor Micev,My blog: www.igormicev.com

  • I disagree with explanation:

    However NULLs are not restricted as the value could be less than 10

    CHECK evaluates only true (accepted) or false (rejected) expression not UNKNOWN (accepted).

    From BOL:

    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.

  • Ouch.

    Yes, too easy, so didn't read the question properly.

  • It looked so simple that I found myself looking for the trick. Thanks, Steve.

  • Carlo Romagnano (1/28/2016)


    I disagree with explanation:

    However NULLs are not restricted as the value could be less than 10

    CHECK evaluates only true (accepted) or false (rejected) expression not UNKNOWN (accepted).

    From BOL:

    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.

    +1, After running the code in the example (did you check it, Steve?). I have this:

    select * from CheckTest

    idmyval

    11

    2NULL

    Gerald Britton, Pluralsight courses

  • g.britton (1/28/2016)


    Carlo Romagnano (1/28/2016)


    I disagree with explanation:

    However NULLs are not restricted as the value could be less than 10

    CHECK evaluates only true (accepted) or false (rejected) expression not UNKNOWN (accepted).

    From BOL:

    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.

    +1, After running the code in the example (did you check it, Steve?). I have this:

    select * from CheckTest

    idmyval

    11

    2NULL

    I don't understand what all the fuzz is about. Your output proves that the question is correct. And the explanations, though using different wording, also coincide.

    First note that a NULL value does not mean unknown, it means that the value is missing (which, in some cases, can be a huge difference). However, testing logical expressions with a missing value is problematic, so ANSI-SQL replaces standard boolean logic with three-values logic, where logical expressions such as "myval < 10" evaluate to "true" for values up to 9, to "false" for values 10 and up, and to "unknown" when the value for myval is missing (NULL). So in plain English: when the value for myval is missing, SQL Server does not know whether the constraint is violated or not. And this means (again as defined in ANSI-SQL) that the row is not rejected.

    You could explain this is giving the row the benefit of the doubt. Or you could choose a more practical justification saying that in addition with your choice of defining the column as NULL or NOT NULL, this choice gives you more options than any other choice would have.

    Anyway -- the BOL quote explains it in technical implementation-related terms. The explanation given by Steve is the same but in more functional terms: the value is unknown, it could be < 10, so the row is admitted.


    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/

  • Hugo Kornelis (1/28/2016)

    I don't understand what all the fuzz is about.

    Here come da fuzz!

    Your output proves that the question is correct. And the explanations, though using different wording, also coincide.

    quite right. The explanation is a little ambiguous, but just a little

    Gerald Britton, Pluralsight courses

  • Anyway -- the BOL quote explains it in technical implementation-related terms. The explanation given by Steve is the same but in more functional terms: the value is unknown, it could be < 10, so the row is admitted.

    If the column is NULL the constraint CHECK is skipped.

  • Carlo Romagnano (1/28/2016)

    If the column is NULL the constraint CHECK is skipped.

    It might be skipped by the SQL engine, I don't know. But logically it's not. It's applied, the outcome is Null, so the value is allowed. If the constraint were defined as

    constraint CheckTest_MyVal_LT_10 check( IsNull(myval,11) < 10)

    then nulls would not be allowed (though you'd be better off just defining the column as NOT NULL!)

  • I'll admit my wording is simple. Perhaps semantics, but the check constraint is evaluated, not skipped, but the evaluation isn't false, so the row is allowed.

    The value is ambigious, as Hugo noted, which means the value could be >10, = 10, > 10, or anything else. Hence, it doesn't fail.

  • No matter what the wording of the explanation is, the point of the question (CHECK constraints) is a good one. It speaks to the topic of good design, which is always a valid discussion.

  • Steve Jones - SSC Editor (1/28/2016)


    The value is ambigious, as Hugo noted

    Not ambiguous. Missing.

    Pedantic, I know, but there are situations where the distinction is relevant.


    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/

  • I liked it. Thanks, Steve!

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

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