Jahangirnagar University — Department of CSE — Admission: 2016 (PMSCS Program)প্রশ্ন ও সমাধান

Referential Integrity (RI) is the rule that a foreign key value in one table must either be NULL or match an existing primary key value in the referenced table — it guarantees relationships between tables always point to something real.

erDiagram
    DEPARTMENT ||--o{ EMPLOYEE : employs

CREATE TABLE Department (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

CREATE TABLE Employee (
    emp_id  INT PRIMARY KEY,
    name    VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);

• With this constraint in place, an Employee row cannot reference a dept_id that doesn't exist in Department — the database rejects such an INSERT/UPDATE outright.
• RI also governs what happens on deletion of a referenced row, via explicit rules:

Rule	Effect when the referenced Department row is deleted
ON DELETE RESTRICT (default)	Deletion is blocked while employees still reference it
ON DELETE CASCADE	All employees in that department are deleted too
ON DELETE SET NULL	Employees' dept_id is set to NULL

Why it matters: without RI, a database could end up with orphaned rows — e.g. an Employee pointing at dept_id = 99 when Department 99 has been deleted — silently corrupting the data's logical meaning and breaking any JOIN that relies on that relationship.

Exam takeaway: Referential Integrity = "every foreign key value must exist as a primary key in the referenced table (or be NULL)" — enforced automatically once a FOREIGN KEY constraint is declared, and its ON DELETE/ON UPDATE clauses decide how to handle the referenced row's removal.