Check Constraints in SQL Server

You use check constraints to limit the range of possible values in a column or to enforce specific patterns for data. Check constraints must evaluate to a Boolean True/False and cannot reference columns in another table.

You can create check constraints at two different levels

·         Column-level check constraints are applied only to the column and cannot reference data in another other column.

·         Table-level check constraints can reference any column within a table but cannot reference columns in other tables.

The most basic constraints compares the data in a column to a specified value---- for example CHECK  salary <= 50000. You can create any number of  check constraints separated by AND, OR, or NOT to create more complex conditions.

 You can also use check constraints to enforce patterns within data. Using check constraints this way, you might enforce the pattern that an employee ID is required to start with an uppercase letter, followed by three digits and then six additional letters. Another example is to require an         e-mail address to contain , in order, any number of character or digits, an @symbol , a number of characters or digits, a period (.), and then either three characters or two characters with a period (.) plus two more characters.

 The wildcard characters for pattern matching are underscore (_), which designates one value that can be a character, number or special character; and a percent symbol (%), which designates any number of a characters, numbers, or special characters. For example,

a table –level check constraints to validate an e-mail address might look like this :

CONSTRAINT chkEmail CHECK (Email like ‘%@% [a-z] [a-z]’ or Email like ‘%@%. [a-z] [a-z].[a-z] [a-z]’

A column-level check constraints for the Employee ID looks like this:

CHECK (EmployeeID like ‘[(A-Z] [0-9] [0-9] [0-9] [A-Z] [A-Z] [A-Z] [A-Z] [A-Z] [A-Z] ‘)