ON DELETE CASCADE

noun

say it: “on dih-LEET kas-KAYD” /ɑn dɪˈlit kæsˈkeɪd/

A rule on a SQL foreign key that deletes the rows pointing at a row whenever that row is deleted.

What does ON DELETE CASCADE do?

ON DELETE CASCADE is a rule on a foreign key: when a row is deleted, the rows pointing at it are deleted too. It fits rows that mean nothing alone, such as a vendor's products. Without it, deleting a vendor that still has products is refused. (SQLite checks foreign keys only after PRAGMA foreign_keys = ON.)

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    vendor_id INTEGER NOT NULL REFERENCES vendors (id) ON DELETE CASCADE
);

DELETE FROM vendors WHERE id = 1;   -- vendor 1's products are deleted with it

In a sentence

  • “Products mean nothing without their vendor, so the products table's foreign key uses ON DELETE CASCADE.”
  • “With ON DELETE CASCADE on vendor_id, removing Hilltop Honey removed its three products too.”

Level: Journeyman · More SQL terms at this level

Updated