Check Constraint
To create conditions that a particular column or set of columns must fulfill, specify a check constraint using the CHECK option. For example, to ensure that salaries are positive numbers:
create table emps (name character(25), sal decimal
check (sal > 0));
The
expression (see
Expressions) specified in the check constraint must be a Boolean expression.
To specify a check constraint for a group of columns, the check constraint must be specified at the table level (rather than specifying check constraints for individual columns).
The following example of a table-level check constraint ensures that each department has a budget and that expenses do not exceed the budget:
create table dept (dname character(10),
location character(10),
budget decimal,
expenses decimal,
check (budget > 0 and expenses <= budget));
Check constraints cannot include the following:
• Subqueries
• Set functions (aggregate functions)
• Dynamic parameters
• Host language variables
Column-level check constraints cannot refer to other columns.