How Referential Integrity ensures database consistency
Referential integrity is a database feature in relational database management systems. It ensures that relationships between tables in a database remain accurate by applying constraints to prevent users or applications from entering inaccurate data or showing data that does not exist.
Databases use tables to organize the information they contain. They are similar to spreadsheets, such as Excel, but are much more capable for advanced users. Databases work by using primary keys and foreign keys, which maintain the relationship between tables.
Primary key
The primary key of a database table is a unique identifier that corresponds to each record. Each table will have one or more columns designated as a primary key. A social security number can be a primary key for a list of employee databases because each social security number is unique.
However, due to privacy concerns, a recognized company ID number is a better choice to act as a primary key for employees. Some database software – such as Microsoft Access – automatically assigns the primary key, but the random key has no real meaning. It is better to use a meaningful key for registration. The simplest way to enforce referential integrity is to disallow changes to a primary key.
Foreign key
A foreign key is an identifier in a table that matches the primary key of a different table. The foreign key creates the relationship with a different table, and referential integrity refers to the relationship between those tables.
When a table has a foreign key to another table, the concept of referential integrity states that you cannot add a record to the table containing the foreign key unless there is a corresponding record in the linked table. It also includes the techniques known as update update and delete with cascading effects, which ensure that changes made to the linked table are reflected in the parent table.
Example of integrity rule rules
Consider the situation where you have two tables: Employees and Managers. The Employees table has a foreign key attribute named ManagedBy that points to the record for each employee’s manager in the Managers table. Referential integrity applies the following three rules:
You cannot add a record to the Employees table unless the ManagedBy attribute points to a valid record in the Managers table. Referential integrity prevents incorrect details from being inserted into a table. Any operation that does not satisfy the referential integrity rule fails.
If the primary key for a record in the Managers table changes, all corresponding records in the Employees table are modified using an up-to-date update.
If a record in the “Administrators” table is deleted, all corresponding records in the “Employees” table are deleted by sequential deletion.
Advantages of referential integrity constraints
Using a relational database management system with referential integrity offers several advantages:
Prevents duplicate data entry.
Prevents a table from pointing to a nonexistent field in another table.
Guaranteed consistency between “partnering” tables.
Prevents the deletion of a record that contains a value referenced by a foreign key in another table.
Prevents adding a record to a table containing a foreign key unless there is a primary key in the linked table.
