13.18.2 Defining referential constraints

To enable one or more referential constraints, you must first define in the referenced table the primary key that is to be referenced by the foreign key (or keys). To do so, use the CREATE TABLE definition SQL statement to specify PRIMARY KEY in the referenced table. To use check pending status, specify USE in the pd_check_pending operand or do not specify (omit) the operand.

For the referencing table, you specify FOREIGN KEY along with the following information in the FOREIGN KEY clause:

The following subsections explain the actions in the referenced and referencing tables when CASCADE or RESTRICT is specified.

Organization of this subsection
(1) If CASCADE is specified
(2) If RESTRICT is specified
(3) Constraint items in defining referenced and referencing tables
(4) Notes on defining referential constraints
(5) Referential constraint definition examples

(1) If CASCADE is specified

If CASCADE is specified and a change is executed on a primary key value of a referenced table, the referencing foreign key value will also be changed in the same manner. In this case, a check is performed to determine if the updated primary key value is the same as a value in a foreign key. The related value in the referencing foreign key is not changed if such a change would result in a referential constraint violation.

Figures 13-31 and 13-32 show examples of the actions that occur if CASCADE is specified when SQL code is executed on a referenced table and on a referencing table.

Figure 13-31 Example of the actions that occur when update SQL code is executed on a referenced table (with CASCADE specified)

[Figure]

Explanation:
If there is a value in a foreign key that is the same as the value in the primary key, to maintain constraints, the foreign key value is changed in the same way that the primary key value is changed. In the above case, updating of the referenced table is performed. Insertion and deletion are handled in the same manner.

Figure 13-32 Example of the actions that occur when update SQL code is executed on a referencing table (with CASCADE specified)

[Figure]
Explanation:
If there is a value in the primary key that is the same as the value in the foreign key after it is updated, updating of the foreign key value is performed. Updating of the foreign key value is also performed if any foreign key in the referencing table contains a null value, even if no value exists in the primary key that is the same as the updated foreign key value. If neither of the above is true, a referential constraint violation results. If this occurs, there is no effect on the referenced table. Insertion and deletion are handled in the same manner.

Table 13-12 lists primary key operations and describes the resulting actions that occur in the referencing table when CASCADE is specified. Table 13-13 lists foreign key operations and describes the resulting actions that occur in the referenced table when CASCADE is specified.

Table 13-12 Primary key operations and the resulting actions that occur in the referencing table (with CASCADE specified)

Primary key manipulationRelationship between rows in referenced and referencing tablesResult of primary key operationAction in referencing table
Insert (INSERT statement)NoneYNone
Update (UPDATE statement), delete (DELETE statement)The referencing table has a value in a foreign key that is the same as a value in the primary key before the update is performed.YThe update is performed with the same value as that in the primary key, or the rows are deleted.
The referencing table does not have a value in a foreign key that is the same as a value in the primary key before the update is performed.YNone
Legend:
Y: Executed normally.

Table 13-13 Foreign key operations and the resulting actions that occur in the referenced table (with CASCADE specified)

Foreign key manipulationRelationship between rows in referenced and referencing tablesResult of foreign key operationAction in referenced table
Insertion (INSERT statement)The referenced table has a value in its primary key that is the same as a value in a foreign key of the rows to be inserted.YNone
The referenced table does not have a value in its primary key that is the same as a value in a foreign key of the rows to be inserted.A foreign key contains a null value.Y
A foreign key does not contain a null value.N
Update (UPDATE statement)The referenced table has a value in its primary key that is the same as the updated foreign key value.YNone
The referenced table does not have a value in its primary key that is the same as the updated foreign key value.A foreign key contains a null value.Y
A foreign key does not contain a null value.N
Delete (DELETE statement)NoneYNone
Legend:
Y: Executed normally.
N: A constraint violation error occurs.

Note that when you specify CASCADE, HiRDB internally generates a trigger during table definition to update the foreign key value with the change made in the primary key value. For details about triggers for referential constraint actions and about user-defined triggers, see 13.18.6 Referential constraints and triggers.

(2) If RESTRICT is specified

If RESTRICT is specified and a change is executed on a primary key value of a referenced table, a referential constraint violation occurs if there is a value in a foreign key that is the same as the value in the primary key after it has been updated. In this case, the primary key value is not changed. If a change is executed on a foreign key value, a check is performed to determine if there is a value in the primary key that is the same as the updated foreign key value. If a referential restraint violation error occurs, updating is not performed on the foreign key value.

