how to set unique constraint over multiple column when any one can be null in sqlite

Question

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

e.g. I have made unique("col1","col2","col3") and tried with insert into tablename values("abc","def",null) twice it hase inserted both rows. The unique constraint is not working when third column is null.

Answer 1

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 ""
);