sql - Table Check Constraint allows invalid data -

when create check constraint using test script below, data violates constraint still allowed table, , constraint still shown trusted.

i realize check constraint not check nulls correctly (it includes column = null instead of column null), still expect sql server not allow 'asdf', '3', or null values, because check condition evaluates false against these values. can explain why check constraint allowing following values: null, '3', 'asdf'?

if change constraint condition (checkme null or checkme = '1' or checkme = '2'), works 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 table

insert dbo.testcheck(checkme) values ('1'),('2'),(null),('3'); go 

add constraint, check existing data should checked. expect both null , '3' violate check, somehow succeds.

alter table dbo.testcheck check  add constraint ck_testcheck  check (checkme = null or checkme = '1' or checkme = '2'); go 

attempt insert invalid data after check constraint has been add... succeeds?

insert dbo.testcheck(checkme) values('asdf'); go 

show table contains invalid data, , constraint marked trusted, meaning data in table has been verified against constraint

select *     --this same logic in check constraint, shows 3 rows not pass     , checkconstraintlogic = case when (checkme = null or checkme = '1' or checkme = '2') 'pass' else 'fail' end 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 sys.check_constraints k  k.parent_object_id = object_id('dbo.testcheck') order 1; go 

script output:

check constraints different clause in check allows modification if expression evaluates null marker. clarify: clause filters out rows expression evaluates false or null marker; check constraint filters out modifications evaluate false.

the expression have written evaluates null, since has comparison null inside it. change = null is null.

additionally, different usages of check constraints have different defaults check/nocheck, should in habit of specifying it.

try following.

alter table dbo.testcheck check  check add constraint ck_testcheck  check (checkme null or checkme = '1' or checkme = '2'); 

edit: re "but i'm wondering why example evaluates true", you've worked out, example evaluates not true null marker, check constraint allows. sorry, should have explained bit better.