Better Soft Deletes With NULL

What is the best way to “soft” delete database rows that are no longer needed?

Perhaps this is a regulatory requirement. Or maybe the schema declares foreign key constraints but without cascading deletes.

We’re don’t need to store a detailed change log, but we don’t want information to disappear, either.

One solution is to add a ‘deleted’ flag to a table:

This is not the best way!

isDeleted TINYINT NOT NULL DEFAULT 0

Does the table have a natural key (aka unique index)? If you soft-delete a row and later attempt to insert a row with the same key, the unique index won’t allow it.

Are you adding a new row or do you need to undelete the existing row? It depends on your application. Undeleting rows means developers need to write more code which requires more testing.

Depending on the nature of a particular table, soft-deleted rows can sometimes be replaced by new rows. In other words, soft-deleted rows should behave like deleted rows. The table should be able to store multiple soft-deleted rows with the same key. To accommodate this requirement, we can leverage the fact that NULL != NULL. Unique indexes ignore NULL values!

Reverse the flag’s meaning. Instead, use NULL to flag soft deleted rows!

This is better!

isActive TINYINT NULL DEFAULT 1

Thus, multiple rows can have the same natural key as long as isActive is NULL for all but one of them.

Stretch Goals

1. Add a deactivated timestamp

Also consider adding a timestamp column indicating when (in database server time) the row was deactivated. This column will be useful for audits.

2. Add a view layer for compliance

Create one view per table that excludes rows in which isActive IS NULL. By making sure that SELECT statements use the views, you won’t have to worry about developers forgetting to add AND isActive to every WHERE and ON clause.

Happy coding!