Database Related

What Is Referential Integrity

 

It is a complex term which is related to data base concepts and ensures that an integral relationship between tables stay consistent. In simple words, if one table possesses a foreign key which is linked to another table, then according to referential integrity you cannot add an additional record to the table if the table is locked with a foreign key, until and unless there is another record which corresponds with the other table. This also has a few techniques called cascading delete and cascading update. These techniques make certain that the changes you have made in the secondary table can be applied to the primary table as well.

This concept is best illustrated by an example. Suppose a company has two tables, first table contains the record of employees and the other table contains the record of the managers. Now the first table is linked with a foreign key and it is named as ManagedBy, which hints that the employee’s record is linked with the record of the manager of that specific employee in the table that contains Manager’s records. The referential integrity has three basic rules which are listed below:

  1. A record cannot be added to the table which is for the employees unless the ManagedBy option allows it to make changes into Managers table.
  2. If a primary key which is linked to a record in the tables of manager’s changes, every corresponding record must be changed in the employee table with the cascading update.
  3. Suppose if one record is deleted from the table of the managers, then every corresponding record should also be deleted in the table of the employee with cascading delete.

 

It is important to know that most relational databases such as DB2, Oracle, Teradata can apply referential integrity automatically in the system if the settings are right. However, the burden lies with the one who is maintaining referential integrity and designs the schema of the database, basically the one who has defined those tables and the related structure/relationships that are in your database. And it is one of the most important concepts of programming.

 

 

 

 

 

 

 

 

 

 

Leave a Reply