Referential Integrity is laid of constraints applied to unusual fundamental which prevents entering a row inwards little tabular array (where you lot convey unusual key) for which you lot don't convey whatever corresponding row inwards bring upwards 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 convey ii tables Order in addition to Customer where Customer is bring upwards tabular array alongside primary key customer_id in addition to Order is little tabular array alongside unusual fundamental customer_id. Since every bit per draw of piece of work concern rules you lot tin non convey an Order without a Customer in addition to this draw of piece of work concern dominion tin hold upwards implemented using referential integrity inwards SQL on relational database. Referential Integrity volition campaign failure on whatever INSERT or UPDATE SQL disputation changing value of customer_id inwards little table, If value of customer_id is non introduce inwards Customer table. By the means What is Referential Integrity inwards SQL is too an of import SQL query similar to finding mo highest salary inwards SQL or difference betwixt truncate in addition to delete and should hold upwards prepared good earlier going for whatever chore interview, where noesis of SQL is i of the requirement.
Referential Integrity instance inwards MySQL tables:
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;
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 similar a shot unusual fundamental inwards Employee table. In this SQL, piece creating unusual fundamental nosotros convey specified ON DELETE clause which tells, what needs to done when a tape from bring upwards tabular array is deleted. CASCADE referential activeness allows to delete or update all matching rows from little table, afterwards deleting a tape inwards bring upwards table. This means Refrential Integrity save information integrity of relationship.
Let's run into How Referential Integrity disallow INSERT in addition to UPDATE for a tape inwards little tabular array for which at that spot is no matching tape inwards bring upwards table. To banking corporation 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 little row: a FOREIGN KEY constraint fails (`test`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `department` (`dept_id`) ON DELETE CASCADE)
INSERT INTO Employee VALUES (101, "Rajeev", 2)
mysql> INSERT INTO Employee VALUES (101, "Rajeev", 2)
-> ;
ERROR 1452 (23000): Cannot ADD OR UPDATE a little 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 showtime tape inwards Department tabular array it ran fine simply when nosotros insert a tape inwards Employee tabular array alongside dept_id = 2 which is not introduce inwards Department i.e. bring upwards table, failed to Referential integrity or unusual fundamental constraint check.
If you lot alter your query in addition to right dept_id to 1, query volition run fine, every bit shown below
mysql> INSERT INTO Employee VALUES (101, "Rajeev", 1);
Query OK, 1 row affected (0.05 sec)
Query OK, 1 row affected (0.05 sec)
Now let's delete our alone tape from Department tabular array in addition to run into if matching records on little 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)
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM Employee;
Empty SET (0.00 sec)
You run into at that spot is no tape inwards Employee tabular array because of ON DELETE CASCADE, matching records inwards little tabular array is delete. Similarly you lot tin work ON UPDATE CASCADE to automatically propagate UPDATE from bring upwards tabular array to little 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 amid bring upwards in addition to little tables. Here are to a greater extent than or less of the near noticed advantages of Referential Integrity inwards SQL:
1) Referential Integrity prevents inserting records alongside wrong details inwards table. Any insert or update performance volition neglect if it doesn't satisfy referential integrity rule.
2) If a records from bring upwards tabular array is deleted, referential integrity allows to delete all related records from little tabular array using cascade-delete functionality.
3) Similar to mo wages if a tape i.e. customer_id of a client is updated on bring upwards tabular array (Customer table) , Referential Integrity helps to update customer_id inwards little tabular array (Order) using cascade-update.
That's all on What is referential integrity inwards database, SQL in addition to particularly inwards MySQL. We convey seen instance of How referential integrity or unusual fundamental constraint industrial plant inwards MySQL. We convey too seen instance of CASCADE DELETE which automatically delete matching records cast little table.
Further Learning
SQL query to detect all tabular array names from database inwards MySQL