What is Referential Integrity in Database or SQL - MySQL Example Tutorial

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.

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 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 :

INSERT INTO
Department VALUES (1, "Sales");

INSERT INTO
Employee VALUES (101, "Rajeev", 2)



mysql> INSERT
INTO Employee VALUES (101, "Rajeev",
2)

    -> ;

ERROR 1452 (23000): Cannot ADD OR
UPDATE a child row: a FOREIGN KEY
constraint fails (`test`.`employee`, CONSTRAINT `employee_ibfk_1`
FOREIGN KEY
(`dept_id`) REFERENCES
`department` (`dept_id`) ON DELETE
CASCADE)

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

mysql> INSERT INTO
Employee VALUES (101, "Rajeev", 1);

Query OK, 1
row affected (0.05
sec)

Now let's delete our only record from Department table and
see if matching records on child table is automatically deleted or not.

mysql> DELETE FROM
Department;

Query OK, 1
row affected (0.05
sec)



mysql> SELECT
* FROM
Employee;

Empty SET (0.00 sec)

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

SQL
query to find all table names from database in MySQL

How
to join three tables in one SQL query with Example

How
to copy one table into another in SQL

How
to split String in stored procedure – Sybase and SQL Server example

How
to create auto incremented identity column in Oracle, MySQL, SQL Server and Sybase
database

What
is subquery in Java with example

Source:http://javarevisited.blogspot.com/2012/12/what-is-referential-integrity-in-database-sql-mysql-example-tutorial.html