Search code examples
androidsqlitedatabase-migrationandroid-room

Room Database Migration Issue with Unique Index

I was trying to upgrade my Database version and in last version the table had a unique index created upon migration room gave me the following error

Expected:

TableInfo{name='ALERT_STATUS', columns={resolution_time=Column{name='resolution_time', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0}, status=Column{name='status', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0}, id=Column{name='id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1}, event_time=Column{name='event_time', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0}, is_uploaded=Column{name='is_uploaded', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0}, InvErrorCode=Column{name='InvErrorCode', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0}, is_fault=Column{name='is_fault', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0}, FaultDetails=Column{name='FaultDetails', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0}, code=Column{name='code', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0}, component=Column{name='component', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0}}, foreignKeys=[], indices=[Index{name='index_ALERT_STATUS_code_event_time', unique=true, columns=[code, event_time]}]}

Found:

TableInfo{name='ALERT_STATUS', columns={resolution_time=Column{name='resolution_time', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0}, status=Column{name='status', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0}, id=Column{name='id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1}, event_time=Column{name='event_time', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0}, is_uploaded=Column{name='is_uploaded', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0}, InvErrorCode=Column{name='InvErrorCode', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0}, is_fault=Column{name='is_fault', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0}, FaultDetails=Column{name='FaultDetails', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0}, code=Column{name='code', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0}, component=Column{name='component', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0}}, foreignKeys=[], indices=[Index{name='code_time', unique=true, columns=[code, event_time]}]}

I tried removing the unique index and the migration worked fine but with unique index it never passed

My database Entity is below


@Entity(
  tableName = "ALERT_STATUS" , 
  indices = {
    @Index(
      value = {"code", "event_time"}, 
      unique = true
    )
  }
)
public class AlertStatus implements Serializable{
  @PrimaryKey(autoGenerate = true)
  public Integer id;
    
  @NonNull
  public String event_time;

  public String resolution_time;
    
  @NonNull
  public String code;

  public Integer component;

  public int status;

  public int is_fault;

  public String FaultDetails;

  public String InvErrorCode;
  
  public int is_uploaded;
}
Solution

  • Try this worked for me.

    1. Create a new Table i.e. Holder table which will hold your data between structural changes.

       database.execSQL("CREATE TABLE TABLE_NAME (id INTEGER, column1 varchar(25), column2 varchar(25) , PRIMARY KEY(id))");

    2. Create Unique Index as required

       database.execSQL("CREATE UNIQUE INDEX IF NOT EXISTS <index-name> ON TABLE_NAME (column1,column2)");