Soft Deletion (Archiving)

In many applications, data should not always be permanently removed when it's "deleted" by a user. Instead, we use a strategy called soft deletion, also known as archiving.

What is Soft Deletion?

Soft deletion means marking a row as deleted without actually removing it from the database. This allows the data to be restored, audited, or logged later.

Instead of using DELETE, we add a column to indicate deletion status:

SQL

CREATE TABLE users (
    userId INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    name VARCHAR(100) NOT NULL,
    createdAt TIMESTAMP NOT NULL DEFAULT NOW(),
    deletedAt TIMESTAMP NULL
);

SQL

INSERT INTO users (name) VALUES ("Aleksi");

When you want to "delete" a row:

SQL

UPDATE users SET deletedAt = NOW() WHERE name = "Aleksi";

To fetch only active (non-deleted) rows:

SQL

SELECT * FROM users WHERE deletedAt IS NULL;

Why Use Soft Deletion?

Ability to recover deleted data
Track when something was deleted (for audit trails)
Useful for time-based cleanup (e.g., permanent deletion after 30 days)
Keeps historical data for analytics and debugging

Pitfalls of Soft Deletion

You must manually filter out deleted rows in every query
Requires extra logic for cascading deletions or cleanups

Should You Use CASCADE with Soft Deletes?

When using soft deletion, using database-level ON DELETE CASCADE is not recommended.

Why? - CASCADE only works with hard deletes (DELETE FROM ...). - If you're only marking rows as deleted, no actual deletion happens – so child rows stay untouched.

Alternative: Handle cascade manually

If you want to mimic cascade behavior with soft deletes:

SQL

UPDATE orders SET deletedAt = NOW()
WHERE user_id = 1;

You must do this manually in your application logic.

Summary

Soft deletion means marking rows as deleted using a flag (isDeleted / isArchived) or timestamp (deletedAt / archivedAt)
Useful for recovery, auditing, and safe deletion practices
Requires more logic, especially in relationships
Don't use ON DELETE CASCADE with soft deletion – handle cascading soft deletes manually in your application logic.