Deactivating Database Rows With One NULL Trick

Let’s say you’re implementing the requirement that rows should never be deleted from particular tables in a relational database. Perhaps this is a requirement from regulators. Or maybe the schema declares foreign key constraints (and — yes! — it should, at least in dev environments) but, for reasons such as performance and safety, without cascading deletes.

You’re not trying to use the RDBMS to store an audit trail of every modification, which is usually a bad idea, but you don’t want data to completely disappear, either.

What’s the best way to virtually delete (or deactivate) rows in a RDBMS?

The obvious solution is to add a column that holds a deactivated flag:

This is not the best way

deactivated TINYINT NOT NULL DEFAULT 0

This sounds like a good thing for most scenarios, but for pure relationship tables, it can be desirous to insert new rows instead of reactivating them. Please, reactivating rows is cumbersome and forces your developers to change their coding patterns.

There’s a better way: reverse the flag’s meaning and use NULL to indicate whether the row has been deactivated:

This is better!

active TINYINT NULL DEFAULT 1

The trick: Add the active column to unique indexes

Store the deactivation time

Implement a view layer to prevent bugs

Happy coding!

He/him. Coder and gamer since 1980. Lately I’ve been coding in Node and React. Always seeking The Best Way.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store