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);

SQL Server Default and Rule objects - Should not I use them now?

In order to make sure that the database contains high quality data, we ensure data integrity with our data that refers to the consistency and accuracy of data stored. There are different types of data integrity that can be enforced at different levels of solutions. Among these types, we have three types called Domain, Entity and Referential Integrity that are specific to database level for enforcing data integrity.

For enforcing Domain Integrity, SQL Server has given two types of objects called Default and Rule. We have been using these objects for handling Domain Integrity but now it is not recommended to use these for enforcing Domain Integrity.

Let's try to understand what these objects first and see the usage. Default object can be used for creating an object that holds a default value and it can be bound to a column of the table. Rule is same as Default and it creates an object for maintaining rules for columns. See below code as an example.

USE tempdb;
GO

-- creating default object
CREATE DEFAULT CreditLimitDefault AS 10000;
GO

-- creating a sample table
CREATE TABLE dbo.Customer
(
 CustomerId int PRIMARY KEY
 , LastName varchar(50) NOT NULL
 , CreditLimit decimal(16,4) NOT NULL
);
GO

-- Binding the default to a column
-- The object can be bound to many tables
EXEC sp_bindefault 'CreditLimitDefault', 'dbo.Customer.CreditLimit';
GO

-- creating rule object
CREATE RULE CreditLimitRule AS @CreditLimit > 9000;
GO

-- Binding the rule to a column
-- The object can be bound to many tables
EXEC sp_bindrule 'CreditLimitRule', 'dbo.Customer.CreditLimit';

As you see, above code creates two objects, CreditLimitDefault and CreditLimitRule that are Default and Rule objects. These objects can be assigned to any column in any table.

As I mentioned above, it is not recommended to use them now as they are deprecated. It is recommended to use Default and Check constraints instead.

Read more on CREATE DEFAULT at: https://msdn.microsoft.com/en-us/library/ms173565.aspx

Read more on CREATE RULE at: https://msdn.microsoft.com/en-us/library/ms188064.aspx