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, ...
Get SQL in a Nutshell, 3rd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.