How do I... Enforce domain integrity for SQL Server data using CHECK constraints?

Even simple errors in data can result in huge problems. Luckily, you can eliminate at least some of those errors by using CHECK constraints to force users to enter data that meets specific conditions.

This article is also available as a PDF download.

You might think that simple data errors aren't terribly important, but even a small error can do a lot of damage. For instance, an incorrect ZIP code doesn't seem too serious--until a new customer's order doesn't show up. Or imagine your best salesperson's surprise when a commission check is half the expected amount. There are no shortcuts--invalid data can wreak havoc. By restricting the data that any domain, or column, can accept, developers help ensure that the data the application stores is appropriate. Enforcing domain integrity simply means that a column accepts only valid values, as dictated by the application's business rules.

The first step of enforcing domain integrity is to set the column's data type, but that's not where your work stops. You can also determine the actual values allowed in the column using constraints. A constraint is a rule that defines how SQL Server enforces data integrity. You're probably familiar with them already--PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, and CHECK are constraints you can apply to a column. In this article, we'll use the CHECK constraint to enforce business rules to protect data integrity.

Apply a simple CHECK constraint

Defining a column's data type is the first step in enforcing domain integrity. For instance, a numeric column won't accept alpha characters, a bit column won't accept anything but the values 0 and 1, and so on. Setting the correct data type will catch some data input errors, but in general, that won't enforce business rules that put further limits on data.

CHECK constraints go a step beyond a column's data type. They limit the actual values that a column can accept using a Boolean expression that determines whether data is valid. For example, you might use a CHECK constraint to ensure that commission percentages always range from 5 to 8 percent:

commission >= .05 AND commission <= .08

If you enter the value .06, the CHECK expression returns TRUE and accepts the value. On the other hand, the expression returns FALSE and rejects the value .01.

NULL values are unpredictable when evaluated by a CHECK expression that doesn't explicitly reject them. SQL Server is flexible enough to allow multiple constraints on the same column. (SQL Server will complain if you try to create a constraint that conflicts with an existing constraint or property.)

To illustrate how to limit data using a CHECK constraint, let's create one that limits an entry to only numeric digits, even though the column's data type is a character (text) data type. This is a common situation when storing ZIP code values. A character column accepts alpha characters, but ZIP code values consist of only numeric digits. Using Management Studio and SQL Server 2005 Express Edition, we'll manually create a constraint that allows a ZIP code column to accept only five numeric digits:

Our example uses a simple table with two nchar columns, ZIPCode and City. You can work with any SQL Server table you want, as long as the table has a ZIP code column.

  1. To follow our example, run the following statement to create the table:
CREATE TABLE ZIPCodesZIPCodenchar(5), City nchar(30)
  1. In Management Studio's Object Explorer, expand to the example table.
  2. Right-click Constraints and choose New Constraint.
  3. In the Check Constraints dialog box, enter the following constraint expression in the Expression field, as shown (partially) in Figure A:
ZIPCode like '[0-9] [0-9] [0-9] [0-9] [0-9]'
  1. Name the constraint CK_ZIPCodes_ZIPCode.
  2. Enter a short description, such as Restrict ZIP codes to numeric digits.
  3. Click Add and then Close.
  4. Click Save on the Standard menu.

Figure A

A CHECK constraint uses an expression to determine whether user input is valid.

You can bypass the manual process and execute a SQL statement in the following form (as long as the table already exists):

ALTER TABLE tablename ADD CONSTRAINT constraintname CHECK (expression)

In the case of our example table, execute the following statement:

ALTER TABLE dbo.ZIPCodes ADD CONSTRAINT CK_ZIPCodes_ZIPCode CHECK (ZIPCode like '[0-9] [0-9] [0-9] [0-9] [0-9]')

The [0-9] component restricts the character to any numeric digit between 0 and 9 in the specified position. Since the expression repeats the [0-9] component five times, the constraint allows only five numeric digits. The brackets ([ ]) are wildcards that allow you to specify a list of characters. (For more information on constraint expressions, see Books On Line.)

After adding the CHECK constraint, click New Query and execute the following SQL statement to add an invalid ZIP code value:

INSERT ZIPCodes (ZIPCode, City)
VALUES ('555aa', 'Smallville')

Figure B shows the results. The value 555aa contains two characters that violate the [0-9] constraint. Consequently, SQL Server rejects the entry. Without the CHECK constraint, SQL Server would accept 555aa.

Figure B

The CHECK constraint won't accept 555aa because it contains alpha characters.

Keep in mind that the example constraint can't guarantee that an accepted entry is actually a valid ZIP code. It simply guarantees that the accepted value contains only numeric digits.

Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is Mastering Microsoft SQL Server 2005 Express, with Mike Gunderloy, published by Sybex. Other collaborations with Gunderloy are Automating Microsoft Access 2003 with VBA, Upgrader's Guide to Microsoft Office System 2003, ICDL Exam Cram 2, and Absolute Beginner's Guide to Microsoft Access 2003, all published by Que. Currently, Susan volunteers as the Publications Director for Database Advisors. You can reach her at