Referential integrity in DBMS

Referential Integrity constraint

Referential integrity, Let's break the word into two parts:

1. Reference: The act of looking at or in something for information or data.
2. Integrity: Firm adherence to a coding standard.

So, before we dive into the Referential integrity of DBMS, it's a prerequisite that we should have a good idea about how Data Base Management System Works, for that you can refer to:

"Data Base Management System" for better understanding.

Referential integrity is a constraint applied on foreign, which requires the foreign key to have a matching primary key or provided primary key is not a null value. Therefore, a constraint is specified between two tables, which makes sure reference from a row in one table to another is valid.

Example:

For a better understanding of referential integrity in DBMS, let us consider a student Andrew, who studies in the computer science department.

Therefore consider two tables:

1. Course Table consisting (Course_id, Course_name)
2. Student Table consisting (Student_id, Student_name, Course_id)

Input :

The above SQL code will create both Course Table and Student Table, where Course_id will be acting as the Foreign key in Student Table. Thus Course_id is the key by which we will be able to refer to Course Table data from Student Table.

Also, you might have noticed that while creating a foreign key we have to specify an ON DELETE Clause. Well, the function of the clause is to alert us whenever a record from the parent table is deleted. Therefore the function of the CASCADE is to delete all the matching row from parent table before deleting them from the child table, thus maintaining data integrity.

Now, if we try to INSERT or ADD SQL statement resulting to change of Course_id value in the student table ( child table of Course table ) and if the value of Course_id is not present in the Course table ( parent table to Student table ) will cause an error.

Let us see how referential integrity throws error while trying to INSERT or DELETE a record in the child table (student table) for which we don't have any matching record in the parent table(department table).

Input :

Output :

When we add the first query on the Course table with (Course_id = 1 and Course_name = 'Computer Science'), the query runs perfectly. But when we try to add (student_id = 123 , student_name = 'Rahul' and Course_id = 6) in Student Table will throw an error, because Course_id = 6 is not present in the department table (parent table) therefore failed to meet the Referential integrity protocol.

But if we modify the query to be

Input :

Output :

Now to check mutual dependency we can delete the record from the department table and see if the record got deleted in the student table or not.

Input :

Output :

Input :

Output :

We can see there is no record in the Student table because of ON DELETE CASCADE, which makes sure the matching records in the child table are deleted. Similarly, you can use ON UPDATE CASCADE to automatically propagate UPDATE from the parent table to child tables.

Advantages of Referential Integrity in DBMS

1. If a record from the parent table is deleted, referential integrity allows deleting all the related records in the child table using cascade-delete functionality. Thus maintaining data authenticity.
2. Similar to the first advantage, if a record is updated in the parent table it will automatically get updated in the child table.
3. Referential integrity prevents inserting records with incorrect details, thus any updation or insertion will fail if it doesn't satisfy the referential integrity rule of DBMS.
4. If the primary key in the Course table is changes then it will automatically get updated in the Student table using the cascading delete. Thus, reducing manual work.
5. Referential integrity duplicate entries in both the tables.
6. Preventing one table from pointing to a null or nonexistential value in another table.
7. Prevents the addition of a record to a child table that contains a foreign key until and unless there is a primary key in the linked parent table.

Use of Referential Integrity in Day to Day life

1. Lack of referential integrity in a database can lead to incomplete data being returned. It could result in customers not receiving products that they paid for.
2. Or Referential Integrity can lead to patients in hospitals not receiving the correct treatment which is prescribed, diaster relief team might not be receiving the correct supplies or information to help the ones in need.
3. Referential Integrity could also result in incomplete data being returned without any indication of error, which could result in records being lost in the database tables because they are never returned in queries or reports.
4. Since random data is not generated, It helps in verifying transactions thus, the money sent from a person A to B should be received by B and not any other random person.

Read More:

• What is Record in DBMS?.
• What is a DBMS Query?.
• Database Testing.