CHECK Constraints in SQL Server

In this article, we will talk about CHECK constraints. We will see how to add CHECK constraints to SQL Server table columns and discuss the pitfalls you may encounter when using this type of SQL Server constraints.

CHECK constraint basics

CHECK constraints are simply conditional statements (predicates that return TRUE or FALSE) that refer to table columns for maintaining data integrity. When one inserts data into a column or several columns in a single row, CHECK constraints come into action. They evaluate the data to be inserted. In case the data does not meet the condition specified in the CHECK constraint, the insertion fails.

Consider the following example:

It is required to set a restriction to the Salary column so that it stores only positive values not exceeding $150,000. The conditional statement will look as follows: (Salary >= 0 and Salary <= 150000). While attempting to insert negative values, the predicate will result in FALSE, and the insertion will fail.

It is possible to add a CHECK constraint to a single or multiple columns. Adding a multi-column CHECK constraint can be implemented at the table level.

Working with CHECK constraints

How to Create CHECK Constraints in SSMS

1. In Object Explorer, navigate to a required table.

2. Right-click the Constraints folder and then click New Constraint…

3. In the right pane of the Check Constraints dialog box, click Expression and then click the ellipsis button.

   

4. Type in a CHECK constraint expression into the text field of the Check Constraint Expression dialog box. For example, to allow only seven-digit zip codes in a Zip column, the expression can look as follows:

   

In the Table Designer section, you can set up the rules to enforce the constraint.

CHECK Constraint on CREATE TABLE

Consider the following example:

It is required to create a table that stores data about bank customers and fill it with test data. The table will include the following columns: Customer Id, First Name, Last Name, Status, Phone, City, State, and Zip.

While developing the table, we must take into consideration the following facts:

1. The basic ZIP format consists of five numerical digits.

2. The standard American telephone number is ten digits, such as (555) 555-1234

3. Two-letter abbreviations are used to represent the political divisions of the United States for postal addresses, data processing, general abbreviations, and other purposes.

The task is to provide data consistency for the table. It is required to forbid the insertion of 12-digits phone numbers and 6-digits zips etc. To do this, SQL Server allows us to add one or more CHECK constraints for each table column.

In the previous section, we have examined the one way of creating a CHECK constraint in SSMS. Now, we will discuss how to create the constraint with the help of T-SQL.

The following script shows how to create a CHECK constraint on the Zip column:

CREATE TABLE Customers
(
 Customer_Id tinyint NOT NULL,
 [First Name] varchar(50),
 [Last Name] varchar(50),
 Status varchar(50),
 Phone tinyint,
 Address varchar(50),
 State varchar(50),
 Zip tinyint,
 Email varchar(50),
 [Credit Limit] INT NULL,
 CONSTRAINT CK_Zip CHECK (Zip LIKE REPLICATE ('[0-9]', 5)) --Check Constraint Condition
)

Now, let’s see what we get when trying to insert a 6-digit value into the Zip column:

INSERT INTO dbo.Customers (Customer_Id, [First Name], [Last Name], Status, Phone, Address, State, Zip, Email)
SELECT 1, 'James', 'Madison', 'Mr', 555-555-1234, 'Madison street, 12', 'LA', 123456, NULL
GO

Insertion fails, and SQL Server displays the following prevention:

So far, so good.

CASE expression in CHECK constraint

Assume that the bank has a business rule to set the credit limit for the residents of the Louisiana state to be under $150,000. We will implement this requirement by adding a CHECK constraint to the Credit Limit column:

ALTER TABLE dbo.Customers
ADD CONSTRAINT CK_Credit_Limit
CHECK (State='LA' AND [Credit Limit] <= 150000)
GO

INSERT INTO Customers (Customer_Id, Name, Status, Phone, State, Zip, Email, [Credit Limit])
VALUES (1, 'James Black', 'Mr', 5558787, 'LA', 46853, '[email protected]', 120000);
GO

INSERT INTO Customers (Customer_Id, Name, Status, Phone, State, Zip, Email, [Credit Limit])
VALUES (2, 'Mark Spencer', 'Mr', 3332244, 'NY', 23487, '[email protected]', 200000);
GO

As we execute the above statement, we get the following error:

The INSERT statement conflicted with the CHECK constraint. What went wrong?

Let’s take a closer look at the query. Notice that the CHECK constraint only allows the ‘LA’ values for the State column. At the same time, values in the Credit column must not exceed 150000.

Similarly, the CHECK constraint would not allow writing other state codes in the column.

Thus, we need to modify the condition. According to the business logic, the bank provides $150000 of the credit limit for Louisiana residents. At the same time, this value may vary for other residents.

To implement this case, we will use the CASE clause inside the CHECK constraint:

ALTER TABLE dbo.Customers
ADD CONSTRAINT CK_Credit_Limit
CHECK (CASE WHEN State='LA' AND [Credit Limit] <= 150000 THEN 1 ELSE 0 END = 1)
GO

This expression completely meets the business logic.

NULL values in CHECK constraint

The bank divides its customers into segments. The Status column contains the data that determine whether a client is VIP or regular. The maximum amount of the credit limit for the regular customers is $200,000. The VIPs may draw upon $500,000.

The CHECK constraint may look as follows:

ALTER TABLE dbo.Customers
ADD CONSTRAINT CK_Status_Credit_Limit
CHECK (Status = 'VIP' OR Status = 'Regular')
GO

Note that the CHECK constraint allows inserting NULLs into the State column (provided that there is no NOT NULL constraint explicitly defined). The CHECK constraint evaluates the values and returns TRUE or FALSE. It evaluates NULL as UNKNOWN. Therefore, NULLs will not cause errors. This is contrary to predicates in WHERE clauses in SELECT or UPDATE statements.

CHECK and NOCHECK

From time to time, business logic changes. It causes database object modifications. Imagine a country extends the zip-code base and adds 6-digit values.

The old 5-digit values will no longer be assigned to the areas. However, they are still valid for existing ones. Thus, the CHECK constraint must take into account the existing data in the old format, and validate data in the new format.

The NOCHECK clause resolves this issue:

ALTER TABLE Customers WITH NOCHECK
ADD CONSTRAINT CK_Zip_Code
CHECK (Zip LIKE REPLICATE('[0-9]', 6));
GO

The following insertion is successful:

INSERT INTO Customers (Customer_Id, Name, Status, Phone, State, Zip, Email, [Credit Limit])
VALUES (102, 'Jake Harrison', 'VIP', 555-555-1234, 'NY', 123456, '[email protected]', 100000);
GO

When trying to insert a five-digit zip, the engine throws the error:

DBCC CHECKCONSTRAINTS

SQL Server provides DBCC CHECKCONSTRAINTS to search for data that doesn’t match constraints.

If there is a database integrity issue, run DBCC CHECKCONSTRAINTS for the entire database to make sure there are no issues.

Note that this command affects the performance. Therefore, it should not be running on a scheduled basis.

It is possible to run DBCC CHECKCONSTRAINTS for a single constraint, a table, or the entire database.

Comparing to other check commands, DBCC CHECKCONSTRAINTS takes substantial time to complete and consumes system resources. Unlike other commands, CHECKCONSTRAINTS does not utilize a database snapshot.

Conclusion

CHECK constraints provide the mechanism for evaluating data before insertion. CHECK constraints can refer to a single column or multiple table columns.

Constraints are simply predicates that result in TRUE, FALSE, or UNKNOWN. In case NULL is inserted in a table, a constraint is not violated.