BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
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.

Creating Check Constraints Using SQL Server Management Studio

Check constraints can be created using the tools within SQL Server Management Studio (SSMS). We will use the Check Constraints dialog box, pictured below, to create our first check constraint. The predicate will prevent contracts from being created with a negative contract value.

To begin, locate the Contracts table in the Object Explorer. Right-click the name of the table and choose "Design" from the context-sensitive menu that appears. The table designer will be displayed. To add a constraint, right-click any column in the table designer and select "Check Constraints..." from the menu. Alternatively, choose this option from the Table Designer menu or click the Manage Check Constraints toolbar button. The Check Constraints dialog box will be displayed.

SQL Server Check Constraints dialog box

There are several options that may be completed in the dialog box:

  • Expression. This option is the most important. This is where you must create the predicate that will be enforced by the constraint. To enforce that the contract value must always be zero or greater, enter "ContractValue>=0" into the field.
  • Name. As with the other constraints that we have created, check constraints should be given a unique name. Conventionally this will have the prefix "CK_". For the new constraint, use the name "CK_Contracts_ValueIsPositive".
  • Description. If you wish, you can describe the purpose of the constraint in the Description field.
  • Check Existing Data on Creation or Re-Enabling. When a check constraint is first created there may already be data in the table that does not fulfil the rules being enforced. Similarly, if a constraint is disabled the data may have become invalid before it is re-enabled. If this option is set to Yes and data that breaks the rule exists, it will not be possible to create or re-enable the constraint. If the value is set to No, the constraint will be created but the invalid data will remain in the table. The value you choose will depend upon the situation. In this case, leave the default value of Yes.
  • Enforce For Inserts And Updates. When set to No, the check constraint is disabled and the creation of invalid data is permitted. This is useful when you wish to temporarily disable a constraint. Set the value to Yes.
  • Enforce For Replication. When you have more than one database and the table is replicated, changes made in one copy of the table are duplicated in the other. As the check constraint should have been checked and passed when the modification was initially made, you may not want to repeat the check when an update is made by the replication engine. Set this option to No.

To create the new check constraint, accept the new changes and close the dialog box. Close the table designer and save the changes. The check constraint will now appear in the Constraints section of the table's branch of the Object Explorer tree. If it doesn't, refresh the constraints list.

We can test the constraint by attempting to insert invalid data into the Contracts table. Execute the following Transact-SQL (T-SQL) statement, which tries to create a negative contract value. You should receive an error and the table will remain unmodified.

INSERT INTO Contracts
    (CustomerAddressId, ContractValue)
VALUES
    (21, -1)
20 April 2009