MySQL + SQL · Lesson 20
Referential Integrity in DBMS
What is Referential Integrity?
Referential integrity ensures a foreign key value always matches an existing primary key in the related table (or is NULL). It stops "orphan" rows.
Example
CREATE TABLE fees (
fee_id INT PRIMARY KEY,
roll_no INT,
FOREIGN KEY (roll_no) REFERENCES students(roll_no)
ON DELETE CASCADE
);You cannot add a fee for a roll_no that does not exist in students. ON DELETE CASCADE auto-removes fees if the student is deleted.
ON DELETE / ON UPDATE
- CASCADE — change/delete children too.
- SET NULL — set the foreign key to NULL.
- RESTRICT — block the action if children exist.
Summary
- Referential integrity keeps foreign keys pointing to valid rows.
- ON DELETE CASCADE/SET NULL/RESTRICT control what happens to children.