Recently I had the problem that I needed a unique index in an SQLite database,
but some fields of my table had null values, which I needed to be considered
equal, or NOT DISTINCT.
Some database systems allow to specify the behaviour when creating indexes,
but SQLite only supports the NULLS DISTINCT behaviour.
In this article, we explore two ways to express the NULLS NOT DISTINCT behaviour
in SQLite.
Not considering null values, two tuples are equal iff each of their components
are equal. (1, 2, 3) is equal to (1, 2, 3). There are usually two ways to view
null in databases: the absence of a value, or a value that exists but is not known.
In "absence of a value", we would consider (1, 2, null) to be equal to (1, 2, null),
as we actually mean something akin to (1, 2), i.e. NULLS NOT DISTINCT.
In "unknown value", we would consider (1, 2, null) to not be equal to (1, 2, null),
as null represents an unknown but possibly or probably different value in either case, i.e. NULLS DISTINCT.
In our example we will consider an index on the table t with columns c1, c2, c3,
where c1 is never null, but the other two columns can be.
Indexes on Expressions
One possible way (which I ultimately chose) is using indexes on expressions.
Per column we choose a value which the column should never normally have.
As the columns are untyped in SQlite, this is quite easy. You could choose a
very large (or negative) number, or a random string.
Again, some value you're fairly certain, your actual data is never going to be equal to.
As an example, we choose -1, as my numbers are usually all positive.
If you want to prevent accidentally using this value, you may exclude it using a CHECK-constraint.
The COALESCE function is variadic and returns its first non-null` argument.
This means, our unique index does not compare the ``null values to each other
(which are not equal), but our choses non-null representative (-1 in this case).
Problem 1: We do need a value per column that is not allowed. Often times
it is very easy to find such a value.
You could always use something that would be a type mismatch, or something that is malformed.
But sometimes it might not be that easy and you might have to choose a different value for each column.
Also, the value will be stored in the index and it should not be very large.
Problem 2: SQLite doesn't actually use this index for queries and operations other than insert.
It will not make your SELECT statements faster. So you probably want to create another index,
or multiple, depending on your access patterns, that are used for retrieval.
Here you can just use the original index without the UNIQUE constraint:
CREATEINDEXONt(c1,c2,c3);
Our uniqueness constraint is checked through the other index.
Partial Indexes
The second way is to use partial unique indexes for every null/not null combination.
In this way we never allow null values to sneak into our index(es) and we are back
to our base case.
These indexes will be used by your SELECT queries and we do not need a particular value per column to trick the index checker.
The problem with this method is obvious though, as we create \(2^n\) indexes for \(n\) nullable columns.
So what you really want depends on your use case. Using partial indexes is probably generally faster and less error-prone,
and less memory-intensive. But you do need to manage multiple (exponentially many) indexes.
If you're not worried about speed and your queries are already supported by the other indexes, and you only need a simple
unique constraint over nullable columns, the indexed expressions might be the variant to use.