With naked eyes, we often spot the evident problems but the specific details are missed many a time. Something similar happened recently. One of the blog readers sent me an email asking about a bug in how CHECK CONSTRAINT works. He suggested that check constraint accepts NULL even though the rule is specified.
After looking at the whole script, I found out what he has done and how to prevent this type of error.
Let us first reproduce the script where the constraint allows NULL value in the 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
Inserting the zero will throw an error.
Inserting NULL will not throw error.
The reality is that any constraint will prevent values that are evaluated as FALSE. When NULL is evaluated, it is not evaluated as FALSE but as UNKNOWN. Owing to the same reason, the constraint is allowing NULL to be inserted. If you want NULL not to be inserted, the constraint has to be created in such a way that NULL will not be allowed.
The following script is created on Col2 where NULL is not allowed. When NULL is attempted to inserted, it will throw Error 547 as displayed in the earlier image.
-- 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
-- Clean up
USE MASTER
GO
ALTER DATABASE TestDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE TestDB
GO
Reference: Pinal Dave (https://blog.sqlauthority.com)