Figure 13-33 shows an example of the actions that occur if RESTRICT is specified when SQL code is executed on a referenced table. The actions in a referencing table are the same as those when CASCADE is specified (see Figure 13-32).

Figure 13-33 Example of the actions that occur when update SQL code is executed on a referenced table (with RESTRICT specified)

[Figure]

Explanation:
If there is a value in a foreign key that is the same as a value in the primary key, a referential constraint violation error occurs, and updating of the primary key value is not performed. If there is no foreign key value that is the same, updating of the referenced table is performed. Insertion and deletion are handled in the same manner.

Table 13-14 lists primary key operations and describes the resulting actions that occur in the referenced table when RESTRICT is specified. Foreign key operations and describes the resulting actions that occur in the referenced table are the same as those when CASCADE is specified (see Table 13-13).

Table 13-14 Primary key operations and the resulting actions that occur in referenced and referencing tables

Primary key manipulationRelationship between rows in referenced and referencing tablesResult of primary key operationAction in referencing table
Insertion (INSERT statement)NoneYNone
Update (UPDATE statement), delete (DELETE statement)The referencing table has a value in a foreign key that is the same as a value in the primary key before the update is performed.NNone
The referencing table does not have a value in a foreign key that is the same as a value in the primary key before the update is performed.Y
Legend:
Y: Executed normally.
N: A constraint violation error occurs.

(3) Constraint items in defining referenced and referencing tables

The following notes explain constraint items in table definition, table definition change, and table deletion performed on referenced and referencing tables.

(a) Defining tables (CREATE TABLE)
(b) Changing table definitions (ALTER TABLE)
(c) Deleting tables (DROP TABLE)

(4) Notes on defining referential constraints

(5) Referential constraint definition examples

The following section provides examples of how to define referential constraints.

(a) Example of defining a referential constraint with a 1-to-1 correspondence

This example defines a referential constraint where the referenced and referencing tables have a 1-to-1 correspondence.

[Figure]

Definition example of a referential constraint (1)

CREATE TABLE MANUFACTURER
 (MNO CHAR(4),MNAME NCHAR(6),TELEPHONE CHAR(12))
 PRIMARY KEY(MNO)  ...Specification of the primary key
CREATE TABLE PRODUCT
 (PNO CHAR(4),MNO CHAR(4),PNAME NCHAR(10),QTY INTEGER)
 CONSTRAINT PRODUCT_FK  ...Specification of the constraint name
 FOREIGN KEY(MNO)  ...Specification of the foreign key
 REFERENCES MANUFACTURER  ... Specification of the referenced table name

Details of the referential constraint action
Because this example omits specification of a referential constraint action, RESTRICT is assumed during updating or deletion. If the MANUFACTURER_NO (primary key) of the MANUFACTURER_TABLE is updated or deleted and there is a row corresponding to the MANUFACTURER_NO (foreign key) of the PRODUCT_TABLE, a referential constraint violation error occurs. As a result, updating or deletion of the MANUFACTURER_NO of the MANUFACTURER_TABLE is suppressed.
Definition example of a referential constraint (2)

CREATE TABLE MANUFACTURER
 (MNO CHAR(4),MNAME NCHAR(6),TELEPHONE CHAR(12))
 PRIMARY KEY(MNO)  ...Specification of the primary key
CREATE TABLE PRODUCT
 (PNO CHAR(4),MNO CHAR(4),PNAME NCHAR(10),QTY INTEGER)
 CONSTRAINT PRODUCT_FK  ... Specification of the constraint name
 FOREIGN KEY(MNO)  ... Specification of the foreign key
 REFERENCES MANUFACTURER  ... Specification of the referenced table name
 ON UPDATE CASCADE  ...Specification of a referential constraint action on update
 ON DELETE CASCADE  ... Specification of a referential constraint action on deletion

Details of the referential constraint action
If the MANUFACTURER_NO (primary key) of the MANUFACTURER_TABLE is updated, the MANUFACTURER_NO (foreign key) of the corresponding PRODUCT_TABLE is also updated to the same value as for the primary key. If a row is deleted from the MANUFACTURER_TABLE, the row corresponding to the PRODUCT_TABLE is also deleted.
(b) Example of defining a referential constraint with a 1-to-2 correspondence

