Referential Integrity

Referential integrity state the relationship between tables( parent and child ) . Database table must have a primary key that appears in the other tables (child tables) for relationship to the data of those tables. Primary key of a table when appears in the other tables of the database it is called foreign key. Foreign keys joins tables and establish dependencies between tables.

In other words, Referential integrity is set of constraints which applied on foreign key on child table for the purpose of prevent entering a row in child table for which there is no corresponding row in the parent table i.e. entering NULL or invalid foreign keys.

Referential integrity is the logical dependency of a foreign key on a primary key . When we delete a row that contains a primary key or update it with different primary key, we destroy the meaning of any rows that contain that value as foreign key.

Referential Integrity Ensures :

  • Accuracy and consistency of data within a relationship.
  • Primary key and Foreign key should have same data type.
  • Referential integrity prevents incorrect or incomplete relationship.

Example of Referential Integrity:

Employee and Department relationship . We have dept_id as foreign key in Employee table . By using referential integrity constrains we can avoid creating employee without having department.

Table : Employee

emp_idemp_namedept_id
1001Tony Greig2001
1002Bill Gate2002
1003Sundar Pichai2003

Table : Department

dept_iddept_name
2001Marketing
2002IT
2003Management
Referential Integrity 1
Referential Integrity
CREATE TABLE Department (
dept_id INT NOT NULL,
dept_name     VARCHAR (255),
PRIMARY KEY (dept_id)) ENGINE = INNODB;

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

Above SQL statement create two tables Department and Employee where dept_id is foreign key in Employee table.

We have mention ON DELETE clause in the SQL statement which state what action need to perform before deleting a record from parent table. CASCADE reference action tells that we have to delete or update all match records in child table after deleting records in parent table.

Advantages of Referential Integrity

There are several advantages of Referential Integrity. some of theme are listed below: –

  • Prevent duplicate entry.
  • If records from parent table is deleted corresponding records also deleted from child table using CASCADE-DELETE.
  • Guarantees consistency of data between parents and child table.
Share This!