When I create a check constraint using the test script below, then data which violates the constraint is still allowed into the table, and the constraint is still shown as trusted.
I realize that the check constraint does not check for NULLs correctly (it includes
column = null instead of
column IS null), but I would still expect that SQL Server would not allow the 'ASDF', '3', or NULL values, because the check condition evaluates as false against these values. Can someone explain why this check constraint is allowing the following values: NULL, '3', 'ASDF'?
If I change the constraint condition to
(checkMe is null or checkMe = '1' or checkMe = '2'), then it works as expected.
SQL Server version: Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
CREATE TABLE dbo.testCheck(checkMe varchar(50));
insert data into the table
INSERT INTO dbo.testCheck(checkMe)
add constraint, with check so that existing data should be checked. I would expect that both the NULL and '3' violate this check, but it somehow succeds.
ALTER TABLE dbo.testCheck WITH CHECK
ADD CONSTRAINT ck_testCheck
CHECK (checkMe = null or checkMe = '1' or checkMe = '2');
Attempt to insert invalid data after check constraint has been add... this succeeds?
INSERT INTO dbo.testCheck(checkMe) VALUES('ASDF');
Show the table contains invalid data, and that this constraint is marked as trusted, meaning all data in the table has been verified against the constraint
--this is the same logic as in the check constraint, shows 3 rows that do not pass
, checkConstraintLogic = case when (checkMe = null or checkMe = '1' or checkMe = '2') then 'PASS' else 'FAIL' end
SELECT parentObject = isnull(OBJECT_SCHEMA_NAME(k.parent_object_id) + '.', '') + OBJECT_NAME(k.parent_object_id)
, k.name, k.is_not_trusted
FROM sys.check_constraints k
WHERE k.parent_object_id = object_id('dbo.testCheck')
ORDER BY 1;
Check constraints are different from a WHERE clause in that CHECK allows a modification if the expression evaluates to a null marker. TO clarify: A WHERE clause filters out rows for which the expression evaluates to false or a null marker; a Check constraint only filters out modifications that evaluate to false.
The expression you have written always evaluates to null, since it has a comparison to null inside it. Change
= null to
Additionally, different usages of Check constraints have different defaults for CHECK/NOCHECK, so you should get in the habit of always specifying it.
Try the following.
ALTER TABLE dbo.testCheck WITH CHECK WITH CHECK ADD CONSTRAINT ck_testCheck CHECK (checkMe IS null or checkMe = '1' or checkMe = '2');
Edit: Re "but I'm wondering why this example evaluates to True", as you've worked out, your example evaluates not to true but to a null marker, which the Check constraint allows. Sorry, I should have explained it a bit better.
As specified here:
CHECK constraints reject values that evaluate to FALSE.
In other words:
A CHECK constraint returns TRUE when the condition it is checking is not FALSE for any row in the table.
In your case, the expression
(checkMe is null or checkMe = '1' or checkMe = '2') evaluates to NULL (aka Unknown), and the CHECK constraint returns TRUE ( as the condition it is checking is not FALSE)
A good example from the same source:
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.