Why is my check constraint not stopping this null insert?

Question

Can anyone explain why the third insert (labeled Query Data) in the below code is allowed by SQL Server?

As far as I can tell, the check constraint should only allow:

• Code is null and System is null.
• Code is not null and System is 1.

My first thought was ANSI NULLS, but setting them on or off made no difference.

This is a simplified example of a larger problem we found in our application (System was checked against a list of numbers - IN(1, 2, etc.)). We replaced this check with a foreign key (instead of IN)and a new check constraint that allowed either, both null or both not null; doing that prevented the third insert.

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

Answer 1

Welcome to SQL's wonderful three valued logic. As you may or may not be aware, the result of any standard comparison to null is not TRUE, or FALSE, but UNKNOWN.

In a WHERE clause, the entire clause has to evaluate as TRUE.

In a CHECK constraint, the entire constraint must evaluate as not FALSE.

So, we have:

([Code] IS NULL AND [System] IS NULL)   --Both null
OR
([Code] IS NOT NULL AND [System] = 1)   --Both not null ????

Which becomes (for query data):

(FALSE AND TRUE)
OR
(TRUE AND UNKNOWN)

And any operator with an UNKNOWN on one side or the other evaluates as UNKNOWN, so the overall result is UNKNOWN. Which isn't FALSE, and so evaluating the check constraint is successful.

---

If you want System not to be null, it's clearest to me if you add that as an additional explicit requirement.

([Code] IS NULL AND [System] IS NULL)   --Both null
OR
([Code] IS NOT NULL AND [System] IS NOT NULL AND [System] = 1)   --Both not null ????

---

It may seem a tad odd the way that this is defined, but it is consistent with the way that other constraints work - e.g. a foreign key constraint may have nullable columns, and if any of those columns are null, there doesn't have to be a matching row in the referenced table.

Answer 2

The result of evaluating the current constraint for the values 123, NULL is Undefined.

• ([Code] IS NULL AND [System] IS NULL) evaluates to False
• ([Code] IS NOT NULL AND [System] IN (1, 2, 3)) evaluates to Undefined

Result is Undefined

Check Constraint

CHECK constraints reject values that evaluate to FALSE. Because null values evaluate to UNKNOWN, their presence in expressions may override a constraint.

You should change your check for [System] IN (1, 2, 3) to ISNULL([System], 0) IN (1, 2, 3).

Your check constraint then becomes

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 ISNULL([System], 0) IN (1, 2, 3))   --Both not null ????
)