What does ON DELETE CASCADE do, and when is it right versus dangerous?
It means that deleting a parent row deletes the rows that point at it, without you asking for it.
PRAGMA foreign_keys = ON;
CREATE TABLE books (
id INTEGER PRIMARY KEY,
title VARCHAR(200) NOT NULL
);
CREATE TABLE book_reviews (
id INTEGER PRIMARY KEY,
book_id INTEGER NOT NULL,
review TEXT NOT NULL,
FOREIGN KEY (book_id) REFERENCES books (id) ON DELETE CASCADE
);
So when is it right? When the child row means nothing at all without the parent. A review of a book that does not exist is not a review of anything, so removing the book should remove the reviews with it.
And when is it dangerous? When the child is something you care about on its own. If a customer has ten thousand orders, then a single delete of the customer takes ten thousand rows with it, and you may not have meant that at all.
safe to cascade dangerous to cascade
------------------------ ----------------------------------------
order lines of an order orders of a customer
reviews of a book tickets of a support agent
rows of a link table invoices of an account
There is one more thing to know, and it is about what happens when the delete is part of a bigger operation. If you delete the parent and then the same statement inserts a new one, the cascade takes the children with it, and the children are gone for good. There are engines that handle this better than others, so it is worth testing the shape that you are about to rely on.
The next question in this topic shows exactly which rows a cascade removes.