alter sqlite table to add unique constraint on multiple columns

Question

I have a ContentProvider backed by an sqlite table. So to create my table I used

public class H extends SQLiteOpenHelper {
  @Override
  public void onCreate(SQLiteDatabase sqliteDatabase) {
    …// here I defined my original table without constraints
  }
}

When originally created, the table had the columns: name, age, height. No Constraints. Nothing.

Now I need to add constraints to the table. So I increased the DATABASE_VERSION, and then in the onCreate String I added UNIQUE(name,age) ON CONFLICT REPLACE. My question is, what should I do inside the onUpgrade method? Stated more simply: How do I call ALTER TABLE just for adding constraints? My attempt failed

ALTER TABLE myTable ADD UNIQUE(name,age) ON CONFLICT REPLACE

Here is the error message:

Caused by: android.database.sqlite.SQLiteException: near "CONSTRAINT": syntax error (code 1): , while compiling: ALTER TABLE myTable ADD CONSTRAINT UNIQUE(name,age) ON CONFLICT REPLACE
#################################################################
Error Code : 1 (SQLITE_ERROR)
Caused By : SQL(query) error or missing database.
    (near "CONSTRAINT": syntax error (code 1): , while compiling: ALTER TABLE myTable ADD CONSTRAINT UNIQUE(name,age) ON CONFLICT REPLACE)

Megen

Answer 1

Alter table is rather limited in sqlite. However for your purpose CREATE UNIQUE INDEX works just as well.

Generally there is no difference between creating a unique constraint vs creating a unique index. Refer:
SQLite - Any difference between table-constraint UNIQUE & column-constraint UNIQUE? https://dba.stackexchange.com/questions/144/when-should-i-use-a-unique-constraint-instead-of-a-unique-index

So in your case change your code to use should change the code in your onCreate to use CREATE INDEX syntax instead.

Please be aware that this may still fail if your table already has duplicate entries.

Saleme

Answer 2

You cannot add a constraint with an ALTER TABLE in SQLite.

Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted.

http://www.sqlite.org/omitted.html

Helicograph