CASCADE delete preview in SQL - Step-by-Step Execution

Concept Flow - CASCADE delete preview

Delete row in Parent Table

↓

Check for Foreign Key with CASCADE

↓

Find matching rows in Child Table

↓

Delete matching rows in Child Table

↓

Repeat cascade if Child has further CASCADE FKs

↓

Finish

When you delete a row in a parent table, the database automatically deletes related rows in child tables that have CASCADE delete set on their foreign keys.

Execution Sample

SQL

DELETE FROM departments WHERE dept_id = 10;

Deletes department with id 10 and automatically deletes employees linked to it due to CASCADE.

Execution Table

Step	Action	Table	Rows Affected	Notes
1	Delete department with dept_id=10	departments	1	Parent row targeted for deletion
2	Check foreign keys with CASCADE	employees	N/A	employees.dept_id FK with CASCADE found
3	Delete employees with dept_id=10	employees	3	Child rows deleted automatically
4	Check further CASCADE FKs	N/A	N/A	No further CASCADE relationships
5	Finish deletion	N/A	N/A	All related rows deleted

💡 No more CASCADE foreign keys to process, deletion complete

Variable Tracker

Variable	Start	After Step 1	After Step 3	Final
departments_rows	5	4	4	4
employees_rows	10	10	7	7

Key Moments - 3 Insights

Why do employees get deleted when we delete a department?

Because the foreign key from employees to departments has CASCADE delete, so deleting a department automatically deletes employees linked to it (see execution_table step 3).

What happens if there are no CASCADE foreign keys?

Only the parent row is deleted; child rows remain untouched (would be shown in execution_table step 2 with no CASCADE found).

Can CASCADE delete cause multiple levels of deletions?

Yes, if child tables also have CASCADE foreign keys, deletions cascade further (execution_table step 4 checks for this).

Visual Quiz - 3 Questions

Look at the execution table, how many employee rows are deleted due to CASCADE?

A 3

B 1

C 5

D 0

💡 Hint

Check the 'Rows Affected' column for employees in step 3.

At which step does the database check for further CASCADE foreign keys?

A Step 1

B Step 2

C Step 4

D Step 5

💡 Hint

Look at the 'Action' column describing checking further CASCADE FKs.

If the employees table had no CASCADE foreign key, what would change in the execution table?

A Step 3 would delete employees anyway

B Step 2 would find no CASCADE FK and step 3 would be skipped

C Step 4 would delete employees

D Step 1 would delete employees

💡 Hint

Refer to step 2 and 3 where CASCADE FK presence controls deletion.

Concept Snapshot

CASCADE delete automatically removes child rows when a parent row is deleted.
Defined by foreign key constraints with ON DELETE CASCADE.
Prevents orphaned child rows.
Can cascade through multiple related tables.
Useful for maintaining data integrity easily.

Full Transcript

When you delete a row in a parent table, the database looks for foreign keys in child tables that have the CASCADE delete option. It then deletes all matching child rows automatically. This process repeats if those child tables have further CASCADE foreign keys. The execution table shows deleting a department row, then deleting employees linked to it. Variables track how many rows remain after each step. Key moments clarify why child rows delete and how cascading can go multiple levels. The quiz tests understanding of these steps and effects. CASCADE delete helps keep data consistent by cleaning up related rows automatically.