5. Referencial Integrity

Simple Explanation:

If one table (child) refers to another table’s (parent) primary key using a foreign key, referential integrity ensures that:

• You cannot insert a value in the child table that doesn't exist in the parent table.
• You cannot delete a row from the parent table if the child table still references it (unless ON DELETE rules are set).

Example:

Parent table:

CREATE TABLE Departments (
  dept_id INT PRIMARY KEY,
  dept_name VARCHAR(100)
);

Child table:

CREATE TABLE Employees (
  emp_id INT PRIMARY KEY,
  emp_name VARCHAR(100),
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);

What Referential Integrity Does Here:

• Insert: You cannot insert an employee with dept_id = 10 unless that department exists in Departments.
• Delete: You cannot delete a department if employees are still linked to it, unless you use ON DELETE CASCADE.

Options to Handle It:

• ON DELETE CASCADE: Delete child rows automatically.
• ON DELETE SET NULL: Set foreign key to NULL in child when parent is deleted.
• Default: Block deletion or update if linked.
  Referential integrity prevents data inconsistency across related tables.