Schedule Meeting


When to use NULLable columns in relational databases

by | Oct 15, 2020 | SQL Language

Need Help?  Click Here for Expert Support

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 NULLable 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, UNIQUEs allow any number of NULLs.

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.


  • 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 NULLs.

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


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

All content in this blog is distributed under the CreativeCommons Attribution-ShareAlike 4.0 International license. You can use it for your needs and even modify it, but please refer to Vettabase and the author of the original post. Read more about the terms and conditions:

About Federico Razzoli
Federico Razzoli is a database professional, with a preference for open source databases, who has been working with DBMSs since year 2000. In the past 20+ years, he served in a number of companies as a DBA, Database Engineer, Database Consultant and Software Developer. In 2016, Federico summarized his extensive experience with MariaDB in the “Mastering MariaDB” book published by Packt. Being an experienced database events speaker, Federico speaks at professional conferences and meetups and conducts database trainings. He is also a supporter and advocate of open source software. As the Director of Vettabase, Federico does business worldwide but prefers to do it from Scotland where he lives.

Recent Posts

Making the case for stored procedures

Making the case for stored procedures

MariaDB stored procedures and why they should be improved was the title of my talk at MariaDB Server Fest 2023 in Helsinki. My point was that an improved stored procedures implementation would bring benefits to individual users, the MariaDB community, and the two...

How slow is SELECT * ?

How slow is SELECT * ?

The most widely known query optimisation rule is that developers should always avoid SELECT *. Even when all columns are needed, they should list their names, because more columns could be created in the future. But developers find this really annoying: listing the...


Need Help?  Click Here for Expert Support


Submit a Comment

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