CHECK constraints accepts values that evaluate to UNKNOWN

Few days back, I wrote a post titled as SQL Server Default and Rule objects - Should not I use them now? that discussed two objects that are deprecated that can be used for enforcing the data integrity. I received a question based on it, related CHECK Constraint.

CHECK Constraint limits the values for columns based on the condition added. It can be set with a column, or it can be set for the entire record by adding it to the table. If you are adding CHECK Constraint for enforcing data integrity, you need to remember how it works.

CHECK Constraint works with any Boolean Expression that can return True, False or Unknown. If the value is False, it will be rejected and if the value if True, it will be accepted. However, if the value is Unknown, then it accepts it without rejecting. Therefore, you need to be very careful with the condition you write because, if the condition returns NULL, then it will be treated as True.

You can understand it by looking at the following code;

USE tempdb;
GO

CREATE TABLE dbo.Student
(
 StudentId int primary key
 , Name varchar(100) NOT NULL
 , Marks int NULL
 , Credit int NOT NULL
 , Constraint ck_Student_Marks_Credit CHECK (Marks + Credit > 100)
);
GO

-- This record can be inserted
INSERT INTO dbo.Student 
 VALUES (1, 'Dinesh', 60, 55);

-- This record cannot be inserted
INSERT INTO dbo.Student 
 VALUES (2, 'Yeshan', 40, 40);

-- This record CAN BE INSERTED
INSERT INTO dbo.Student 
 VALUES (3, 'Priyankara', null, 60);