Fatal Exception on Room DB Migration: android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed

Question

I am adding UNIQUE constrains for 2 of my DB table’s columns so that each rows of the table could be unique. And so along with adding UNIQUE constrains I am also performing DB migration as below:

static final Migration MIGRATION_1_2 = new Migration(1, 2) {
     @Override
     public void migrate(SupportSQLiteDatabase database) {
         database.execSQL(“CREATE UNIQUE INDEX index_<MyTableName>_<ColumnName> ON <MyTableName> (<ColumnName>)”);
     }
 };

But Getting bellow crash:

Fatal Exception: android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: . (code 2067) at android.database.sqlite.SQLiteConnection.nativeExecuteForChangedRowCount(SQLiteConnection.java) at android.database.sqlite.SQLiteConnection.executeForChangedRowCount(SQLiteConnection.java:734) at android.database.sqlite.SQLiteSession.executeForChangedRowCount(SQLiteSession.java:754) at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:64) at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1677)

Note: One of the reason (But not sure) may be: As I am using "room:runtime:1.1.0" which has some issue related with it (Ref:https://issuetracker.google.com/issues/79362399) , So should I migrate to "room:runtime:1.1.1-rc1" ?

Any pointer on the above issue would be highly appreciated.

Thanks You.

Pinakin Kansara · Accepted Answer · 2019-04-05 08:59:05Z

You need to process your existing records for duplicate content in order to apply unique constraint during migration.

To understand the problem let's take an example.

+----+------------+-----------+------------+
| id | first_name | last_name |   mobile   |
+----+------------+-----------+------------+
|  1 | John       | Smith     | 1234567890 |
|  2 | Adel       | Jan       | 0987654321 |
|  3 | Marray     | Jane      | 1234567890 |
+----+------------+-----------+------------+

Above table consist a mobile number , which you might want to convert to a unique index.

To do so you first need to remove duplicate values from that column and then apply your migration query.

static final Migration MIGRATION_1_2 = new Migration(1, 2) {
     @Override
     public void migrate(SupportSQLiteDatabase database) {

      database.execSQL("Query to remove duplicate value in column")

      database.execSQL(“CREATE UNIQUE INDEX index_<MyTableName>_<ColumnName> ON <MyTableName> (<ColumnName>)”);
     }
 };

How to remove duplicate value from your column is up to your business logic.

If you don't remove duplicate before making column to unique index then you will face.

android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed

Collectives™ on Stack Overflow

Fatal Exception on Room DB Migration: android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related