When you buy through affiliate links in our content, we may earn a commission at no extra cost to you. Learn What Does Referential Integrity Mean?
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.
Techopedia Explains Referential Integrity
The typical way to enforce referential integrity is through foreign key constraints. Another common way 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.
In Relational Database Management Systems (RDBMS) referential integrity can be enforced by working with primary and foreign keys. Each foreign key must have a matching primary key so that reference from one table to another must always be valid.
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.
A referential integrity constraint is specified between two tables. If any record in the table containing the Primary Key (CUSTOMER_MASTER table) is deleted, all the corresponding records of the Foreign Key (ACCOUNTS_MASTER Table) will be deleted to enforce referential integrity.
Following the example mentioned above, if a customer is deleted, all data about his account must be null or its account records would refer to a non-existent customer.
Referential integrity must be maintained before every transaction is completed. If a key constraint is enforced to uniquely identify an entity within a table (such as the CUSTOMER_ID), if a new entity with the same ID is created the transaction will be prevented to ensure referential integrity is maintained.
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.
