DELETE (and Why Rows Rarely Truly Die)

A leftover is sitting in the bookings table: booking 501, seat G7, made by Lora herself while she was checking that bookings work at all. It is test data, it means nothing, and it needs to go. That completes the verb set — INSERT gave rows life, UPDATE edited them, and DELETE ends them. Same WHERE logic as the last page, same missing-WHERE danger, and by now you can predict that danger before the page names it.

But DELETE brings two things of its own. First, the foreign keys that have quietly pointed between tables since Chapter 2 turn out to push back when you try to delete a row that others depend on. And second, a professional secret: much of the industry avoids true DELETE entirely, for reasons an accountant would recognize on sight.

The Same Anatomy, the Same Trap

DELETE FROM bookings
WHERE booking_id = 501;

Notice what is missing: there is no SET, because deletion does not modify values — the whole row goes. Everything else is familiar. The WHERE picks the rows, the database reports 1 row deleted, and you read that count the way the last page taught you: expected one, got one.

And yes — DELETE FROM bookings with no WHERE at all deletes every row in the table. Empty table, no error, no undo. The defense transfers from the last page verbatim, so here is the short recap: run a SELECT with the same WHERE first, read what comes back, count what you are about to remove, then delete. If that habit felt optional an hour ago, it should not now.

Pointers Push Back

Paper Lanterns finishes its run, and Lora decides to tidy the films table. She aims carefully, rehearses with a SELECT, and runs a DELETE that is about to fail anyway:

DELETE FROM films
WHERE title = 'Paper Lanterns';

PostgreSQL answers with something like: update or delete on table "films" violates foreign key constraint "screenings_film_id_fkey" on table "screenings". In plain words: rows in screenings still carry this film's id, and removing the film would leave them pointing at nothing. Chapter 2's foreign-key promise — every film_id in screenings names a real film — is being kept from the other side. The database would rather refuse your DELETE than break its own guarantee.

The honest resolution is to delete in order: children first, then the parent. Remove the screenings that point at the film (and the bookings that point at those screenings), and only then does the film row go quietly. Schemas can also declare an automatic answer with ON DELETE rules: CASCADE, which deletes the pointing rows along with the parent, or SET NULL, which blanks the pointer instead. CASCADE does a great deal with one keyword — including, if you are not careful, far more than you meant, since deleting one film could silently take its screenings and their bookings with it. Chapter 6 decides when that trade is worth making; for now it is enough to recognize the names.

Why Rows Rarely Truly Die

Now the secret. Watch an accountant fix a mistake in a ledger: they never reach for an eraser. They add a correcting entry underneath, so the book keeps both the error and the fix — because the history of what happened is the point of keeping books at all. Erase a line and you have not corrected the record; you have destroyed it. The software industry reached the same conclusion about most business data, and the technique it landed on is called soft delete.

A cancelled booking at the Marquee is not removed from the bookings table. Instead, the table carries a cancelled_at column — normally NULL, and filled with a timestamp the moment the booking is cancelled. "Delete" quietly becomes an UPDATE:

UPDATE bookings
SET cancelled_at = '2026-07-09 10:15'
WHERE booking_id = 501;

The row stays, the seat frees up, and every question the future might ask still has its answer: the refund has a booking to refer to, a dispute has evidence, the monthly report still knows the sale happened and was cancelled. NULL — Chapter 2's marker for "no value here" — earns its keep in this pattern: cancelled_at IS NULL means the booking is alive.

The honest tradeoff is a permanent tax on queries. Cancelled rows are still rows, so every ordinary question must now exclude them explicitly:

SELECT seat
FROM bookings
WHERE screening_id = 88
  AND cancelled_at IS NULL;

Forget the last line and cancelled bookings sneak back into the seat map, showing G7 as taken by a booking that no longer exists in any business sense. Whether that tax is worth paying is a real decision, made per table — test data deserves a true DELETE; money and history usually deserve the timestamp. Once you know the pattern, you will recognize it in almost every real schema you ever open: columns named deleted_at, cancelled_at, is_active are all the same idea wearing different names.

TRUNCATE Exists

One last word to file away, not to use. TRUNCATE empties a table wholesale — every row, no WHERE clause even possible, typically faster than a mass DELETE because the engine clears the table rather than removing rows one by one. It exists for wiping test tables and rebuilding imports. Recognize it when you meet it in the wild, treat it with the respect a word like "empty everything" deserves, and move on; this book will not need it again.