I recently noticed something I thought unusual with a check constraint. One of the conditions of interest was
- len(someCol) > 5
Here's a pseudo line of the full condition set:
- ((Flag='Y' AND (String1='str1' OR String1='str2') AND len(String2)>(5)) OR (Flag='N' AND String1 IS NULL AND String2 IS NULL))
So you can see that it either needs to meet condition 1 OR condition 2, condition 1 being the one with the length function.
There were two things that I had to get to grips with:
1) I do not think that means what you think it means...
Null Values aren't dealt with the same by check constraints as they would as they would be in conditions in your regular code. Check constraints pass a check, not when a condition is true, but when it is not false. The difference is subtle but remember that SQL operates of three-valued logic. There is True, False and also Unknown (i.e. NULL).
So if something is NULL, it is also "NOT FALSE" and will pass the check. Be wary!!! Here is some script to illustrate this.
set nocount on; --Let's get rid of those pesky row counts
declare @Flag char(1) = 'Y', @String1 varchar(10) = 'str1', @String2 varchar(10) = null; --NULL is what we're mostly interested in here.
--Let's check the consition is some "regular" script. Does it fail or pass?
if ((@Flag='Y' AND (@String1='str1' OR @String1='str2') AND len(@String2)>(5)) OR (@Flag='N' AND @String1 IS NULL AND @String2 IS NULL))print 'Check test condition passed. String2 = ' + isnull(@String2,'NULL');else print 'Check test condition failed. String2 = ' + isnull(@String2,'NULL');
--We'll need a table with a check constraint to test an actual check constraint
if OBJECT_ID('WhatTheHell','U') is not null drop table WhatTheHell;create table WhatTheHell( Flag char(1), String1 varchar(10), String2 varchar(10));alter table WhatTheHelladd constraint QuePasa check ((Flag='Y' AND (String1='str1' OR String1='str2') AND len(String2)>(5)) OR (Flag='N' AND String1 IS NULL AND String2 IS NULL));
--Can we pass the check and insert into the table with the same values?
insert into WhatTheHell (Flag,String1,String2) values (@Flag,@String1,@String2);
if @@ROWCOUNT = 1 --i.e. If the insert worked
print 'Check actual condition passed. String2 = ' + isnull(@String2,'NULL');else print 'Check actual condition failed. String2 = ' + isnull(@String2,'NULL');
Feel free to play around with the values, but running this as it is you'll see the following results from your testing:
Check test condition failed. String2 = NULL
Check actual condition passed. String2 = NULL
As you can see the results actually PASS the check constraint. So be careful and always consider NULLs if your columns allow it.
2) The refactor factor
Have a look at definition of the check constraint created above using the following:
select definition from sys.check_constraints where name = 'QuePasa';Can you spot the difference? It now looks like this:
([Flag]='Y' AND ([String1]='str1' OR [String1]='str2') AND len([String2])>(5) OR [Flag]='N' AND [String1] IS NULL AND [String2] IS NULL)And before it essentially looked like this:
((Flag='Y' AND (String1='str1' OR String1='str2') AND len(String2)>(5)) OR (Flag='N' AND String1 IS NULL AND String2 IS NULL))It removed some parentheses. This is because SQL Server deemed these parentheses to be redundant, and they are according to the operator precedence. AND takes precedence over OR.
Personally, I'll still use the "redundant" parentheses in my code as I find it easier to read and you don't always have to remember the precedence.
No longer mystified :)
Sources:
On check constraints: https://technet.microsoft.com/en-us/library/ms188258(v=sql.105).aspxOn operator precedence: https://msdn.microsoft.com/en-us/library/ms190276.aspx
