Tech moves fast! Stay ahead of the curve with Techopedia!
Join nearly 200,000 subscribers who receive actionable tech insights from Techopedia.
In order to maintain referential integrity, the relational data in database tables has to be universally configurable so that changes in one part of the system don't lead to unanticipated problems elsewhere.
Specifically, keys that reference elements of other tables need to be connected to those other fields, so that if there is a change, everything gets updated together, and not separately.
This prevents errors where piecemeal updates create discrepancies.
One common way to enforce referential integrity has to do with developing ‘triggers’ that will implement ‘cascades’ throughout a database structure.
Generally speaking, these tools let administrators build in referential integrity that works.
Working with primary and foreign keys, database pros can spin up models for enforcing referential integrity in table systems.
Then, with referential integrity in place, the database contents will be joined together to be jointly updated when necessary. For example:
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.
So, why is referential integrity important?
It allows for the tables to be changed when they need to be. Suppose you have a table of managers in one table, and a table of team members in another table, with a reference to the team member’s manager from the first table.
If one of the managers leaves, and the database has to reflect that, the referential integrity system makes sure that in the second table, the separate team member table, the field for assigned manager gets changed over, too.
Otherwise, you would have the obsolete name cropping up when searching the second table.
All of this process can also be referred to as creating “consistency.”
Database normalization efforts often contribute to this process as well.
Database normalization is commonly described as “reducing a complex data structure into a simple one” and through these techniques, which include the use of the three “forms” known to administrators, referential integrity can be naturally enforced to a significant extent.