ALLBASE/SQL Reference Manual (36216-90216)

142 Chapter4
Constraints, Procedures, and Rules
Using Integrity Constraints
2. Clubs sponsoring the events are listed in the Clubs table
3. Events cannot be scheduled earlier than the current date.
CREATE PUBLIC TABLE RecDB.Clubs
(ClubName CHAR(15) NOT NULL
PRIMARY KEY CONSTRAINT Clubs_PK,
ClubPhone SMALLINT,
Activity CHAR(18))
IN RecFS;
CREATE PUBLIC TABLE RecDB.Members
(MemberName CHAR(20) NOT NULL,
Club CHAR(15) NOT NULL,
MemberPhone SMALLINT,
PRIMARY KEY (MemberName, Club) CONSTRAINT Members_PK,
FOREIGN KEY (Club) REFERENCES RecDB.Clubs
CONSTRAINT Members_FK)
IN RecFS;
CREATE PUBLIC TABLE RecDB.Events
(Event CHAR(30),
Coordinator CHAR(20),
SponsorClub CHAR(15),
Date DATE DEFAULT CURRENT_DATE,
CHECK (Date >= '1990-01-01'),
Time TIME,
FOREIGN KEY (Coordinator, SponsorClub)
REFERENCES RecDB.Members
CONSTRAINT Events_FK)
IN RecFS;
Note that updating the Members table before the Clubs table could cause a referential
constraint error when error checking is at statement level. The RecDB.Members.Club
column references the RecDB.Clubs.ClubName column which is not yet updated. However,
if you deferred referential checking to the end of the transaction, no error would occur. A
value could then be inserted into the RecDB.Clubs.ClubName column that would resolve
the reference. When a COMMIT WORK statement is executed, no constraint errors will exist.
The illustration in Figure 4-1. shows the referential constraints based on this sample
schema. The arrows point to the columns with unique constraints.
Figure 4-1. Referential Constraints in a Set of Tables