Table Check Constraint allows invalid data

Question

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));
go

insert data into the table

INSERT INTO dbo.testCheck(checkMe)
VALUES ('1'),('2'),(NULL),('3');
GO

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');
GO

Attempt to insert invalid data after check constraint has been add... this succeeds?

INSERT INTO dbo.testCheck(checkMe) VALUES('ASDF');
GO

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

SELECT *
    --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
FROM dbo.testCheck;
go

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;
GO

Script output:

output screenshot

Also here is a SQL Fiddle if that is easier for anyone to view: http://sqlfiddle.com/#!3/69dcc/2 — BateTech, Apr 10 '14 at 21:06
You can not use `=` to compare something with `NULL`. You *have* to use `IS NULL` — , Apr 10 '14 at 21:29
@a_horse_with_no_name You *can* compare to NULL in SQL Server using `=`, it just returns Unknown as the result. I understand how nulls work within normal T-SQL. This was a constraint that was incorrectly setup in an app a long time ago, and I just did not know why in this case the CHECK constraint evaluates to a different result than a normal SQL statement would. Normally (Unknown OR False OR False) evaluates to a False result in SQL Server conditions, but in this case with Check constraints it evaluates to True. — BateTech, Apr 10 '14 at 21:47

Greenstone Walker · Accepted Answer · 2014-04-10T22:57:46.583

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 is null.

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.

I understand what the correct usage should be (and is stated in the OP), but I'm wondering *why* this example evaluates to True. Say for the value '3', that would evaulate: ('3' = null OR '3' = '1' OR '3' = '2'), which would be: (NULL/Unknown OR False OR False), which would end up as Unknown. So I guess that Check constraints work differently than other T-SQL handling of NULLs and treat a Null/Unknown result as valid? — BateTech, Apr 10 '14 at 21:31
Ah, I just confirmed your answer in the BOL, which states: "A CHECK constraint returns TRUE when the condition it is checking is not FALSE for any row in the table". Thanks! — BateTech, Apr 10 '14 at 21:33

bjnr · Answer 2 · 2014-04-10T21:40:07.887

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.

Table Check Constraint allows invalid data

2 Answers2