ALLBASE/SQL Reference Manual (36216-90216)

Chapter 4 143
Constraints, Procedures, and Rules
Using Integrity Constraints
The Events table contains information about events. The combination of values in the
Coordinator and SponsorClub columns of the Events table must be either be null or match
the combination of values in the MemberName and Club columns of the Members table, as
shown by the Events_FK constraint.
The Members table contains the names of members and clubs. A member can be in more
than one club. For every Coordinator/SponsorClub pair of values exists a corresponding
MemberName/Club match.
The Clubs table contains information about clubs. For every club entry in the Members
table, a corresponding entry must exist in the Clubs table, as shown by the Members_FK
constraint.
Inserting Rows in Tables Having Constraints
There are two ways you can insert data in tables having constraints. You can insert values
in referenced columns before inserting values in referencing columns, or you can defer
constraint error checking in a transaction until all constraints referring to each other have
been resolved.
With the first method, using the tables defined in the previous example, the Clubs data
should be loaded first, then the Members data, because the MemberName column is
dependent on the ClubName column. The Events table should be loaded last as the
Coordinator and SponsorClub columns are dependent on the MemberName and Club
columns of the Members Table.
If the Clubs, Members, and Events tables were empty and you attempted to insert the
values in the order shown below, you would receive the following corresponding results:
Order Table Values Result
1 Members 'John Ewing', 'Energetics', 6925 Violates Members_FK because
'Energetics' club does not exist in
the ClubName column of the
Clubs table
2 Members 'John Ewing', NULL, 6925 Violates NOT NULL on
Members_PK columns
3 Clubs 'Energetics', 1111, 'aerobics' Valid
4 Clubs 'Windjammers', 2222, 'sailing' Valid
5 Clubs 'Energetics', 3333,' lo-impact' Violates Clubs_PK because
'Energetics' is already in the
ClubName column of the Clubs
table (entries must be unique in a
primary key column)
6 Members 'John Ewing', 'Energetics', 6925 Valid
7 Events 'Energetics', 'advanced stretching',
'1986-12-04', '15:30:00', 'Martha
Mitchell'
Valid