Search code examples
androidsqlitesqliteopenhelper

Not in operator not working with unique index

In my android application I am trying to create unique index in on upgrade in my table but I want to exclude some duplicate ids which are previously created so I have created sql query like this 

CREATE UNIQUE INDEX indexname ON tblName(formid, formno) WHERE ID NOT IN (36, 37)

but it give me error 

09-21 19:15:51.378: W/System.err(31454): java.lang.RuntimeException: Failure delivering result ResultInfo{who=null, request=2, result=2, data=Intent { (has extras) }} to activity {a.b.c/a.b.c.MainActivity}: android.database.sqlite.SQLiteException: near "WHERE": syntax error (code 1): , while compiling: CREATE UNIQUE INDEX indexname ON tblName(formid, formno) WHERE ID NOT IN (36, 37)

Data type of id is integer.

Solution

  • Partial indexes are supported since SQLite 3.8.0.

    This would be available only in Android API level 21 (Lollipop) or higher.