Referential Integrity is set of constraints applied to foreign key which prevents entering a row in child table (where you have foreign key) for which you don't have any corresponding row in parent table i.e. entering NULL or invalid foreign keys. Referential Integrity prevents your table from having incorrect or incomplete relationship e.g. If you have two tables Order and Customer where Customer is parent table with primary key customer_id and Order is child table with foreign key customer_id. Since as per business rules you can not have an Order without a Customer and this business rule can be implemented using referential integrity in SQL on relational database. Referential Integrity will cause failure on any INSERT or UPDATE SQL statement changing value of customer_id in child table, If value of customer_id is not present in Customer table. By the way What is Referential Integrity in SQL is also an important SQL question similar to finding second highest salary in SQL or difference between truncate and delete and should be prepared well before going for any job interview, where knowledge of SQL is one of the requirement.
Referential Integrity example in MySQL tables:Another example of Referential Integrity is Employee and Department relationship. 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.
Above SQL statements will create both Department and 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 Refrential Integrity preserve data integrity of relationship.
Let's see How Referential Integrity disallow INSERT and UPDATE for a record in child table for which there is no matching record in parent table. To check this Refrential Integrity example execute following MySQL queries :
When we inserted first record in Department table it ran fine but when we insert a record in Employee table with dept_id = 2 which is not present in Department i.e. parent table, failed to Referential integrity or foreign key constraint check.
If you modify your query and correct dept_id to 1, query will run fine, as shown below
Now let's delete our only record from Department table and see if matching records on child table is automatically deleted or not.
You see there is no record in Employee table because of ON DELETE CASCADE, matching records in child table is delete. Similarly you can use ON UPDATE CASCADE to automatically propagate UPDATE from parent table to child tables.
Advantage of Referential Integrity in RDBMS and SQL
There are several benefit of Referential Integrity in relational database and maintaining integrity of data among parent and child tables. Here are some of the most noticed advantages of Referential Integrity in SQL:
1) Referential Integrity prevents inserting records with incorrect details in table. Any insert or update operation will fail if it doesn't satisfy referential integrity rule.
2) If a records from parent table is deleted, referential integrity allows to delete all related records from child table using cascade-delete functionality.
3) Similar to second advantage if a record i.e. customer_id of a customer is updated on parent table (Customer table) , Referential Integrity helps to update customer_id in child table (Order) using cascade-update.
That's all on What is referential integrity in database, SQL and especially in MySQL. We have seen example of How referential integrity or foreign key constraint works in MySQL. We have also seen example of CASCADE DELETE which automatically delete matching records form child table.
Other SQL and Database tutorials from Javarevisited Blog