In this post , Let us learn Referential integrity in SQL .
What is Referential integrity ?
Referential integrity in SQL requires that a foreign key must have a matching primary key or it must be null. This constraint is specified between parent and child table.It actually maintains the correspondence between rows in these tables.
What is Primary key ?
The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).
program
CREATE TABLE Emp_detail (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);What is Foreign key?
Foreign key is used to maintain relationship between two tables. Primary of a table act as foreign key in the other table.
program
CREATE TABLE Emp_salary (
Dep_id int,
ID int NOT NULL,
Salary int,
PRIMARY KEY (Dep_id),
FOREIGN KEY (ID) REFERENCES Emp_detail(ID)
);In this way, the referential integrity makes parent table getting co-related with child table .
Rules of Referential integrity
We cant add a record to the table that contains the foreign key unless there is a corresponding record in the linked table.
cascading update and cascading delete are the other new techniques . This ensures that changes made to the linked table gets reflected in the primary table.
Benefits of Referential Integrity
- Restricts the entry of duplicate data
- Avoids one table from pointing to a nonexistent field in another table
- Prevents the deletion of a record that contains a value referred to by a foreign key in another table
- Prevents for the addition of a record to a table that contains a foreign key unless there is a primary key in the linked table
Reference
https://towardsdatascience.com/common-issues-founded-with-referential-integrity-fc05e93693a1