You Should Always Set a Default Value on Your Boolean Database Columns

Why null is not always the right solution

Photo by Chris Barbalis on Unsplash

There is something that always annoys me when I look at a schema.

active can have 3 values: true, false and nil

When I create a user, I expect to have the most important columns filled.

active is an important value because this information can change the whole behaviour of my application.

I refer toNULL as "I don't know," and this answer is not acceptable for this column.

You should avoid NULL for a boolean.

In SQL, you cannot compare NULL with TRUE and FALSE.

• NULL AND TRUE IS NULL
• NULL AND FALSE IS NULL
• NULL AND TRUE OR FALSE IS NULL

What should I do to avoid this problem?

Well, you should add a default value to your boolean, and expect it to not be able to have a NULL value.

Now active is directly set to true and can only be changed to false.

With this solution, your user will always have the most important values and you will no longer encounter weird behaviours.

For more information about SQL, you can check out Null (SQL) or this Microsoft document on relational algebra.