To enable a referential constraint, you must first define in the referenced table the primary key that is referenced by the foreign key. To do so, use the CREATE TABLE definition SQL statement to specify PRIMARY KEY in the referenced table. To apply check pending status, specify USE in the pd_check_pending operand or omit specification of the pd_check_pending operand.

To define a referential constraint, you first specify FOREIGN KEY in the referencing table. In the FOREIGN KEY clause, you specify the following:

• Column to be referenced
• Referenced table
• Referential constraint action
  A referential constraint action is an insertion, update, or deletion action in the referenced table; it is specified with CASCADE or RESTRICT.
  

The following subsections explain the actions in the referenced table and the referencing table when CASCADE and RESTRICT are specified.

(a) When CASCADE is specified

When CASCADE is specified and a primary key of a referenced table is changed, the foreign key is also changed. When a foreign key of a referencing table is changed, a check is performed to determine if there is a row containing a primary key whose value is the same as the value of the foreign key after the change; the foreign key is not changed if such a change would result in a referential constraint violation.

Figures 5-8 and 5-9 show examples of the actions that occur when CASCADE is specified when SQL code is executed for a referenced table and for a referencing table.

Figure 5-8 Example of the action when update SQL code is executed for a referenced table (with CASCADE specified)

Explanation

If a row contains a foreign key with the same value as the primary key, then in order to maintain constraints the foreign key is changed in the same way that the primary key is changed. In this case, updating is performed on the referenced table; insertion and deletion would be handled in the same manner.

Figure 5-9 Example of the action when update SQL code is executed for a referencing table (with CASCADE specified)

Explanation

If a row contains a primary key with the same value as that of the foreign key after updating, the update is executed on the foreign key. The update is also executed if the foreign key contains a null value, even if there is no row containing a primary key with the same value. If there is no null value, a referential constraint violation results. If this occurs, there is no effect on the referenced table. Insertion and deletion would be handled in the same manner.

(b) When RESTRICT is specified

When RESTRICT is specified, then when a primary key of a referenced table is changed, a referential constraint violation occurs if there is a row that contains a foreign key with the same value. If the foreign key can be changed, a check is performed to determine if a row contains a primary key with the same value and, if a referential constraint violation would result, the foreign key is not changed.

Figure 5-10 shows the actions that occur when RESTRICT is specified when SQL code is executed for a referenced table. The action that occurs in a referencing table is the same as when CASCADE is specified (see Figure 5-9).

Figure 5-10 Example of the action when update SQL code is executed for a referenced table (with RESTRICT specified)

When you use a data manipulation SQL (other than PURGE TABLE) to manipulate data in a referenced table or referencing table, HiRDB performs checking when the SQL code is executed in order to guarantee data integrity. However, if you perform the data operations shown below, HiRDB may no longer be able to guarantee data integrity.

• Loading data with the database load utility (pdload)
• Reorganization or reloading with the database reorganization utility (pdrorg)
• Re-initialization of an RDAREA (initialize rdarea) with the database structure modification utility (pdmod)
• Execution of the PURGE TABLE statement
• Changing the partition storage conditions of a table with the ALTER TABLE statement

If you do perform any of these data operations, you must verify the integrity of the data. For details about how to verify data integrity, see the HiRDB Version 8 Installation and Design Guide. If USE is specified in the pd_check_pending operand, the referencing table is placed in check pending status if you perform any of these data operations.