12.19.1 About referential constraints

The tables in a database may not all be independent, because some tables may be related to one another. Some data in a table may serve no purpose if related data does not exist in another table. To maintain referential conformity in data between tables, a referential constraint can be defined for a particular column (called a foreign key) when the table is defined. A table in which a referential constraint and a foreign key are defined is called a referencing table, while a table that is referenced from a referencing table by means of such a foreign key is called a referenced table. A primary key, which is referenced by one or more foreign keys, must be defined in the referenced table.

Execution of SQL code or utilities may cause loss of guaranteed data integrity between referenced and referencing tables. In such cases, the referencing table is placed in check pending status. For details about check pending status, see 12.19.3 Check pending status. For details about operations that cause loss of guaranteed data integrity, see 12.19.4 Data manipulation and integrity.

The figure below shows examples of a referenced and a referencing table. In this example, PRODUCT_TABLE is the referencing table and MANUFACTURER_TABLE is the referenced table. The primary key is referenced by a foreign key in the referencing table to obtain the name of a manufacturer.

Figure 12-32 Example of referenced and referencing tables

[Figure]

When you define a referential constraint, defining an index for the foreign key improves throughput. However, if the primary key values in the referenced table are not updated, updating performance may be affected adversely due to the overhead associated with the index updating that results when a foreign key value is updated.

Effects of referential constraints
When you define a referential constraint, the workload associated with UAP creation can be reduced because checking of data integrity on tables and data manipulation can be automated. However, note that the processing time for checking increases when referenced and referencing tables are updated, because data integrity is checked. Processing time increases for checks in the following cases.
  • The column to be updated is the primary key of the referenced table.
    The more foreign keys there are in the referencing table that references the primary key of the referenced table, the greater the delay.
  • The column to be updated is the foreign key of the referencing table.
    The more foreign keys there are that have the column to be updated as a constituent column, the greater the delay.