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 ...
Get SQL in a Nutshell, 2nd 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.