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 13.18.3 Check pending status. For details about operations that cause loss of guaranteed data integrity, see 13.18.4 Data manipulation and integrity.
Figure 13-30 shows an example of a referenced table 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 13-30 Example of referenced and referencing tables
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.