Deleting Is an Architecture Decision

The request always shows up the same way. Someone entered the wrong data, or a customer left, or the screen got cluttered, and the question is simple: can we get a delete button here?

Sure. The DELETE is one line. In dev the table has three rows and none of them point anywhere, so it works on the first try. The ticket closes the same day.

The problem arrives weeks later, when someone clicks that button in production. There, the row isn't alone. It has children, and some of those children have children.

What "just a delete button" is actually asking for

When someone asks for deletion, that's rarely what they want. What they want is usually one of three things:

• disappear from this list, because the screen is cluttered
• stop being counted, because the row is ruining a report
• not exist anywhere, because that's the only way to meet an obligation

All three use the same word and require different implementations. The first is a filter. The second is a status flag. Only the third is a real DELETE.

Most of the data-loss incidents I've watched started in that confusion. Nobody decided to destroy valuable information. Somebody asked to stop seeing it, and got permanent removal.

What the database does when you don't decide

It's worth knowing the default behavior, because it's already a choice, even when you never made one.

In PostgreSQL, if you declare a foreign key and say nothing about deletion, it comes out as NO ACTION. That means the database refuses the operation while referencing rows still exist. The practical result matches RESTRICT, with one difference that only surfaces inside a transaction: NO ACTION can be deferred to the end of it, RESTRICT cannot.

ON DELETE CASCADE does the opposite. Delete the parent and the database removes the children automatically. The chain keeps going until it reaches a foreign key declared with a different rule, where it stops.

There are also SET NULL and SET DEFAULT, which don't remove the child at all. They change the column that pointed at the parent. Useful for optional relationships, and usually forgotten.

None of these is correct by default. Each answers a different question.

The cost nobody puts in the estimate

What makes this decision expensive isn't the DELETE. It's everything after it.

The orphan nobody notices. With no foreign key declared, the database happily lets you remove the parent and keep thousands of children pointing at an id that no longer exists. Nothing breaks at the time. It breaks three months later in a report that won't reconcile, and by then you can't reconstruct what was there. The trust you lose all at once. A user who deleted something by accident and lost work stops trusting the screen. They start avoiding the whole operation, including the times it was the right one. You don't win that back with a better warning message. The bill that lands in support. Every "can you bring that record back?" turns into somebody opening a backup, restoring it into a separate database, finding the row and re-inserting it by hand. That work is manual, repetitive and reactive, and it grows right alongside your user base.

The rule I use

Before picking the foreign key behavior, I ask one question: does the child row mean anything without the parent?

If it doesn't, CASCADE is the honest answer. An order line doesn't exist without the order. A shipping address attached to a purchase doesn't exist without the purchase. A photo inside a deleted album becomes nothing. Keeping those rows alive only creates garbage someone has to clean up later.

If it does, RESTRICT. Invoices, financial entries, audit logs and support history have value of their own. They describe something that happened, and a customer leaving doesn't undo what happened.

Then comes the part almost nobody builds, and it's what turns RESTRICT from an obstacle into a tool: a way to see what's attached before confirming. A simple endpoint that answers how many rows depend on that id and what kind they are. The screen shows the number, the person decides with information in hand.

A database error message doesn't work as an interface. update or delete on table violates foreign key constraint is accurate information written for someone who will never read it.

When the opposite is the right call

I spent a long stretch arguing that almost nothing should be truly deleted, and that position has a hard limit.

The first limit is legal. Under GDPR Article 17, a data subject can request erasure of their personal data, and controllers have to comply unless a specific exemption applies. A system where nothing is ever really removed, only flagged inactive, can't answer that request. You find that out at the worst possible moment.

The second limit is operational. Soft delete sprinkled around without discipline gets expensive. Every query has to remember the filter, and the one that forgets will return data that was supposed to be invisible. Unique indexes stop behaving the way you expect, because the inactive row still occupies the value. Reports start disagreeing depending on who wrote the query.

The third is simpler: not every row deserves that care. Drafts, abandoned cart items, read notifications, session cache. Deleting them for real is cheaper than carrying them.

The close

The deletion decision isn't about the button. It's about how much the system should know about the damage before letting anyone confirm.

If deleting on that screen can hurt, someone needs to see the size of the damage first, and not in the form of an error message.

Which DELETE in your system does nobody know the full blast radius of right now?

📩 Let's talk