In SQL Server you will find that you need to keep control of the data that gets entered into tables. One way to do this is to add check constraints. What are check constraints?

Check constraints control the values that are put in a “field” (the intersection of a column and a row). Check constraints only look at one row at a time. Contrast “unique” constraints. For a particular column, unique constraints say that you can only have a value occur one time. no more. No duplicate values are allowed.

Check constraints are often used to enforce business rules. For example they can limit the range of salaries entered into a Salary column.

At this site we have a bit more information on constraints at the post called SQL Database Design Introduction.

Contrast Foreign Key Constraints

The Microsoft Technet website says this: CHECK constraints are similar to FOREIGN KEY constraints in that they control the values that are put in a column. The difference is in how they determine which values are valid: FOREIGN KEY constraints obtain the list of valid values from another table, and CHECK constraints determine the valid values from a logical expression.

• You can apply multiple CHECK constraints to a single column.
• You can also apply a single CHECK constraint to multiple columns by creating it at the table level.

Below is some code that adds a constraint. Note that your type what you are allowing in the column, not what you don’t want in a column.

alter table Examples
add constraint chkExactly4Chars check (LEN(Exactly4Chars) = 4)

What are some examples of constraints? We can provide an acceptable ranges of values for an integer. Can we use expressions similar to regular expressions? Can we even use a list of valid values stored in another table?

Not Necessarily for New Rows

Check constraints are not necessarily for new rows. We will cover this in Part 2.