January 27, 2016 at 9:59 pm
Comments posted to this topic are about the item CHECKing Values
January 27, 2016 at 11:09 pm
This was removed by the editor as SPAM
January 28, 2016 at 12:44 am
Nice question. 2 rows with current identity value 3.
Igor Micev,My blog: www.igormicev.com
January 28, 2016 at 2:52 am
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.
January 28, 2016 at 4:39 am
Ouch.
Yes, too easy, so didn't read the question properly.
January 28, 2016 at 5:11 am
It looked so simple that I found myself looking for the trick. Thanks, Steve.
January 28, 2016 at 7:29 am
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
January 28, 2016 at 7:45 am
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 CheckTestidmyval
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.
January 28, 2016 at 7:57 am
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
January 28, 2016 at 8:04 am
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.
January 28, 2016 at 8:28 am
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!)
January 28, 2016 at 8:37 am
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.
January 28, 2016 at 8:46 am
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.
January 28, 2016 at 8:46 am
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.
January 28, 2016 at 9:22 am
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