ALLBASE/SQL Reference Manual (36216-90216)

138 Chapter4
Constraints, Procedures, and Rules
Using Integrity Constraints
constraints are not allowed. Neither UNIQUE nor PRIMARY KEY columns can contain
null values--they must be defined as NOT NULL.
The following syntax is used to define a unique constraint on an individual column or
column list at the table level:
{UNIQUE PRIMARY KEY} ( ColumnName [,...]) [CONSTRAINT ConstraintID ]
ConstraintID is the name of the constraint. It is not necessary to name the constraint. If
it is not named, ALLBASE/SQL names it SQLCON_
uniqueid , where uniqueid is a
unique string. The constraint names are maintained in the system catalog table
SYSTEM.CONSTRAINT.
A column list cannot contain a column more than once. In the example below, a constraint
is placed on a column at the table level:
CREATE PUBLIC TABLE RecDB.Clubs
(ClubName CHAR(15) NOT NULL,
UNIQUE (ClubName) CONSTRAINT ClubConstrnt)
IN RecFS;
The syntax for defining a unique constraint at the column level is part of the column
definition. NOT NULL and either UNIQUE or PRIMARY KEY are included along with the
other column parameters. In the example below, one column is defined with a unique
constraint:
CREATE PUBLIC TABLE RecDB.Clubs
(ClubName CHAR(15) NOT NULL UNIQUE CONSTRAINT ClubConstrnt)
IN RecFS;
A table defined with a PRIMARY KEY followed by a column list is shown in the section
“Examples of Integrity Constraints.”
Referential Constraints
A referential constraint requires that the value in a column or columns of the referencing
table, must either be null or match the value of a column or columns of a unique constraint
in the referenced table. To establish a referential constraint, a unique or primary key
constraint must first be defined on the referenced table's column or column list and then a
referential constraint must be defined on the referencing table's column or column list.
The Referenced Table
The referenced table must contain a unique constraint created with either a UNIQUE or
PRIMARY KEY clause on a column or column list:
CREATE PUBLIC TABLE RecDB.Clubs
(ClubName CHAR(15) NOT NULL
PRIMARY KEY CONSTRAINT Clubs_PK, -- column level constraint
ClubPhone SMALLINT,
Activity CHAR(18))
IN RecFS;
The referenced table must be created before the referencing table unless the referenced
and referencing tables are created within a CREATE SCHEMA statement or if both the tables
are created in the same transaction, the SET REFERENTIAL CONSTRAINTS DEFERRED
statement has been executed and is still in effect.