Search code examples
javaandroidsqlitesqliteopenhelper

Syntax to create unique composite column - Android SQLiteOpenHelper

I have this onCreate method in my SQLiteOpenHelper class, and I would like to add a unique constraint on these two columns (composite unique columns):

  • SongContract.SongEntry.COLUMN_TITLE
  • SongContract.SongEntry.COLUMN_RELEASEDATE

But I am getting an error:

Cannot resolve method UNIQUE

Here is my code:

public void onCreate(SQLiteDatabase db) {
    final String SQL_CREATE_SONG_TABLE = "CREATE TABLE " + SongContract.SongEntry.TABLE_SONG + " (" +
        SongContract.SongEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
        SongContract.SongEntry.COLUMN_TITLE + " TEXT NOT NULL, " +
        SongContract.SongEntry.COLUMN_RELEASEDATE + " INTEGER, " +
        UNIQUE(SongContract.SongEntry.COLUMN_TITLE, SongContract.SongEntry.COLUMN_RELEASEDATE) +
        SongContract.SongEntry.COLUMN_RATING + " TEXT);";

    db.execSQL(SQL_CREATE_SONG_TABLE);
}

What is the correct syntax to achieve my goal?

Solution

  • I found the corrrect syntax after playing around sqllite:

    final String SQL_CREATE_SONG_TABLE = "CREATE TABLE " + SongContract.SongEntry.TABLE_SONG + " (" + SongContract.SongEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + SongContract.SongEntry.COLUMN_TITLE + " TEXT NOT NULL, " + SongContract.SongEntry.COLUMN_RELEASEDATE + " INTEGER NOT NULL, " + SongContract.SongEntry.COLUMN_RATING + " TEXT, " + "UNIQUE" + "(" + SongContract.SongEntry.COLUMN_TITLE + "," + SongContract.SongEntry.COLUMN_RELEASEDATE + ") " + ");";