ALLBASE/SQL Reference Manual (36216-90216)

Chapter 4 141
Constraints, Procedures, and Rules
Using Integrity Constraints
However, the same constraint defined at the table level is defined as follows:
CREATE PUBLIC TABLE RecDB.Events
CHECK (Date >= '1990-01-01')
Check Constraint
CONSTRAINT Check_No_Old_Events
(SponsorClub CHAR(15),
Event CHAR(30),
Date DATE DEFAULT CURRENT_DATE,
Time TIME,
Coordinator CHAR(20),
FOREIGN KEY (Coordinator, SponsorClub)
REFERENCES RecDB.Members (MemberName, Club)
CONSTRAINT Events_FK)
IN RecFS;
This table level constraint could also be defined after the Date or Time column, or at any
point in the parenthesized list. There is one difference between table and column level
check constraints: a column level check constraint must reference only the column on
which it is defined.
A check constraint that references more than one column
must be defined at the table
level. For example, the constraint CHECK (Date >= '1990-01-01' AND Time > '00:00.000')
must be defined at the table level because both the Date and Time columns are specified in
the check constraint.
A view check constraint is defined with the CREATE VIEW statement using the following
syntax at the end of the view definition:
WITH CHECK OPTION [CONSTRAINT ConstraintID ]
The conditions of the SELECT statement defining the view become the view check
constraint search conditions when the WITH CHECK OPTION clause is specified. A view
can have only one WITH CHECK OPTION. This check constraint checks all of the
conditions which are included in the SELECT statement. These SELECT statement
conditions serve two purposes. First, they originally define the view. They also define the
conditions of the check constraint that is applied when the underlying base table is
modified through the view. When a table is modified through a view, the view check
constraint is checked along with any table constraints. The view check constraint must be
true (not unknown ) to ensure that all changes made through a view can still be displayed.
All underlying views are also checked, whether or not they are defined with check options.
Unique and referential constraints cannot be defined on views.
See Chapter 10 , “SQL Statements A - D,” for the check constraint syntax, within the
syntax of CREATE TABLE, ALTER TABLE, or CREATE VIEW statements.
Examples of Integrity Constraints
The schema example in this section shows the constraints among three tables: Clubs,
Members, and Events. The tables are created as PUBLIC so as to be accessible to any user
or program that can start a DBE session.
Constraints are placed on the tables to ensure that:
1. Events are coordinated by club members who are listed in the Members table