CODEWITHSUNDEEP
sqliteindexingconstraintsunique
Greelings
Greelings

Reputation: 5434

Sqlite : unique constraint if not empty

I would like to create an unique constraint on a TEXT column that will only affect the non-empty cells. (Each cell must be unique if and only if it contains some characters).

I tried this, but it raised an exception :

db.execSQL("CREATE TABLE tables (" +
        "table_id INTEGER PRIMARY KEY, " +
        "table_name TEXT, " +
        "table_number INTEGER, " +
        "table_alias TEXT, " +
        "UNIQUE(table_alias) WHERE table_alias != '' ON CONFLICT IGNORE);"

Any idea how can I achieve this?

Upvotes: 0

Views: 463

Answers (1)

CL.
CL.

Reputation: 180290

In a UNIQUE column, NULL values are considered distinct. (Don't ask why, this is just how SQL works.)

So replace the emtry strings with NULLs.

Upvotes: 2

Related Questions