What Should Disappear When a User Is Deleted?

In part 1, I added foreign keys to one table and picked cascade, restrict, and set null in a single sentence. That sentence is the whole design problem, so it gets its own post.

Here is what deleting a user does in MetaMove today, with no foreign keys anywhere: nothing, except removing the user. The sessions they logged, the messages they sent, the plans they authored, the roster rows that name them, all stay, pointing at an id that no longer exists. Nothing errors. The app just starts showing blank names in odd places.

The four answers

Every foreign key has an ON DELETE clause, whether you write one or not. It answers “when the row I point at is deleted, what happens to me?”

• NO ACTION, the default, and RESTRICT: refuse the delete while I still exist. (They differ only in when the check runs inside a transaction; for design purposes they are the same answer.)
• CASCADE: delete me too.
• SET NULL: keep me, but forget the link.
• SET DEFAULT: keep me, point at a default row instead. Rare.

The question that picks between them, for each key: is the child row about the parent, or does it merely mention it? A row that is about its parent should go with it. A row that mentions its parent should survive and forget. And if the parent is a shared thing that other rows are built on, the delete should be refused until those rows are dealt with.

Walking the schema

I went through every relationship with that question. This is the table I ended up with.

Most rows fell out of the question quickly. The interesting ones were progress_entries, which is about the member (cascade) but only mentions the session it happened in (set null), and exercises, where I first reached for cascade and then imagined a trainer deleting “Back squat” from the library and silently gutting every plan on the platform.

Watching it work

I built the tables with those rules on a scratch Postgres 17 and tried the deletes that should fail first.

DELETE FROM exercises WHERE id = '2222…';

ERROR:  update or delete on table "exercises" violates foreign key constraint "workout_exercises_exercise_id_fkey" on table "workout_exercises"
DETAIL:  Key (id)=(2222…) is still referenced from table "workout_exercises".

DELETE FROM "user" WHERE id = 't1';   -- a trainer with a plan

ERROR:  update or delete on table "user" violates foreign key constraint "workout_plans_trainer_id_fkey" on table "workout_plans"
DETAIL:  Key (id)=(t1) is still referenced from table "workout_plans".

Both refusals name the table and the row that is in the way, which is exactly the information you need to fix it. Then the deletes that should succeed:

DELETE FROM workout_plans WHERE id = '1111…';

 sessions | workout_exercises | progress | progress_session
----------+-------------------+----------+------------------
        0 |                 0 |        1 |

The plan took its sessions and their exercise rows with it (cascade following cascade), and Ana’s progress entry survived with a null session. Then the member:

DELETE FROM "user" WHERE id = 'm1';

 progress | sender_id | recipient_id |    content
----------+-----------+--------------+---------------
        0 | t1        |              | Great session

Her progress is gone. Rey’s message to her is still in his outbox, addressed to nobody, which is what a deleted account should look like from the other side.

Cascades chain, so look before you delete

cascade follows cascade. Deleting one plan removed its sessions, and each session’s exercise rows, in one statement. On a real database one delete can remove thousands of rows, silently and correctly. Two habits help:

BEGIN;
DELETE FROM workout_plans WHERE id = '1111…';
SELECT count(*) FROM workout_sessions;   -- is this the number you expected?
ROLLBACK;                                -- or COMMIT

Run it in a transaction and look at the counts before you commit. And when a delete is refused, resist the urge to switch the key to cascade; the refusal is usually telling you a human decision is missing, like who inherits the plans.

Where none of the four is right

Sometimes “delete” in the product doesn’t mean delete in the database. A gym might need to keep a member’s payment history after they leave. A message thread reads badly with half the messages missing. In those cases the answer is a soft delete: a deleted_at column, and the row stays.

The price is that every query must now add WHERE deleted_at IS NULL, and the partial unique index from part 1 must include that condition too, or a deleted member’s old active row will block their return. Soft delete is a policy you commit to everywhere, not a column you add to one table.

My call for MetaMove: real deletion. A member who deletes their account expects their logs to go, the app is small, and the cascades above express that. restrict on the shared things protects the rest.

In Drizzle

The rules go on the column, same as the foreign key itself:

workoutSessionId: uuid("workout_session_id").references(
  () => workoutSessions.id,
  { onDelete: "set null" }
),
exerciseId: uuid("exercise_id")
  .notNull()
  .references(() => exercises.id, { onDelete: "restrict" }),

One thing the type system won’t catch: set null needs a nullable column. My original workoutSessionId was .notNull(), and that combination fails at delete time, not at migration time.

What I’d tell past me

Write the ON DELETE rule when you write the key, by asking whether the child is about the parent or merely mentions it. Cascade the first, null the second, restrict anything shared. And test the deletes you expect to be refused, because those are the ones protecting you.

Next in this series: my status columns were text with a TypeScript union on top, and my ids came in three flavours. What Postgres can actually check.