title: Why is my check constraint not stopping this null insert?
body: <p>Can anyone explain why the third insert (labeled <strong>Query Data</strong>) in the below code is allowed by SQL Server?</p>
  
  <p>As far as I can tell, the check constraint should only allow:</p>
  
  <ul>
  <li><code>Code</code> is null and <code>System</code> is null.</li>
  <li><code>Code</code> is not null and <code>System</code> is <code>1</code>.</li>
  </ul>
  
  <p>My first thought was <code>ANSI NULLS</code>, but setting them <code>on</code> or <code>off</code> made no difference.</p>
  
  <p>This is a simplified example of a larger problem we found in our application (System was checked against a list of numbers - <code>IN(1, 2, etc.)</code>). We replaced this check with a foreign key (instead of <code>IN</code>)and a new check constraint that allowed either, both null or both not null; doing that prevented the third insert.</p>
  
  <pre><code>IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK_TestCheck]') AND parent_object_id = OBJECT_ID(N'[dbo].[TestCheck]'))
      ALTER TABLE [dbo].[TestCheck] DROP CONSTRAINT [CK_TestCheck]
  GO
  
  IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestCheck]') AND type in (N'U'))
      DROP TABLE [dbo].[TestCheck]
  GO
  
  SET ANSI_NULLS ON
  GO
  
  CREATE TABLE TestCheck(
      [Id] [int] IDENTITY(1,1) NOT NULL,
      [Code] [varchar](50) NULL,
      [System] [tinyint] NULL,
      PRIMARY KEY CLUSTERED ([Id] ASC))
  GO
  
  ALTER TABLE [dbo].[TestCheck] WITH CHECK ADD CONSTRAINT [CK_TestCheck] CHECK
  (
      ([Code] IS NULL AND [System] IS NULL)   --Both null
      OR
      ([Code] IS NOT NULL AND [System] = 1)   --Both not null ????
  )
  GO
  
  ALTER TABLE [dbo].[TestCheck] CHECK CONSTRAINT [CK_TestCheck]
  GO
  
  --Good Data
  insert TestCheck (Code, [System]) Values(null, null);
  insert TestCheck (Code, [System]) Values('123', 1);
  
  --Query Data
  insert TestCheck (Code, [System]) Values('123', null);
  
  --Bad data stopped
  insert TestCheck (Code, [System]) Values(null, 1);
  insert TestCheck (Code, [System]) Values('123', 4);
  
  select * from TestCheck
  Where
      case when
      (
          ([Code] IS NULL AND [System] IS NULL)           --Both null
          OR
          ([Code] IS NOT NULL AND [System] in (1, 2, 3))  --Both not null ????
      )
      then 0 else 1 end
       = 1
  </code></pre>