SQL 2005+

SQL Server Check Constraints

The twenty-fourth part of the SQL Server Programming Fundamentals tutorial describes check constraints. These constraints add business rules to database tables to prevent invalid information being stored and damaging the domain integrity of the database.

What is a Check Constraint?

We have already mentioned the concept of database constraints in this tutorial. Constraints in general apply rules to a database to prevent invalid information from being stored. There are various types of constraint including primary keys, foreign keys and unique constraints. A check constraint applies a business rule to a column or table. It checks information as new rows are inserted or when existing rows are updated. If the constraint's rule is broken because the data is invalid, the update fails.

A check constraint includes a conditional expression, or predicate, that returns a Boolean value. When an insert or update is attempted, the expression is evaluated. If the resultant value is true, the change is permitted. If false, an error occurs. As an example, consider a table that contains pricing information for contract renewals. The business that owns the database may allow a free-of-charge contract but never a negative price. The constraint in this case would include the predicate, "ContractValue >= 0". A negative contract value would fail this constraint and be disallowed.

Some developers advise against the use of check constraints, arguing that the business logic of an application should be retained within the business logic layer of an application. However, in such a scenario, it becomes possible for invalid data to be added to the database. This can happen because there are bugs in the business logic or because data is updated via a different route. For example, when a database is used by multiple applications with differing logic. A check constraint prevents this problem.

Limitations

There are several limitations to consider when creating check constraints. The first of these is that Boolean expressions in SQL Server can produce three results, not two. These are true, false and unknown. The unknown value will occur, for example, when comparing NULL values. If a constraint check returns unknown, the insert or update will be permitted. You should carefully consider this situation when creating constraints, especially those with complex predicates that include a combination of checks.

There can be problems with constraints that include data type conversion. You should avoid this type of constraint wherever possible. Note that this includes implicit data type conversion.

If you include a user-defined function within a check constraint, you can perform checks that use values from other tables or from other rows in the same table. For example, you could create a function that returns the number of rows in the table and include this in a check constraint that specifies that there must be at least one row in the table. However, this check constraint will not be tested when deleting rows from the table so will not provide the desired effect. Such a business rule must be enforced using a database trigger instead.

Creating Check Constraints

In this instalment of the tutorial we will create check constraints against the tables in the JoBS tutorial database. This database has been created, modified and populated throughout the course of these articles. If you do not have an up-to-date copy of the database, you can create one by downloading and running the script from the link at the top of this page.