What Is Referential Integrity Inward Database Or Sql - Mysql Event Tutorial
Referential Integrity is laid of constraints applied to unusual telephone substitution which prevents entering a row inward youngster tabular array (where you lot accept unusual key) for which you lot don't accept whatever corresponding row inward raise 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 accept ii tables Order in addition to Customer where Customer is raise tabular array with primary key customer_id in addition to Order is youngster tabular array with unusual telephone substitution customer_id. Since equally per delineate of piece of occupation concern rules you lot tin hand notice non accept an Order without a Customer in addition to this delineate of piece of occupation concern dominion tin hand notice last implemented using referential integrity inward SQL on relational database. Referential Integrity volition drive failure on whatever INSERT or UPDATE SQL contention changing value of customer_id inward youngster table, If value of customer_id is non acquaint inward Customer table. By the means What is Referential Integrity inward SQL is equally good an of import SQL query like to finding minute highest salary inward SQL or difference betwixt truncate in addition to delete and should last prepared good earlier going for whatever chore interview, where noesis of SQL is i of the requirement.
Referential Integrity instance inward 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 right away unusual telephone substitution inward Employee table. In this SQL, piece creating unusual telephone substitution nosotros accept specified ON DELETE clause which tells, what needs to done when a tape from raise tabular array is deleted. CASCADE referential activeness allows to delete or update all matching rows from youngster table, later on deleting a tape inward raise table. This means Refrential Integrity save information integrity of relationship.
Let's come across How Referential Integrity disallow INSERT in addition to UPDATE for a tape inward youngster tabular array for which in that place is no matching tape inward raise table. To banking firm 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 youngster 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 youngster 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 commencement tape inward Department tabular array it ran fine simply when nosotros insert a tape inward Employee tabular array with dept_id = 2 which is not acquaint inward Department i.e. raise 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)
Query OK, 1 row affected (0.05 sec)
Now let's delete our solely tape from Department tabular array in addition to come across if matching records on youngster 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 come across in that place is no tape inward Employee tabular array because of ON DELETE CASCADE, matching records inward youngster tabular array is delete. Similarly you lot tin hand notice utilization ON UPDATE CASCADE to automatically propagate UPDATE from raise tabular array to youngster tables.
Advantage of Referential Integrity inward RDBMS in addition to SQL
There are several practice goodness of Referential Integrity inward relational database in addition to maintaining integrity of information alongside raise in addition to youngster tables. Here are approximately of the close noticed advantages of Referential Integrity inward SQL:
1) Referential Integrity prevents inserting records with wrong details inward table. Any insert or update performance volition neglect if it doesn't satisfy referential integrity rule.
2) If a records from raise tabular array is deleted, referential integrity allows to delete all related records from youngster tabular array using cascade-delete functionality.
3) Similar to minute payoff if a tape i.e. customer_id of a client is updated on raise tabular array (Customer table) , Referential Integrity helps to update customer_id inward youngster tabular array (Order) using cascade-update.
That's all on What is referential integrity inward database, SQL in addition to peculiarly inward MySQL. We accept seen instance of How referential integrity or unusual telephone substitution constraint plant inward MySQL. We accept equally good seen instance of CASCADE DELETE which automatically delete matching records cast youngster table.
Further Learning
SQL query to notice all tabular array names from database inward MySQL
0 Response to "What Is Referential Integrity Inward Database Or Sql - Mysql Event Tutorial"
Post a Comment