Referential Integrity

📘 What is Referential Integrity in DBMS?

Referential Integrity is a rule in relational databases that ensures relationships between tables remain valid and consistent.

In simple terms: If one table references another, the referenced data must exist — or the relationship is broken, and that’s not allowed.

---

🧠 Real-Life Analogy:

Imagine an employee record says:

Department_ID = 5

But there’s no department with ID 5 in the departments table.

That’s a referential integrity violation — like pointing to a door that doesn’t exist.

---

🔗 Referential Integrity in SQL:

It’s enforced using Foreign Keys.

🔧 Example:

-- Parent table
CREATE TABLE Departments (
  dept_id INT PRIMARY KEY,
  name VARCHAR(100)
);
 
-- Child table
CREATE TABLE Employees (
  emp_id INT PRIMARY KEY,
  name VARCHAR(100),
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);

🔐 What this means:

• You cannot insert an employee with dept_id = 5 unless Departments has that ID.

• You cannot delete a department if employees are still referencing it — unless you handle it explicitly (like cascade delete).

---

⚠️ Referential Integrity Violations (and how to prevent them):

Violation	Fix
Inserting a child row with no parent	Reject the insert or create the missing parent first
Deleting a parent that’s referenced	Restrict the delete, or use ON DELETE CASCADE
Updating parent ID without syncing	Restrict or cascade update

---

🔧 ON DELETE / ON UPDATE options in SQL:

FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
ON DELETE CASCADE
ON UPDATE CASCADE

Option	What It Does
CASCADE	Automatically deletes or updates child rows when parent changes
SET NULL	Sets child foreign key to NULL when parent is deleted/updated
RESTRICT / NO ACTION	Blocks the operation if it would violate integrity

---

🔍 Why It’s Important:

Reason	Benefit
✅ Prevents broken references	Ensures data is always valid
✅ Enforces logical relationships	Keeps the DB consistent
✅ Simplifies code	DBMS handles the rules automatically

---

🧠 Interview Line:

“Referential Integrity ensures that a foreign key in one table always refers to a valid primary key in another, maintaining consistency across related tables.”