Referential integrity in RDBMS can be defined as a consistent relationship shared between tables present in a database. In other words, the foreign key references used in a database must agree with the references of primary key. The set of referential integrity constraints can applied to foreign key to prevent a row from entering inside child table. The incomplete and wrong relationship present in a database can be removed using referential integrity.

• Related: SQL & .NET Interview questions and Answers

Let us consider that we have two tables named company and employee. Company table will various financial dealings made by the company. Employee table will contain the designation, dept and remuneration paid. Both employee and company table can be related using the foreign key employee_id. The employee_id can be used to search between the two tables. If a key from company table is used instead of employee_id it will result in failure of referential integrity.

Explanation of Referential Integrity using MySQL

The relationship between a student and his college can be also used as an example for defining referential integrity. If college_id is used as a foreign key in student table, creation of student without college can be avoided by using the constraints of referential integrity. The primary key is declared as the foreign key in referential integrity to increase the vitality of relationship between the tables.

• Recommended: Final year Academic projects download

The following MySQL code is used to create foreign key, primary key and referential integrity constraints.

CREATE TABLE College (college_id INT NOT NULL,

college_name VARCHAR (250),

PRIMARY KEY (college_id)) ENGINE=INNODB;

CREATE TABLE Student (stud_id INT NOT NULL,

Stud_name VARCHAR(250),

College_id INT,

FOREIGN KEY (dept_id) REFERENCES Department (dept_id)

ON DELETE CASCADE) ENGINE=INNODB;

Using the SQL statements given above, tables like student and college can be created. The foreign key used to relate these tables is college_id. The cascade referential mentioned in the code will be used to update child according to the changes made in parent table.

INSERT INTO College VALUES (1, “Cambridge”);

INSERT INTO Student VALUES (101, “Harry”,2)

Mysq1> INSERT INTO Employee VALUES (101, “Harry”,2)

->;

ERROR 1452 (23000): Cannot ADD OR UPDATE a child row: a FOREIGN KEY constraint fails

(‘test’, ‘student’, CONSTRAINT ‘student_ibfk_1’ FOREIGN KEY (‘college_id’) REFERENCES

‘College’ (‘college_id’) ON DELETE CASCADE)

In the above code, the referential integrity disallows changes in child table when it has no similarities to parent table. During the insertion of the first record in College table nothing went wrong but after insertion of student table using college_id 2 referential integrity is failed. The referential integrity is failed because college_id 2 is not present in college table. If college_id 2 is changed into college_id, the code will run without error.

If record from college table is deleted, the corresponding record in child table will also be deleted.

mysql> DELETE FROM College;

Query Ok, 1 row affected (0.05 sec)

mysql> SELECT * FROM Student;

Empty SET (0.00 sec)

From the code above, no record in student table is found because of ON DELETE CASCADE.

Update and Delete Rules in Referential Integrity

The following update and delete rules can be mentioned in all relations which use referential integrity.

DELETE

• RESTRICT - A row deletion in parent table is prevented, if same row is present in child table.
• CASCADE - Using cascade number of child rows can be deleted, if a parent row is deleted.
• SET_NULL - If a parent row is deleted, the value of foreign key is set to NULL.
• SET_DEFAULT - If a parent row is deleted then the value foreign key is set to already assigned default value

UPDATE

• RESTRICT - Update on primary key value is stopped if foreign key value is present inside child table
• CASCADE - If primary key value is updated, automatic foreign key update will occur
• SET_NULL - All foreign values will be set to NULL if a primary key is updated.
• SET_DEFAULT - Foreign keys will be set to default if primary key is updated.

Advantages of using referential integrity

• Inserting records with wrong details can be prevented using referential integrity.
• If referential integrity does not hold good all update operation will be stopped.
• If a record in parent table is deleted, referential integrity will make sure that record in child table are also deleted.
• If a record is updated in parent table, referential integrity is used to update records in child table.