HP 3000 Manuals HP 3000 Manuals
Using Unique and Referential Integrity Constraints
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 in nature. 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 10-1. Commands Used with Integrity Constraints
-----------------------------------------------------------------------------------------------
| | | |
| DDL Operations | DCL Operations | DML Operations |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| CREATE TABLE | GRANT REFERENCES | 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 contraints 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 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.
Table 10-2. Constraint Test Matrix
-------------------------------------------------------------------------------------------------
| | | | |
| DML Operations | UNIQUE or | Referenced Table | Referencing Table |
| | PRIMARY KEY | | |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| INSERT or Type 2 | Must be unique in | | Must match a unique key |
| INSERT | the table. | | in the referenced table. |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| UPDATE [WHERE | Must be unique in | No foreign key can | Must match a unique key |
| CURRENT] | the table. | reference the unique key | in the referenced table. |
| | | being updated. | |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DELETE [WHERE | | No foreign key can | |
| CURRENT] | | reference the unique key | |
| | | being deleted. | |
| | | | |
-------------------------------------------------------------------------------------------------