Referential integrity (RI) is a relational database concept, which states that table relationships must always be consistent. In other words, any foreign key field must agree with the primary key that is referenced by the foreign key. Thus, any primary key field changes must be applied to all foreign keys, or not at all. The same restriction also applies to foreign keys in that any updates (but not necessarily deletions) must be propagated to the primary parent key.
Consider a bank database, which contains two tables:
CUSTOMER_MASTER Table: This holds basic customer/account holder data such as name, social security number, address and date of birth.
ACCOUNTS_MASTER Table: This stores basic bank account data such as account type, account creation date, account holder and withdrawal limits.
To uniquely identify each customer/account holder in the CUSTOMER_MASTER table, a primary key column named CUSTOMER_ID is created.
To identify a customer and bank account relationship in the ACCOUNTS_MASTER table, an existing customer in the CUSTOMER_MASTER table must be referenced. Thus, the CUSTOMER_ID column – also created in the ACCOUNTS_MASTER table – is a foreign key. This column is special because its values are not newly created. Rather, these values must reference existing and identical values in the primary key column of another table, which is the CUSTOMER_ID column of the CUSTOMER_MASTER table.
Referential integrity is a standard that means any CUSTOMER_ID value in the CUSTOMER_MASTER table may not be edited without editing the corresponding value in the ACCOUNTS_MASTER table. For example, if Andrew Smith’s customer ID is changed in the CUSTOMER_MASTER table, this change also must be applied to the ACCOUNTS_MASTER table, thus allowing Andrew Smith’s account information to link to his customer ID.