SQL in a Nutshell, 2nd Edition

CHECK Constraints

CHECK constraints allow you to perform comparison operations to ensure that values match specific conditions that you set out. The syntax for a check constraint is very similar to the general syntax for constraints:

[CONSTRAINT] [constraint_name] CHECK (search_conditions)
[constraint_deferment] [deferment_timing]

Other elements of the constraint are introduced earlier in this section. The following element is unique to the CHECK constraint:

search_conditions

Specifies one or more search conditions that constrain the values inserted into the column or table, using one or more expressions and a predicate. Multiple search conditions may be applied to a column in a single check constraint using the AND and OR operators. (Think of a WHERE clause.)

A check constraint is considered matched when the search conditions evaluate to TRUE or UNKNOWN. Check constraints are limited to Boolean operations (e.g., =, >=, <=, or <>), though they may include any SQL2003 predicates such as IN or LIKE. Check constraints may be appended to one another (when checking a single column) using the AND and OR operators. Some other rules about CHECK constraints:

• A column or table may have one or more CHECK constraints.

• A search condition cannot contain aggregate functions, except in a subquery.

• A search condition cannot use non-deterministic functions or subqueries.

• A check constraint can only reference like objects. So if a check constraint is declared on a global temporary table, it cannot then ...