Have you ever encountered a situation where a CHECK CONSTRAINT in SQL Server seemed to accept NULL values, even though you specified a rule against it? Recently, one of our blog readers reached out to us with a similar concern. After examining the script, we discovered what was causing this issue and how to prevent it. Let’s dive into the details.

First, let’s reproduce the scenario where the constraint allows NULL values in a column:

CREATE DATABASE TestDB
GO

USE TestDB
GO

CREATE TABLE TestTable (
    ID INT,
    Col1 INT,
    Col2 INT
)
GO

-- Create Constraint on Col1
ALTER TABLE TestTable ADD CONSTRAINT CK_TestTable_Col1 CHECK (Col1 > 0)
GO

-- Insert will work fine
INSERT INTO TestDB.dbo.TestTable (ID, Col1, Col2) VALUES (1, 1, 1)
GO

-- Insert will throw an error
INSERT INTO TestDB.dbo.TestTable (ID, Col1, Col2) VALUES (1, 0, 1)
GO

-- Insert will work fine with NULL
INSERT INTO TestDB.dbo.TestTable (ID, Col1, Col2) VALUES (1, NULL, 1)
GO

In this case, inserting a zero value will throw an error, but inserting NULL will not. The reason behind this behavior lies in how SQL Server evaluates constraints. When a constraint is evaluated, it checks if the condition is FALSE. However, when NULL is evaluated, it is not considered FALSE but rather UNKNOWN. As a result, the constraint allows NULL values to be inserted.

If you want to prevent NULL values from being inserted, you need to modify the constraint accordingly. Let’s take a look at an example where NULL is not allowed:

-- Add the Constraint on Col2
ALTER TABLE TestTable ADD CONSTRAINT CK_TestTable_Col2 CHECK (Col2 > 0 AND Col2 IS NOT NULL)
GO

-- Insert will throw an error
INSERT INTO TestDB.dbo.TestTable (ID, Col1, Col2) VALUES (1, 1, NULL)
GO

-- Insert will work fine
INSERT INTO TestDB.dbo.TestTable (ID, Col1, Col2) VALUES (1, 1, 1)
GO

In this case, attempting to insert NULL into Col2 will throw an error (Error 547). By including the condition “Col2 IS NOT NULL” in the constraint, we ensure that NULL values are not allowed.

Remember, when working with CHECK CONSTRAINTs in SQL Server, it’s important to understand how NULL values are evaluated. By considering this behavior, you can create constraints that enforce the desired rules for your data.

That’s all for today’s blog post. We hope you found this information helpful in understanding how CHECK CONSTRAINTs work in SQL Server. If you have any questions or comments, feel free to reach out to us.

Happy coding!