2014-03-07 12:00:00

UNIQUE NULL

(This entry is part of a series. The audience: SQL Server developers. The topic: SQLite on mobile devices.)

---

Nullable columns in UNIQUE constraints

The following code will cause a unique constraint violation in SQL Server:

CREATE TABLE foo (pk int PRIMARY KEY IDENTITY, x int UNIQUE NULL);
INSERT INTO foo (x) VALUES (1);
INSERT INTO foo (x) VALUES (2);
INSERT INTO foo (x) VALUES (NULL);
INSERT INTO foo (x) VALUES (NULL);

SQL Server doesn't allow the second NULL value to be inserted for column [x].

But SQLite allows this. See Issues with sync and constraints for more discussion of why this kind of thing matters.

Boxes and Cats

The difference arises from the question of whether NULL is considered a value or the absence of a value.

Suppose you have a set of cardboard boxes. Each box can hold one cat. And the cats must be unique.

SQLite thinks of NULL as an empty box. Having multiple empty boxes does not violate the rule about cats being unique.

SQL Server thinks of NULL as the name of a specific cat. A box cannot be empty. And the NULL cat can only be in one box.

If you prefer an explanation with more technical detail and fewer cat pictures, see this page on the SQLite website, which (at the time of this writing) provides a chart of how 12 different SQL implementations handle 8 different situations with NULL values. An additional remark about the amibiguous language in the SQL92 standard appears in the SQLite FAQ.

Solution 1: Don't have the problem

One possible solution is to never have this problem in the first place. Obviously, this issue won't arise if you don't have any nullable columns in unique constraints.

Solution 2: Don't cause the problem

Another option is to just not have any bugs in your app. :-)

Hmmm. That was a bit too flippant to be helpful. Let me try to explain it a different way:

In many (or most?) situations, SQL constraints are used as a way to prevent bugs in upper layers from screwing up the data. In other words, if a constraint gets violated, that's a sign that something went wrong. A constraint violation is typically not used as control flow of the software under normal usage. So, if a constraint violation happens, it is usually a sign of a bug in an upper layer.

SQL constraints also provide guarantees which allow the code in upper layers to be simpler. Those guarantees are part of the contractual agreement between your application code and your database. If the database is promising that values in column [foo] will never be greater than 5, the application code can trust in that promise. It doesn't need to do range checking on every value it gets from the database.

If SQL constraints did not exist, we would simply have to enforce our data integrity rules in application code instead of at the database level. This is not "The SQL Way", but it is certainly possible. The NoSQL folks have to live this way all the time.

So, you have the option of simply accepting the fact that SQLite will not enforce SQL Server's rule about distinct NULLs. If your app never inserts a NULL into that column, you don't need to worry about it. Or you can make your application code do extra checking to make sure.

Solution 3: Use a trigger to make SQLite act like SQL Server

If you want SQLite to enforce distinct NULLs at the database level in the same way as SQL Server does, you can get that behavior by adding triggers to your SQLite tables. For example:

CREATE TRIGGER "uniqnull" BEFORE INSERT ON "foo" WHEN ( (NEW."x" IS NULL) )
BEGIN

SELECT RAISE(ABORT, 'constraint violation: unique null')
WHERE EXISTS (SELECT 1 FROM "foo" c WHERE ( (c."x" IS NEW."x") ));

END

The syntax of this trigger probably looks strange to you. I'll be writing about the differences between SQL Server triggers and SQLite triggers in another entry in this series.

This trigger will impact performance. You can decide if the tradeoff is worth it for your application. Measure it.

(When Zumero for SQL Server encounters a nullable column in a unique constraint, it automatically adds triggers like this one to the corresponding SQLite table on the client side. Those triggers include a comment about possible performance impact and suggesting that the trigger can simply be dropped if the other tradeoff is preferable.)

Summary

SQLite handles nullable columns in unique constraints differently from SQL Server. This distinction may never cause you problems, but if it does, you have several options for dealing with it.