Any database containing tables with interdependent data is a good candidate for
the use of integrity constraints. You can profit from their use whether
your data is volatile or stable.
For instance, your database might contain a table of employee and department
data that is constantly changing, or it could contain a table of part number
data that rarely changes even though it is frequently accessed.
(Note that integrity constraints cannot be assigned to LONG columns. LONG columns are described in the chapter, "Programming with LONG Columns.")
To implement unique and referential constraints,
use the CREATE TABLE command and optionally the
GRANT REFERENCES command in your schema file.
The following table lists the commands you might use in dealing with
integrity constraints.
Table 11-1 Commands Used with Integrity Constraints
DDL Operations | DCL Operations | DML Operations |
---|
CREATE TABLE | GRANT REFERENCES | [BULK] INSERT |
DROP TABLE | GRANT DBA | UPDATE [WHERE CURRENT] |
REMOVE FROM GROUP | REVOKE REFERENCES | DELETE [WHERE CURRENT] |
DROP GROUP | REVOKE DBA | |
The concepts and syntax of integrity constraints are fully discussed
in the ALLBASE/SQL Reference Manual , and database administration
considerations are found in the ALLBASE/SQL Database Administration Guide .
This chapter contains techniques to use when coding applications
that manipulate data upon which integrity constraints have been defined.
When executing the [BULK] INSERT, UPDATE [WHERE CURRENT],
or DELETE [WHERE CURRENT] commands,
ALLBASE/SQL considers
applicable integrity constraints depending on what the overall effect of a
statement would be once it completes execution.
The syntax for UNIQUE or PRIMARY KEY requires unique constraint enforcement.
The syntax for REFERENCES requires referential constraint enforcement on the
referencing and referenced tables involved.
For example, consider the following table showing what tests must
be passed for a DML command to successfully
complete. Refer to the ALLBASE/SQL Reference Manual for more information on enforcing constraints.
Table 11-2 Constraint Test Matrix
DML Operations | UNIQUE or PRIMARY KEY | Referenced Table | Referencing Table |
---|
[BULK] INSERT or Type 2 INSERT | Must be unique in the table. | | Must match a unique key in the
referenced table. |
UPDATE [WHERE CURRENT] | Must be unique in the table. | No foreign key can reference the unique
key being updated. | Must match a unique key in the referenced table. |
DELETE [WHERE CURRENT] | | No foreign key can reference the unique key
being deleted.
| |