What Is Referential Integrity Inwards Database Or Sql - Mysql Event Tutorial

Referential Integrity is laid of constraints applied to unusual telephone substitution which prevents entering a row inwards small-scale tabular array (where you lot bring unusual key) for which you lot don't bring whatever corresponding row inwards rear tabular array i.e. entering NULL or invalid unusual keys. Referential Integrity prevents your tabular array from having  incorrect or incomplete human relationship e.g. If you lot bring 2 tables Order in addition to Customer where Customer is rear tabular array with primary key customer_id in addition to Order is small-scale tabular array with unusual telephone substitution customer_id. Since equally per trouble concern rules you lot tin non bring an Order without a Customer in addition to this trouble concern dominion tin endure implemented using referential integrity inwards SQL on relational database. Referential Integrity volition drive failure on whatever INSERT or UPDATE SQL disceptation changing value of customer_id inwards small-scale table, If value of customer_id is non introduce inwards Customer table. By the agency What is Referential Integrity inwards SQL is likewise an of import SQL enquiry similar to finding minute highest salary inwards SQL or difference betwixt truncate in addition to delete  and should endure prepared good earlier going for whatever project interview, where noesis of SQL is i of the requirement.

Referential Integrity instance inwards MySQL tables:

Another instance of Referential Integrity is Employee in addition to Department relationship. If nosotros bring dept_id equally unusual telephone substitution inwards Employee tabular array than past times using referential integrity constraints nosotros tin avoid creating  Employee without subdivision or non existing department. In curt Referential Integrity makes primary telephone substitution unusual telephone substitution human relationship viable. Let's outset create Employee in addition to Department tabular array with primary key, unusual telephone substitution in addition to 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 volition create both Department in addition to Employee table. dept_id is forthwith unusual telephone substitution inwards Employee table. In this SQL, piece creating unusual telephone substitution nosotros bring specified ON DELETE clause which tells, what needs to done when a tape from rear tabular array is deleted. CASCADE referential activity allows to delete or update all matching rows from small-scale table, afterwards deleting a tape inwards rear table. This agency Refrential Integrity save information integrity of relationship.

Let's encounter How Referential Integrity disallow INSERT in addition to UPDATE for a tape inwards small-scale tabular array for which at that spot is no matching tape inwards rear table. To depository fiscal establishment check this Refrential Integrity instance execute next 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 small-scale row: a FOREIGN KEY constraint fails (`test`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `department` (`dept_id`) ON DELETE CASCADE)

When nosotros inserted outset tape inwards Department tabular array it ran fine simply when nosotros insert a tape inwards Employee tabular array with dept_id = 2 which is not introduce inwards Department i.e. rear table, failed to Referential integrity or unusual telephone substitution constraint check.

If you lot modify your query in addition to right dept_id to 1, query volition run fine, equally shown below

mysql> INSERT INTO Employee VALUES (101, "Rajeev", 1);
Query OK, 1 row affected (0.05 sec)

Now let's delete our exclusively tape from Department tabular array in addition to encounter if matching records on small-scale tabular array 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 encounter at that spot is no tape inwards Employee tabular array because of ON DELETE CASCADE, matching records inwards small-scale tabular array is delete. Similarly you lot tin utilization ON UPDATE CASCADE to automatically propagate UPDATE from rear tabular array to small-scale tables.

Advantage of Referential Integrity inwards RDBMS in addition to SQL

There are several exercise goodness of Referential Integrity inwards relational database in addition to maintaining integrity of information alongside rear in addition to small-scale tables. Here are to a greater extent than or less of the almost noticed advantages of Referential Integrity inwards SQL:

1) Referential Integrity prevents inserting records with wrong details inwards table. Any insert or update performance volition neglect if it doesn't satisfy referential integrity rule.

2) If a records from rear tabular array is deleted, referential integrity allows to delete all related records from small-scale tabular array using cascade-delete functionality.

3) Similar to minute wages if a tape i.e. customer_id of a client is updated on rear tabular array (Customer table) , Referential Integrity helps to update customer_id inwards small-scale tabular array (Order) using cascade-update.

That's all on What is referential integrity inwards database, SQL in addition to particularly inwards MySQL. We bring seen instance of How referential integrity or unusual telephone substitution constraint industrial plant inwards MySQL. We bring likewise seen instance of CASCADE DELETE which automatically delete matching records cast small-scale table.

Further Learning
SQL query to detect all tabular array names from database inwards MySQL

How to bring together 3 tables inwards i SQL query with Example

How to re-create i tabular array into to a greater extent than or less other inwards SQL

How to split upwards String inwards stored physical care for – Sybase in addition to SQL Server example

How to create motorcar incremented identity column inwards Oracle, MySQL, SQL Server in addition to Sybase database

What is subquery inwards Java with example