Search code examples
sqlsql-serversql-server-2008-r2check-constraintscheck-constraint

Table Check Constraint allows invalid data

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

Solution

  • 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.