Database.Middle.How can you enforce referential integrity?

✅ What Is Referential Integrity?

It’s the concept that:

• A foreign key must refer to a valid, existing primary key in the parent table.
• If a referenced record is deleted or updated, the database must handle the dependent records properly.

🔧 How to Enforce Referential Integrity (SQL / Relational Databases)

1. Use Foreign Key Constraints

Define foreign keys when creating or altering a table.

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

This ensures every department_id in employees exists in departments.

2. Specify ON DELETE and ON UPDATE Rules

These options determine what happens to dependent rows if a parent row is deleted or updated:

✅ ON DELETE / ON UPDATE actions:

Action	Behavior
CASCADE	Automatically deletes/updates child rows when parent is deleted/updated.
SET NULL	Sets foreign key to NULL when parent is deleted/updated.
SET DEFAULT	Sets foreign key to its default value.
RESTRICT	Prevents delete/update if any child rows exist (fails immediately).
NO ACTION	Similar to RESTRICT, but checks after statement execution.

Example:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id)
        REFERENCES departments(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Now:

• Deleting a department deletes all its employees.
• Updating a department’s id updates it in employees.

🧠 Referential Integrity in ORMs (e.g., JPA/Hibernate)

In code-first setups, you’d define relationships like:

@Entity
public class Employee {
    @ManyToOne
    @JoinColumn(name = "department_id", nullable = false)
    private Department department;
}

The ORM will generate foreign keys and constraints for you.

🔍 How to Check for Referential Integrity Issues

1. Use database tools (like DataGrip or pgAdmin).
2. Run queries to find orphaned records:

SELECT *
FROM employees
WHERE department_id NOT IN (SELECT id FROM departments);

✅ Summary

Method	Purpose
FOREIGN KEY constraint	Enforces valid references to parent keys
ON DELETE / ON UPDATE	Controls cascading behavior
Validation queries	Detect integrity violations
ORM Annotations	Code-level integrity via entities