Using Table Check Constraints [ ALLBASE/SQL Release F.0 Application Programming Bulletin for MPE/iX ] MPE/iX 5.0 Documentation
ALLBASE/SQL Release F.0 Application Programming Bulletin for MPE/iX
Using Table Check Constraints
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:
CHECK (SearchCondition)
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:
ColumnA > 10
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.
MPE/iX 5.0 Documentation