This example defines a referential constraint where there is one referenced table and two referencing tables.

[Figure]

Definition example of a referential constraint

CREATE TABLE PRODUCT
 (PNO CHAR(4),MNO CHAR(4),PNAME NCHAR(10),QTY INTEGER)
 PRIMARY KEY(PNO)  ...Specification of the primary key
CREATE TABLE PURCHASE
 (PNO CHAR(4),PNAME NCHAR(10),PQTY INTEGER)
 CONSTRAINT PURCHASE_FK  ... Specification of the constraint name
 FOREIGN KEY(PNO)  ... Specification of the foreign key
 REFERENCES PRODUCT  ... Specification of the referenced table name
 ON UPDATE CASCADE  ... Specification of a referential constraint action on update
 ON DELETE CASCADE   ... Specification of a referential constraint action on deletion
CREATE TABLE SALES
 (FNO CHAR(4),CNO CHAR(4),PNO CHAR(4),SQTY INTEGER)
 CONSTRAINT SALES_FK  ... Specification of the constraint name
 FOREIGN KEY(PNO)  ... Specification of the foreign key

 REFERENCES PRODUCT  ... Specification of the referenced table name
 ON UPDATE RESTRICT  Specification of a referential constraint action on update
 ON DELETE RESTRICT  ... Specification of a referential constraint action on deletion

Details of the referential constraint action
If the PRODUCT_NO (primary key) of the PRODUCT_TABLE is to be updated and the SALES_TABLE contains a row whose PRODUCT_NO (foreign key) is the same as the primary key before updating, a referential constraint violation error occurs, in which case update processing is suppressed. If the SALES_TABLE contains no row that has the same value as the primary key before updating, the corresponding PRODUCT_NO in the PURCHASE_TABLE is also updated to the same value as the primary key.
If a row is to be deleted from the PRODUCT_TABLE and the SALES_TABLE contains a row that has the same value as the primary key before updating, a referential constraint violation error occurs, in which case the deletion processing is suppressed. If the SALES_TABLE contains no row that has the same value as the primary key, the corresponding row is also deleted from the PURCHASE_TABLE.
(c) Example of defining a referential constraint with a 2-to-1 correspondence

This example defines a referential constraint where there are two referenced tables and one referencing table.

[Figure]

Definition example of a referential constraint

CREATE TABLE PRODUCT
 (PNO CHAR(4),MNO CHAR(4),PNAME NCHAR(10),QTY INTEGER)
 PRIMARY KEY(PNO)  ...Specification of the primary key
CREATE TABLE CUSTOMER
 (CNO CHAR(4),CNAME NCHAR(8),ADDR NCHAR(24))
 PRIMARY KEY(CNO)  ...Specification of the primary key
CREATE TABLE SALES
 (FNO CHAR(4),CNO CHAR(4),PNO CHAR(4),SQTY INTEGER)
 CONSTRAINT SALES_PRODUCT_FK  ... Specification of the constraint name
 FOREIGN KEY(PNO)  ... Specification of the foreign key
 REFERENCES PRODUCT  ... Specification of the referenced table name
 ON UPDATE CASCADE  Specification of a referential constraint action on update
 ON DELETE CASCADE  ... Specification of a referential constraint action on deletion
 CONSTRAINT SALES_CUSTOMER_FK
 FOREIGN KEY(CNO)  ... Specification of the foreign key

 REFERENCES  CUSTOMER  ... Specification of the referenced table name
 ON UPDATE CASCADE  ... Specification of a referential constraint action on update
 ON DELETE CASCADE  ... Specification of a referential constraint action on deletion

Details of the referential constraint action
If the PRODUCT_NO (primary key) of the PRODUCT_TABLE is updated, the PRODUCT_NO (foreign key) of the SALES_TABLE is also updated to the same value. If a row is deleted from the PRODUCT_TABLE, the corresponding row is also deleted from the SALES_TABLE.
If the CUSTOMER_NO (primary key) of the CUSTOMER_TABLE is updated, the CUSTOMER_NO (foreign key) of the SALES_TABLE is also updated to the same value. If a row is deleted from the CUSTOMER_TABLE, the corresponding row is also deleted from the SALES_TABLE.