ALLBASE/SQL Reference Manual (36216-90216)

140 Chapter4
Constraints, Procedures, and Rules
Using Integrity Constraints
If a check constraint is added to an existing table, data already in the table is verified to
ensure that the check constraint is satisfied. A constraint error occurs if the constraint is
not satisfied; the ALTER TABLE statement adding the constraint fails.
The check is also performed when the INSERT or UPDATE statement is executed. A DELETE
statement never causes a check constraint error.
The check search condition must not contain a subquery, aggregate function, TID function,
local variable, procedure parameter, dynamic parameter, current function, USER, or host
variable. The search condition expression also cannot contain a LONG column unless it is
within a long column function. When adding a new column, the columns specified in the
search condition must be defined in the same CREATE TABLE or ALTER TABLE ADD COLUMN
statement. For the ALTER TABLE ADD COLUMN statements, the check constraint can only be
specified for the column being added. When adding a constraint, columns specified in the
check constraint search condition must already exist in the table.
The search condition is a boolean expression which must not be false for a table check
constraint to be satisfied. If any value specified in the search condition expression is
NULL, the result of the expression may be the boolean unknown value rather than true or
false. The check constraint is satisfied if the result is true or unknown.
For example, consider the following check constraint:
CHECK (NumParts > 5)
If NumParts is 5, the result is false and the check is not satisfied. If NumParts is 10, the
result is true and the check constraint for this row is satisfied. If NumParts is NULL, the
result is unknown and the check constraint is also satisfied for this row.
A table check constraint can be defined at a column level or a table level. A check
constraint defined on a column is specified before the comma that ends the column
definition as shown below. A table constraint can be placed anywhere-- before, after, or
among the column descriptions. These rules apply for columns defined with either the
CREATE TABLE or ALTER TABLE statements.
For example, a column level check constraint on the Date column is defined as follows:
CREATE PUBLIC TABLE RecDB.Events
(SponsorClub CHAR(15),
Event CHAR(30),
Date DATE DEFAULT CURRENT_DATE
No comma here
(CHECK (Date >= '1990-01-01'),
Constraint Check_No_Old_Events),
Time TIME,
Coordinator CHAR(20),
FOREIGN KEY (Coordinator, SponsorClub)
REFERENCES RecDB.Members (MemberName, Club)
CONSTRAINT Events_FK)
IN RecFS;