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.
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. 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 12-33 and 12-34 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 12-33 Example of the actions that occur when update SQL code is executed on a referenced table (with CASCADE specified)
Figure 12-34 Example of the actions that occur when update SQL code is executed on a referencing table (with CASCADE specified)
Table 12-13 lists primary key operations and describes the resulting actions that occur in the referencing table when CASCADE is specified. Table 12-14 lists foreign key operations and describes the resulting actions that occur in the referenced table when CASCADE is specified.
Table 12-13 Primary key operations and the resulting actions that occur in the referencing table (with CASCADE specified)
Primary key manipulation | Relationship between rows in referenced and referencing tables | Result of primary key operation | Action in referencing table |
---|---|---|---|
Insert (INSERT statement) | None | Y | None |
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. | Y | The 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. | Y | None |
Table 12-14 Foreign key operations and the resulting actions that occur in the referenced table (with CASCADE specified)
Foreign key manipulation | Relationship between rows in referenced and referencing tables | Result of foreign key operation | Action 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. | Y | None | |
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. | Y | None | |
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) | None | Y | None |
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 12.19.6 Referential constraints and triggers.
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.
The figure below shows an example of the actions that occur when update SQL code is executed on a referenced table and RESTRICT is specified. The actions in a referencing table are the same as those when CASCADE is specified (see Figure 12-34).
Figure 12-35 Example of the actions that occur when update SQL code is executed on a referenced table (with RESTRICT specified)
The table below lists primary key operations when RESTRICT is specified, and resulting actions in referenced and referencing tables. 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 12-14).
Table 12-15 Primary key operations and the resulting actions that occur in referenced and referencing tables
Primary key manipulation | Relationship between rows in referenced and referencing tables | Result of primary key operation | Action in referencing table |
---|---|---|---|
Insertion (INSERT statement) | None | Y | None |
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. | N | None |
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 |
The following notes explain constraint items in table definition, table definition change, and table deletion performed on referenced and referencing tables.
The following section provides examples of how to define referential constraints.
This example defines a referential constraint where the referenced and referencing tables have a 1-to-1 correspondence.
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
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
This example defines a referential constraint where there is one referenced table and two referencing tables.
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
This example defines a referential constraint where there are two referenced tables and one referencing table.
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