Cascading

When we design relational databases with foreign keys, we can define what happens to child records when the parent record is updated or deleted.

This is done using:

• ON DELETE CASCADE → If a parent row is deleted, all child rows that reference it are also deleted automatically.
• ON UPDATE CASCADE → If a parent key changes (usually the primary key), all child foreign key references are updated automatically.

Other options exist too (like SET NULL, SET DEFAULT, RESTRICT, NO ACTION), but we’ll focus on CASCADE.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_code VARCHAR(10),
    FOREIGN KEY (student_id) REFERENCES students(student_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Cascading Deletes (Integrity + Transactions)

If a student is deleted, all their enrollment records should also be removed to avoid “orphan” rows.

Example:

INSERT INTO students VALUES (101, 'Alice'), (102, 'Bob');
INSERT INTO enrollments VALUES (1, 101, 'CS101'), (2, 101, 'MATH201'), (3, 102, 'CS101');

Now delete Alice:

BEGIN TRANSACTION;
DELETE FROM students WHERE student_id = 101;
COMMIT;

Because of ON DELETE CASCADE:

• Alice (student_id = 101) is deleted from students.
• Her enrollments (1, 2) are automatically deleted from enrollments.
• Bob’s enrollment (3) remains unaffected.

This prevents dangling enrollments for a student who doesn’t exist.

Cascading Updates (Integrity + Transactions)

If the primary key of a parent row changes, the foreign key references in child rows are updated automatically.

Example:

BEGIN TRANSACTION;
UPDATE students
SET student_id = 201
WHERE student_id = 102;
COMMIT;

Because of ON UPDATE CASCADE:

• Student id 102 changes to 201 in students.
• In enrollments, the row (3, 102, 'CS101') is automatically updated to (3, 201, 'CS101').

Ensures child rows stay consistent with parent rows.

Cascading and ACID Transactions

Now let’s connect this with transactions:

• Atomicity: If the parent delete/update fails, the cascaded changes are rolled back too.
• Consistency: Referential integrity is preserved because no child is left orphaned.
• Isolation: If two users modify the same parent, cascades won’t conflict (depending on isolation level).
• Durability: Once committed, all cascaded changes are permanent.

Why Cascading is Useful

• Prevents orphan records automatically.
• Reduces the need for manual cleanup logic in application code.
• Keeps relationships consistent across multiple tables.