ALLBASE/SQL Reference Manual (36216-90216)

Chapter 4 137
Constraints, Procedures, and Rules
Using Integrity Constraints
4 Constraints, Procedures, and Rules
In addition to the basic tables and indexes in a DBEnvironment, ALLBASE/SQL lets you
create database objects known as constraints, procedures, and rules, which provide for a
high degree of data consistency and integrity inside the DBEnvironment without the need
for extensive application programming. Constraints define conditions on the rows of a
table; procedures define sequences of SQL statements that can be stored in the
DBEnvironment and applied as a group either through rules or through execution by
specific users; and rules let you define complex relationships among tables by tying specific
procedures to particular kinds of data manipulation on tables. Together, these tools let you
store many of your organization's business rules in the DBEnvironment itself, reducing
the need for application code.
This chapter presents the following topics:
• Using Integrity Constraints
• Using Procedures
• Using Rules
Using Integrity Constraints
Using integrity constraints helps to ensure that a database contains only valid data.
Integrity constraints provide a way to check data within the database system rather than
by coding elaborate validation checks within application programs. An integrity constraint
is either a unique constraint, a referential constraint, or a check constraint. All of these
constraints are described in this section.
When a table is created, integrity constraints can be defined at the column level or at the
table level. A constraint can be placed on an individual column (at the column or table
level) or on a combination of columns (at the table level).
Unique Constraints
A unique constraint requires that no two rows in a table contain the same value in a given
column or list of columns. You can create a unique constraint at either the table level or
the column level. Unique constraints can be defined as either UNIQUE or PRIMARY KEY.
The two types of unique constraints differ in that if a PRIMARY KEY is placed on a
column or column list, the column name(s) can be omitted from the referential constraint
syntax in the definition of the referencing table. A given column upon which a unique or
primary constraint has been defined need not be referenced by a referential constraint; but
a referential constraint can only refer to a column upon which a unique or primary key
constraint has been defined. Referential constraints are discussed below.
Additionally, PRIMARY KEY can be specified only once per table. Duplicate unique