ALLBASE/SQL Reference Manual (36216-90216)

Chapter 4 139
Constraints, Procedures, and Rules
Using Integrity Constraints
The Referencing Table
A referential constraint is placed on columns which are dependent on other columns (in
the referenced table). You can create a referential constraint at either the table level or the
column level. Referencing columns need not be NOT NULL.
The following syntax is used to define a referential constraint at the table level in the
CREATE TABLE statement for a referencing table:
FOREIGN KEY(
FKColumnName [,...])
REFERENCES
RefTableName [( RefColumnName [,...])] [CONSTRAINT ConstraintID ]
FOREIGN KEY identifies a referencing column or column list. REFERENCES identifies
the referenced table and referenced column list. The order and number of referencing
columns in the FOREIGN KEY clause must be the same as that of the referenced columns
in the REFERENCES clause. The referenced table cannot be a view.
The syntax for defining a referential constraint at the column level for a referencing
column is shown here:
REFERENCES
RefTableName [( RefColumnName )] [CONSTRAINT ConstraintID ]
Only one
RefColumnName is possible.
Note in the following example that the table's column definitions and table level
constraints can be in any order within the parentheses and are separated from each other
with commas:
CREATE PUBLIC TABLE RecDB.Members
(MemberName CHAR(20) NOT NULL,
column definition
Club CHAR(15) NOT NULL,
MemberPhone SMALLINT,
FOREIGN KEY (Club)
table level
REFERENCES Clubs (ClubName)) referential constraint
IN RecFS;
If the REFERENCES clause does not specify a RefColumnName , then the table definition
referenced must contain a unique constraint that specifies PRIMARY KEY. The primary
key column list is the implicit
RefColumnName list. It must have the appropriate number of
columns.
The owner of the table containing referencing columns must have the REFERENCES
authority on referenced columns, have OWNER authority on the referenced table, or have
DBA authority, for the duration of the referential constraint.
Check Constraints
A check constraint specifies a condition which must be upheld for an insert or update to be
successfully performed on a table or view. A table check constraint must not be false for
any row of the table on which it is defined. A view check constraint must be true for the
condition in the SELECT statement that defines the view.
A table check constraint is defined in the CREATE TABLE or ALTER TABLE statement with
the following syntax:
CHECK(
SearchCondition ) [ CONSTRAINT ConstraintID ]