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 CHAR(25), sal MONEY,
CONSTRAINT check_salary CHECK (sal > 0));
The
expression (see
Expressions in SQL) specified in the check constraint must be a Boolean expression.
To specify a check constraint for a group of columns, specify the check constraint at the table level (as opposed to 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 CHAR(10),
location CHAR(10),
budget MONEY,
expenses MONEY,
CONSTRAINT check_amount CHECK (budget > 0 AND
expenses <= budget));
Note: The way nullability is specified for a column determines whether you can change the nullability of the column. If CHECK...NOT NULL is specified for a column, use the ALTER TABLE...DROP CONSTRAINT statement to remove the constraint (because the column is created as nullable-that is, with an additional byte for the null indicator-and the check constraint is used to prevent nulls from being inserted). However, if NOT NULL is specified (as opposed to a CHECK...is NOT NULL CONSTRAINT), the constraint cannot be removed using the ALTER TABLE...DROP CONSTRAINT statement because the column was created without the additional byte for the null indicator, and the additional byte cannot be added.
Check constraints cannot include the following:
• Subselects
• Set functions (aggregate functions)
• Dynamic parameters
• Host language variables
Column-level check constraints cannot reference other columns.