Cascading Actions

Cascading actions define what happens to child rows when parent rows are deleted or updated.

Choose actions based on business rules, not convenience.

Core Concepts

flowchart TD
  A[Parent row changes] --> B{FK action}
  B -->|CASCADE| C[Apply change to children]
  B -->|SET NULL| D[Child FK becomes NULL]
  B -->|RESTRICT / NO ACTION| E[Block parent change if children exist]

Action	Typical meaning
ON DELETE CASCADE	Delete child rows automatically
ON DELETE SET NULL	Keep child rows, clear reference
ON DELETE RESTRICT	Prevent parent delete when children exist
ON UPDATE CASCADE	Propagate parent key updates to children

Delete Cascade

CREATE TABLE parents_c (
  parent_id INTEGER PRIMARY KEY,
  name      TEXT NOT NULL
);

CREATE TABLE children_c (
  child_id   INTEGER PRIMARY KEY,
  parent_id  INTEGER,
  label      TEXT NOT NULL,
  FOREIGN KEY (parent_id)
    REFERENCES parents_c(parent_id)
    ON DELETE CASCADE
);

Expected output
Tables created; deleting a parent will delete matching children.

Set Null

CREATE TABLE parents_n (
  parent_id INTEGER PRIMARY KEY,
  name      TEXT NOT NULL
);

CREATE TABLE children_n (
  child_id   INTEGER PRIMARY KEY,
  parent_id  INTEGER,
  label      TEXT NOT NULL,
  FOREIGN KEY (parent_id)
    REFERENCES parents_n(parent_id)
    ON DELETE SET NULL
);

Expected output
Tables created; deleting a parent nulls child parent_id.

Code Examples

-- Demonstrate ON DELETE CASCADE behavior.
INSERT INTO parents_c (parent_id, name)
VALUES (1, 'Parent A');

INSERT INTO children_c (child_id, parent_id, label)
VALUES (10, 1, 'Child X'), (11, 1, 'Child Y');

DELETE FROM parents_c
WHERE parent_id = 1;

Expected output
Parent row deleted and its child rows deleted automatically.

-- Verify child table is now empty.
SELECT count(*) AS child_rows
FROM children_c;

child_rows
0

SQLite-Specific Nuances

SQLite Nuance

Cascading actions only execute when foreign key enforcement is enabled (PRAGMA foreign_keys = ON).

Common Pitfalls / Best Practices

Pitfall

Using CASCADE without understanding data-loss impact, then deleting a parent row and unexpectedly removing large child datasets.

Best Practice

Choose delete behavior explicitly per relationship and document the reason (CASCADE, SET NULL, or RESTRICT).

Quick Challenge

For a blogging app, comments should disappear when their post is deleted. Which foreign key action fits best?

View Solution

Use ON DELETE CASCADE on comments.post_id referencing posts.post_id.

FOREIGN KEY (post_id) REFERENCES posts(post_id) ON DELETE CASCADE

Why
Comments are dependent data and should not remain orphaned.