I have 'ImagePendingDBStore' entity, I have added 4 more columns in this entity (see comment in code below). In this, one column is indexed 'referenceId'
@Entity(tableName = "ImagePendingDBStore",indices = {@Index(value = "referenceId")})
public class ImagePendingDBStore {
@PrimaryKey(autoGenerate = true)
@NonNull
private int ImageId;
private String referenceId;
private Date createdAt;
private Date updatedAt;
private String imageAbsolutePath;
private int synced = 0;
private String customerFirstName;
private String customerMiddleName;
private String customerLastName;
private String dasId;
private String documentName;
private String imageIdRandom;
private int imageResolution;
//new columns
private String dealerId;
private String lat;
private String lng;
private Date imageCaptureDate;
}
My migration logic in database class is as below:
public static final Migration MIGRATION_1_2 = new Migration(1, 2) {
@Override
public void migrate(SupportSQLiteDatabase database) {
database.execSQL("ALTER TABLE ImagePendingDBStore ADD COLUMN dealerId TEXT");
database.execSQL("ALTER TABLE ImagePendingDBStore ADD COLUMN lat TEXT");
database.execSQL("ALTER TABLE ImagePendingDBStore ADD COLUMN lng TEXT");
database.execSQL("ALTER TABLE ImagePendingDBStore ADD COLUMN imageCaptureDate DATE");
database.execSQL("CREATE INDEX index_ImagePendingDBStore_referenceId ON ImagePendingDBStore (referenceId)");
}
};
Below is the error I am facing:
Caused by: android.database.sqlite.SQLiteException: index index_ImagePendingDBStore_referenceId already exists (code 1): , while compiling: CREATE INDEX index_ImagePendingDBStore_referenceId ON ImagePendingDBStore (referenceId)
#################################################################
Error Code : 1 (SQLITE_ERROR)
Caused By : SQL(query) error or missing database.
(index index_ImagePendingDBStore_referenceId already exists (code 1): , while compiling: CREATE INDEX index_ImagePendingDBStore_referenceId ON ImagePendingDBStore (referenceId))
#################################################################
As the message says index_ImagePendingDBStore_referenceId already exists.
You only need the 4 ALTER statements as the index exists i.e. you have not added it. As it already exists then adding the 4 columns doesn't require the index to be changed.
You could remove the line :-
database.execSQL("CREATE INDEX index_ImagePendingDBStore_referenceId ON ImagePendingDBStore (referenceId)");