Database Management System (DBMS) / SQL(STRUCTURED QUERY LANGUAGE)

ON DELETE CASCADE / ON DELETE SET NULL

Both are used to maintain data consistency when deleting rows from a parent table (with a primary key) that are referenced in a child table (with a foreign key).

🔗 Problem: Referential Integrity Violation

Imagine you have:

📦 students table → parent (with student_id as PRIMARY KEY)

📘 enrollments table → child (with student_id as FOREIGN KEY)

❌ If you delete a student who is still referenced in enrollments, it causes a referential integrity error — unless you handle it using ON DELETE actions.

✅ Solution 1: ON DELETE CASCADE

Automatically deletes the matching child rows when a parent row is deleted.

📌 Example:

CREATE TABLE enrollments (
  student_id INT,
  course_id INT,
  FOREIGN KEY (student_id) REFERENCES students(student_id)
  ON DELETE CASCADE
);

🗑️ If a student is deleted from students, all their enrollments are automatically deleted too.

✔️ Ensures no orphan rows

✔️ Maintains clean data automatically

✅ Solution 2: ON DELETE SET NULL

Sets the foreign key to NULL in the child table when the parent row is deleted.

📌 Example:

CREATE TABLE enrollments (
  student_id INT,
  course_id INT,
  FOREIGN KEY (student_id) REFERENCES students(student_id)
  ON DELETE SET NULL
);

🗑️ If a student is deleted from students, their student_id in enrollments becomes NULL.

✔️ Keeps the row in child table

✔️ Use this only when student_id allows NULL

⚠️ Comparison

Option

What It Does

Use When...

🔗 ON DELETE CASCADE

Deletes child rows automatically

You want to delete all related records

🕳️ ON DELETE SET NULL

Sets FK to NULL in child rows

You want to keep child rows but break link

❌ Default (No action)

Error if parent is deleted while child exists

You want to prevent accidental data loss

🧠 Summary

  • These are referential integrity options added in the FOREIGN KEY clause

  • They prevent errors and help cleanly manage dependent data