Foreign key ON DELETE behavior in SQL - Step-by-Step Execution

Concept Flow - Foreign key ON DELETE behavior

Delete row in parent table

↓

Check foreign key constraints

↓

ON DELETE CASCADE

↓

Delete matching child rows

↓

ON DELETE RESTRICT

↓

Prevent delete if child rows exist

↓

End

When deleting a row in the parent table, the database checks the foreign key's ON DELETE rule and either deletes child rows, sets their foreign keys to NULL, or prevents the delete.

Execution Sample

SQL

CREATE TABLE parent (
  id INT PRIMARY KEY
);

CREATE TABLE child (
  id INT PRIMARY KEY,
  parent_id INT,
  FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
);

INSERT INTO parent VALUES (1);
INSERT INTO child VALUES (10, 1);
DELETE FROM parent WHERE id = 1;

This code creates two tables with a foreign key ON DELETE CASCADE, inserts data, then deletes a parent row to show cascading delete.

Execution Table

Step	Action	Parent Table State	Child Table State	Result
1	Create parent table	Empty	N/A	Parent table created
2	Create child table with FK ON DELETE CASCADE	Empty	Empty	Child table created with FK constraint
3	Insert (1) into parent	id=1	Empty	Parent row inserted
4	Insert (10,1) into child	id=1	id=10, parent_id=1	Child row inserted
5	Delete parent row where id=1	Empty	id=10, parent_id=1	Delete triggers ON DELETE CASCADE
6	ON DELETE CASCADE: Delete child rows with parent_id=1	Empty	Empty	Child row deleted
7	Final state	Empty	Empty	Parent and child rows deleted

💡 Parent row deleted; ON DELETE CASCADE caused matching child rows to be deleted

Variable Tracker

Table	Start	After Step 3	After Step 4	After Step 5	After Step 6	Final
parent	Empty	id=1	id=1	Empty	Empty	Empty
child	Empty	Empty	id=10, parent_id=1	id=10, parent_id=1	Empty	Empty

Key Moments - 3 Insights

Why does the child row get deleted after deleting the parent row?

Because the foreign key is defined with ON DELETE CASCADE, deleting the parent row automatically deletes matching child rows (see execution_table step 6).

What would happen if ON DELETE SET NULL was used instead?

The child row's foreign key column would be set to NULL instead of deleting the row, preserving the child row but removing the link (not shown in this trace).

Why can't we delete a parent row if ON DELETE RESTRICT is set and child rows exist?

ON DELETE RESTRICT prevents deletion of a parent row if any child rows reference it, so the delete would fail to maintain data integrity.

Visual Quiz - 3 Questions

Look at the execution_table at step 5. What is the state of the child table before ON DELETE CASCADE runs?

A Empty

B id=10, parent_id=NULL

C id=10, parent_id=1

D id=1

💡 Hint

Check the 'Child Table State' column at step 5 in the execution_table.

At which step does the child row get deleted due to ON DELETE CASCADE?

A Step 6

B Step 5

C Step 4

D Step 7

💡 Hint

Look at the 'Action' and 'Result' columns in execution_table rows 5 and 6.

If the foreign key used ON DELETE SET NULL instead, what would be the child table state after deleting the parent row?

A Child rows deleted

B Child rows' parent_id set to NULL

C Child rows unchanged

D Delete prevented

💡 Hint

Refer to key_moments explanation about ON DELETE SET NULL behavior.

Concept Snapshot

Foreign key ON DELETE behavior controls what happens to child rows when a parent row is deleted.
Options include:
- CASCADE: delete child rows automatically
- SET NULL: set child foreign keys to NULL
- RESTRICT: prevent parent delete if child rows exist
Use ON DELETE clause in foreign key definition to specify behavior.

Full Transcript

This visual execution trace shows how the ON DELETE behavior of foreign keys works in SQL. When a parent row is deleted, the database checks the foreign key's ON DELETE rule. If it is CASCADE, matching child rows are deleted automatically. If SET NULL, child foreign keys are set to NULL. If RESTRICT, the delete is prevented if child rows exist. The example creates parent and child tables with ON DELETE CASCADE, inserts rows, then deletes the parent row. The child row is deleted automatically due to the cascade. The execution table tracks each step's table states and actions. Key moments clarify common confusions about why child rows are deleted or not. The quiz tests understanding of the execution steps and ON DELETE options.