Check constraints check data against a defined expression in an INSERT or UPDATE statement. You can define check constraints on any column of a table or on a view. For information about view constraints, see the section "Defining and Dropping View Constraints" later in this document. Use table check constraints when you want to test a column against a condition you define rather than against data in the database. This applies only to table checks since they cannot contain subqueries. (Views using WITH CHECK OPTION can contain subqueries and are discussed in a separate section below.)
Table check constraints can be defined at the table level or the column level. At the table level, the constraint can reference multiple columns. At the column level, the constraint can reference only the column on which it is defined. The following syntax applies to both table and column level check constraints:
The search condition is an expression which must not evaluate to false. If digits and NULLs are combined in an expression, the result is the unknown boolean value. A true or unknown value satisfies a table check constraint.
In the following search condition, for example, when ColumnA equals 15, the search condition is satisfied because the result of the expression is true:
When ColumnA equals NULL, the search condition is also satisfied because the result of this same expression is unknown. However, when ColumnA equals 5, the search condition is false and a check constraint error would occur (unless constraint checking is currently deferred).
Note that their are restrictions on the search condition of
a table check constraint. The "SQL Commands" chapter of the ALLBASE/SQL Reference Manual lists these restrictions along with complete syntax for the CREATE TABLE and ALTER TABLE statements.
