Make SQLite consider nulls indistinct in UNIQUE constraint-CodePudding

In SQLite UNIQUE constraints NULLs are distinct (source). In my use case I'd like NULLs to be indistinct for a particular UNIQUE constraint. The following example shows what I want.

sqlite> CREATE TABLE Foo (Name TEXT NOT NULL, Nullable TEXT, UNIQUE(Name, Nullable));
sqlite> INSERT INTO Foo(Name, Nullable) VALUES('foo', 'bar');
sqlite> INSERT INTO Foo(Name, Nullable) VALUES('foo', 'bar'); -- Errors as expected
Runtime error: UNIQUE constraint failed: Foo.Name, Foo.Nullable (19)
sqlite> INSERT INTO Foo(Name) VALUES('baz');
sqlite> INSERT INTO Foo(Name) VALUES('baz'); -- I want this to error, but it doesn't

It appears that PostgreSQL has support for this with NULLS NOT DISTINCT, but this syntax doesn't work in SQLite. So, I'm wondering how I can get this behavior in SQLite.

CodePudding user response：

You need to convert NULL values to some value to make it count in unique index.

Create your unique index in a following way:

CREATE UNIQUE INDEX `idx_my_unique` ON Foo (
    Name,
    IFNULL(Nullable, '')
);

Of course this has a limitation that empty strings and NULL will be treat as the same value from the uniqueness point of view. If this does not limit you, you can choose this design.

Otherwise you need to re-design your table in a way unique index is used over columns without NULL value.

CodePudding user response：

You cannot make NULLS NOT DISTINCT in SQLite, but you can have desired behavior, though at some price. So long as you target schema is not too complex, you can add a partial UNIQUE index on the complementary field(s). In this case, you add this index:

CREATE UNIQUE INDEX "DistinctNullsOnNullable" ON "Foo" ("Name") WHERE Nullable IS NULL;

When you have multiple nullable columns, this approach becomes expensive. A better option, as suggested in the comment, to make the column not nullable and use a some value, such as '' or 0 or -1 to indicate absence of data. You can set this value as default to yield the desired behavior, for example

CREATE TABLE "Foo" (
    "Name"  TEXT NOT NULL,
    "Nullable"  TEXT NOT NULL DEFAULT '',
    UNIQUE("Name","Nullable")
);