We can define different types of Referential Integrity scenarios that could be applied during such cases. Then all the referencing rows in the Employee table would be impacted. Simply stated, Referential Integrity refers to the Action that we expect from the database engine to take, when an UPDATE or DELETE happens in the referenced table that contains the FOREIGN KEY.įor example, in our Employee/Department example, suppose we change the Department ID for a certain row in DB. Referential Integrity helps to maintain data in a clean and consistent state where there are tables related to each other with a FOREIGN KEY relationship. Let’s first try to understand what exactly is Referential Integrity. So, broadly, when FOREIGN KEY References are defined, it is important to ensure that the table that is being referenced should have data before it’s being referred. `depIdFk` FOREIGN KEY (`deptId`) REFERENCES `department` (`departmentId`) Cannot add or update a child row: a foreign keyĬonstraint fails (`my_sql_foreign_key`.`employee`, CONSTRAINT In this scenario, we will get an error like below:Įrror Code: 1452.INSERT INTO EMPLOYEE VALUES (1, 'DARREN JOHNSON', 'CHICAGO', 32, '',10) Now reference a value for departmentId which is not existing.įor example, in the below query statement, we are creating an Employee with a non-existing departmentId -10.You will see that both the statements would get executed without errors. INSERT INTO EMPLOYEE VALUES (1, 'AMIT KUMAR', 'MUMBAI', 32, '',1) INSERT INTO department VALUES (1, 'ENGINEERING') Create a record in the Department table first and add a record in the Employee table referencing the ID of the record that was added to the Department table.Let’s try inserting data into these tables and see how FOREIGN KEY CONSTRAINT works. ![]() What this essentially means that deptId column in the Employee table can contain only values that are in the Department table. ![]() REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE) Īs you can see, in the above Employee table, we have declared deptId column of type Int and defined FOREIGN KEY from the Department table on column departmentId. INT (Foreign Key) referenced from department tableĬREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100),Īddress VARCHAR(100), age INT, dob DATE, deptId INT, CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL, departmentName VARCHAR(100)) Ĭreate a table Employee with columns as below: We will create a table Department with columns – departmentId (int & PRIMARY KEY) and departmentName (varchar). Let’s see an example of a FOREIGN KEY reference using the Employee/Department example. We would learn about different referential integrity actions later in this tutorial. ![]() Both UPDATE and DELETE can have the same or different reference options.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |