When to use NULLable columns in relational databases

In the article What does NULL mean in SQL? I explained why I consider SQL  NULL  inconsistent. As a general rule, I should have made clear that I don’t think it is a good idea to use  NULL , even if columns are NULL able by default in SQL.

However, there are exceptions. This article mentions the classical cases when using  NULL  appears much simpler than the alternatives.

TL;DR:  NULL  is actually very useful with  UNIQUE  indexes, but only apparently useful with foreign keys.

NULL represents the unknown, just like
Loch Lomond in the fog

UNIQUE constraints

UNIQUE  constraints (or  UNIQUE  indexes, which are functionally the same thing) disallow duplicate values for a certain column or set of columns. But with most DBMSs,  UNIQUE s allow any number of  NULL s.

The most frequent case when this is desirable is when we need to guarantee that some optional data is unique. For example, maybe our users are not required to provide us with their email; but if they do, each user should have a unique email. This becomes very easy if we store missing emails as  NULL .

Partial indexes

Some databases support a feature called partial indexes. They are indexes that only include data matching a certain  WHERE  clause. Apparently, they could be a solution, as they could allow to only include non- NULL  data in a  UNIQUE  index.

However:

  • MySQL and MariaDB do not support partial indexes;
  • PostgreSQL does, but they cannot be  UNIQUE  indexes.

The alternative

In theory, the alternative is also simple. Instead of having an  email  column, we’ll have an  email  table with two columns:  user_id  and  user_id .

In practice, this forces us to run a  JOIN  every time we need to read a user’s email, which has an impact on performance.

Keep in mind however that this second approach is cleaner and more generic. Suppose that at some point the users can provide two emails. The typical solution is to add a column called  email2 , which is quite a dirty trick and does not allow us to guarantee the uniqueness of all emails.

So, which method am I cheering for? None of them. In most cases, the former looks more practical. I wouldn’t use the second if the only reason is avoiding  NULL s.

Foreign keys

When we have a child table logically linked to a parent table, we can enforce some constraints by adding a foreign key to the child table. So, for example, a child table book can have a foreign key that references the parent table author (in the simplistic case that each book has no more than one author).

One of the constraint that will be enforced is that the child table cannot have an orphan row. So, if a book has  author_id=24 , there must be a book with  id=24 .

The exception is that we can have a book with  author_id=NULL . From a logical point of view, this means that we don’t know the author of the book. From the implementation point of view, the foreign key will allow to insert such a book instead of producing an error.

The alternative

Alternatively, we could use a special value, like  0  or  -1 . To be able to do so, we’ll need to add a row in the  author  table, which represent an unknown author.

This solution needs to be known by whoever writes the application queries. For example, a query that count the authors should keep into account that one row does not represent a specific author.

Note that this solution can easily be generalised. So we could have one row for unknown authors, one for no author at all (the book is an anthology), one could even be for unknown French Middle Age authors, and so on.

Now, suppose you are not allowing orphaned rows (books whose author is unknown) and at some point you need to start to do it. Or the opposite. This solution simply imply to add or delete a row in a table, and developers can do it themselves. But if you use  NULL , you will have to change a column definition and run an  ALTER TABLE  in production.

See also

Related courses

Related articles

Conclusions

As usual, I’ll be happy to fix errors and discuss your ideas. I want to thank all the persons who contributed this website with their comments, creating a valuable shared knowledgebase.

Did you notice any mistake? Do you have ideas to contribute?
Please comment.

Federico Razzoli

About Federico Razzoli

Federico is Vettabase Ltd director, and he's an expert database consultant specialised in the MariaDB and MySQL ecosystems.

Leave a Reply

Your email address will not be published. Required fields are marked *

*