SQL in a Nutshell, 3rd 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]

Most of the elements of the check constraint were introduced earlier in this section. The following element is unique to this 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 predicate, such as IN or LIKE. Check constraints may be appended to one another (when checking a single column) using the AND and OR operators. Here are 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 nondeterministic functions or subqueries.

• A check constraint can only reference like objects. That is, if a check constraint is declared on a global temporary table, ...