|
Referential integrity is a relational database concept in
which multiple tables share a relationship based on the data
stored in the tables, and that relationship must remain consistent.
The concept of referential integrity, and one way in which it's enforced,
is best illustrated by an example. Suppose company X has
2 tables, an Employee table, and an Employee Salary table. In the Employee
table we have 2 columns - the employee ID and the employee name. In the Employee Salary table,
we have 2 columns - the employee ID and the salary for the given ID.
Now, suppose we wanted to remove an employee because he no longer works at company X.
Then, we would remove his entry in the Employee table. Because he also exists in the Employee
Salary table, we would also have to manually remove him from there also.
Manually removing the employee from the Employee Salary can become quite a pain. And if there
are other tables in which Company X uses that employee then he would have to be deleted from those
tables as well - an even bigger pain.
By enforcing referential integrity, we can solve that problem, so that we wouldn't have to manually delete
him from the Employee Salary table (or any others). Here's how: first we would define the employee ID column
in the Employee table to be our primary key. Then, we would define the employee ID column
in the Employee Salary table to be a foreign key that points to a primary key that is the
employee ID column in the Employee table. Once we define our foreign to primary key relationship, we would
need to add what's called a 'constraint' to the Employee Salary table. The constraint that we would add in particular
is called a 'cascading delete' - this would mean that any time an employee is removed from the Employee table,
any entries that employee has in the Employee Salary table would also automatically be removed from
the Employee Salary table.
Note in the example given above that referential integrity is something that must be enforced, and that
we enforced only one rule of referential integrity (the cascading delete). There are actually 3 rules that
referential integrity enforces:
1.We may not add a record to the Employee Salary table unless the foreign key for that record
points to an existing employee in the Employee table.
2.If a record in the Employee table is deleted, all corresponding
records in the Employee Salary table must be deleted using a cascading delete. This was the example we had given earlier.
3.If the primary key for a record in the Employee table changes, all
corresponding records in the Employee Salary table must be modified using what's called a cascading update.
It's worth noting that RDBMS's - relational databases like Oracle, DB2, Teradata, etc. -
automatically enforce referential integrity. It's an important concept and you simply must know it
for any programmer interviews.
|
