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 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.
Table 11-2. Constraint Test Matrix
-------------------------------------------------------------------------------------------------
| | | | |
| DML Operations | UNIQUE or | Referenced Table | Referencing Table |
| | PRIMARY KEY | | |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| [BULK] INSERT or | Must be unique in | | Must match a unique key |
| Type 2 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. | |
| | | | |
-------------------------------------------------------------------------------------------------