title: Table Check Constraint allows invalid data
body: <p>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.  </p>
  
  <p>I realize that the check constraint does not check for NULLs correctly (it includes <code>column = null</code> instead of <code>column IS null</code>), 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'?  </p>
  
  <p>If I change the constraint condition to <code>(checkMe is null or checkMe = '1' or checkMe = '2')</code>, then it works as expected.</p>
  
  <p>SQL Server version: Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)</p>
  
  <pre><code>CREATE TABLE dbo.testCheck(checkMe varchar(50));
  go
  </code></pre>
  
  <p>insert data into the table</p>
  
  <pre><code>INSERT INTO dbo.testCheck(checkMe)
  VALUES ('1'),('2'),(NULL),('3');
  GO
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>ALTER TABLE dbo.testCheck WITH CHECK 
  ADD CONSTRAINT ck_testCheck 
  CHECK (checkMe = null or checkMe = '1' or checkMe = '2');
  GO
  </code></pre>
  
  <p>Attempt to insert invalid data after check constraint has been add... this succeeds?</p>
  
  <pre><code>INSERT INTO dbo.testCheck(checkMe) VALUES('ASDF');
  GO
  </code></pre>
  
  <p>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</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Script output: </p>
  
  <p><img src="https://i.stack.imgur.com/ZHUD6.png" alt="output screenshot"></p>