CODEWITHSUNDEEP
sqlite
Anjali Kaushal
Anjali Kaushal

Reputation: 86

How to set unique constraint over multiple columns when any one can be null?

How do I set unique constraint over multiple columns when any one can be null in SQLite?

e.g. I have made unique("col1","col2","col3") and tried insert into tablename values("abc","def",null) twice it inserted both rows.

The unique constraint is not working when third column is null.

Upvotes: 4

Views: 1957

Answers (3)

springy76
springy76

Reputation: 3786

Starting with version 3.9.0 (2015-10-14) you can use indexes on expressions (https://www.sqlite.org/expridx.html) and use for example the COALESCE function to transform null values into some form of fallback value:

CREATE UNIQUE INDEX IX_Unique ON Table1 (
    COALESCE(col1, ""), 
    COALESCE(col2, ""), 
    COALESCE(col3, "")
);

Upvotes: 2

CL.
CL.

Reputation: 180280

You could create a trigger to enfore this:

CREATE TRIGGER col1_col2_col3null_unique
BEFORE INSERT ON MyTable
FOR EACH ROW
WHEN col1 IS NOT NULL
 AND col2 IS NOT NULL
 AND col3 IS NULL
BEGIN
    SELECT RAISE(ABORT, 'col1/col2/col3 must be unique')
    FROM MyTable
    WHERE col1 = NEW.col1
      AND col2 = NEW.col2
      AND col3 IS NULL;
END;

You need such a trigger for each possible combination of NULLs in the three columns. Furthermore, if it is possible to have UPDATEs that change such a column to NULL, you need triggers for those, too.

Upvotes: 1

lucemia
lucemia

Reputation: 6627

In sqlite, all null are differences. I think the best way to solve this issue is to set column c not null with a special default value. Then use the default value (for example 0, '') to represent null.

edit 1

you can easily extend this solution to any columns

create table test (
    a text not null default "",
    b text not null default "",
    c text not null default ""
);

Upvotes: 5

Related Questions