You can enforce integrity in particular tables or in
the relationships among tables in your design by
creating integrity constraints on specific columns.
Unique, referential, and check integrity constraints can be included as part of
the CREATE TABLE
or ALTER TABLE statements.
A check constraint can be included as part of a CREATE VIEW statement.
A unique constraint lets you eliminate duplicate key values.
To create the unique constraint, you use the PRIMARY KEY or UNIQUE
option along with the NOT NULL clause
in the CREATE TABLE
or ALTER TABLE statement.
To enforce uniqueness, ALLBASE/SQL automatically creates a unique index
on the specified key in the same DBEFileSet as the table.
A unique constraint differs from a unique index in that you do not
create it with a separate SQL statement, and you do not have to name it.
A referential constraint lets you enforce a relationship
of dependency between different keys. This
relationship implies the existence of both a
referenced table and a referencing table, which may be
the same table.
The referential relationship means that
a key value must exist in the referenced table before
a row containing that key value is inserted in the referencing table.
(However, the foreign key in the referencing table
can contain NULL values, even though the
primary or unique key in the referenced table
cannot contain NULL values.)
The referenced table
must be created with a PRIMARY KEY or UNIQUE clause in the
CREATE TABLE
or ALTER TABLE statement.
The primary or unique key is then referred to in
the FOREIGN KEY or REFERENCES clause in the
CREATE TABLE
or ALTER TABLE statement
for the referencing table.
Table check constraints additionally ensure that a specified search condition does not evaluate to false for any row of a table.
The search condition may evaluate to unknown if a column specified in the
condition contains a NULL value. A search condition is defined on columns of a table in the CREATE TABLE or ALTER TABLE statement.
A check constraint for a view is defined through the WITH
CHECK OPTION clause. This constraint ensures that no changes made through
the view violate its definition.
Such a check constraint is enforced during an insert or update to a table through the view. The check must satisfy first the view constraints and the
constraints defined on the table on which the view is based.
You can also defer constraint checking to the transaction level and set general
error checking to row level.
Constraint error checking is performed at the current general error checking
level unless constraint checking is deferred.
Deferring constraint error checking avoids constraint errors that will be
resolved by the end of the transaction. Setting general error checking to
row level avoids some logging and rollback overhead
if you are using nonarchive logging.
For complete information about integrity constraints, refer to
"Constraints, Procedures, and Rules" chapter in
the ALLBASE/SQL Reference Manual, and to the application programming guide for the language of your choice.
