Referential integrity in SQL

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