Explain Referential integrity.

52views

written 8.4 years ago by

teamques10 ★ 68k

Referential integrity is a database concept that ensures that relationships between tables remain consistent.
When one table has a foreign key to another table, the concept of referential integrity states that you may not add a record to the table that contains the foreign key unless there is a corresponding record in the linked table.
Referential integrity is violated when the relation to which a foreign key refers no longer exists.
It also includes the techniques known as cascading update and cascading delete, which ensure that changes made to the linked table are reflected in the primary table.
Consider the situation where we have two tables: Employees and Managers. The Employees table has a foreign key attribute entitled Managed By which points to the record for that employee’s manager in the Managers table.
Referential integrity enforces the following three rules:

We may not add a record to the Employees table unless the Managed By attribute points to a valid record in the Managers table.
If the primary key for a record in the Managers table changes, all corresponding records in the Employees table must be modified using a cascading update.
If a record in the Managers table is deleted, all corresponding records in the Employees table must be deleted using a cascading delete.

Example:

Consider the example of Employee and Department relationship table
If we have dept_id as foreign key in Employee table than by using referential integrity constraints we can avoid creating Employee without department or non-existing department.
In short Referential Integrity makes primary key foreign key relationship viable.

Let's first create Employee and Department table with primary key, foreign key and referential Integrity constraints.

    CREATE TABLE Department (dept_id INT NOT NULL,
                        dept_name VARCHAR(256),                       
                         PRIMARY KEY (dept_id)) ENGINE=INNODB;

    CREATE TABLE Employee (emp_id INT NOT NULL,
                emp_name VARCHAR(256),
                dept_id INT,
              FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
                ON DELETE CASCADE) ENGINE=INNODB;

Above statements will create both Departmentand Employee table. dept_id is now foreign key in Employee table.
In this SQL, while creating foreign key we have specified ON DELETE clause which tells, what needs to done when a record from parent table is deleted.
CASCADE referential action allows to delete or update all matching rows from child table, after deleting a record in parent table.
This way Referential Integrity preserve data integrity of relationship.

ADD COMMENT EDIT