ON DELETE — Cascade, SET NULL, RESTRICT

~12 min · foreign-keys, cascade, constraints

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

What happens to children when the parent dies

When you declare a foreign key, you can specify the referential action SQLite takes when the parent row is deleted or updated. The four meaningful options:

NO ACTION (default) — reject the delete/update if it would orphan child rows.
RESTRICT — same as NO ACTION but enforced earlier (immediate, not deferred).
CASCADE — delete (or update) the child rows along with the parent.
SET NULL — null out the child's FK column. Requires the column to be nullable.
SET DEFAULT — replace with the column's default value.

Warning: ON DELETE CASCADE on a parent table can wipe huge subtrees in one statement. That's the point — but it also means a typoed DELETE can do enormous damage. For high-stakes parent tables, prefer NO ACTION + an explicit cleanup query, or wrap the delete in a transaction with a safety check.

Code

Three referential actions in one example·sql

CREATE TABLE conversations (id INTEGER PRIMARY KEY, title TEXT) STRICT;

CREATE TABLE messages (
  id INTEGER PRIMARY KEY,
  conversation_id INTEGER NOT NULL
    REFERENCES conversations(id) ON DELETE CASCADE,   -- delete msgs with conv
  role TEXT NOT NULL, content TEXT NOT NULL
) STRICT;

CREATE TABLE attachments (
  id INTEGER PRIMARY KEY,
  message_id INTEGER
    REFERENCES messages(id) ON DELETE SET NULL,        -- keep attachment, lose link
  url TEXT NOT NULL
) STRICT;

CREATE TABLE summaries (
  id INTEGER PRIMARY KEY,
  conversation_id INTEGER NOT NULL
    REFERENCES conversations(id) ON DELETE RESTRICT    -- block delete if summary exists
) STRICT;

External links

Foreign Key Actions

Exercise

Build the three-table example above. Insert sample rows. Then try (1) deleting a conversation that has messages — observe CASCADE, (2) deleting a message that has attachments — observe SET NULL, (3) deleting a conversation that has a summary — observe RESTRICT block. For each, note whether you'd actually want this action in a real product.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousCardinality — One-to-Many and Many-to-Many Next →Transactions — BEGIN, COMMIT, ROLLBACK

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.