Android Room Unique Constraint Not Working

Question

I have a DAO like this

@Dao
public interface ActivityDAO {

    @Insert
    void insert(Activity activity);

Entity is defined like this

@Entity(indices = {@Index(value = {"`id_from_client`"},
        unique = true)})
public class Activity {
    @PrimaryKey(autoGenerate = true)
    public int id;

    @NotNull
    @ColumnInfo(name = "id_from_client")
    public String id_from_client;

Despite having set an Index on id_from_client when I try to save objects with same id_from_client the insertion isn't rejected and the rows are added. Why is Room not enforcing the UNIQUE index constraint?

I can verify that the Index is created by exporting the database and viewing on DB Browser there's an Index with create statement CREATE UNIQUE INDEX index_Activity_id_id_from_clientONActivity (id, id_from_client).

Answer 1

Using your Entity with later libraries (2.4.3 and 2.5.0-beta01) does not successfully compile due to being enclosed in `. The resulting message being:-

error: `id_from_client` referenced in the index does not exists in the Entity. Available column names:id, id_from_client
public class Activity {
       ^

Changing to use:-

@Entity(indices = {@Index(value = {"id_from_client"}, /*<<<<<<<<<< removed enclosing `` */
    unique = true)})

Compiles successfully and the generated java has the appropriate CREATE INDEX as per:-

  @Override
  public void createAllTables(SupportSQLiteDatabase _db) {
    _db.execSQL("CREATE TABLE IF NOT EXISTS `Activity` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `id_from_client` TEXT NOT NULL)");
    _db.execSQL("CREATE UNIQUE INDEX IF NOT EXISTS `index_Activity_id_from_client` ON `Activity` (`id_from_client`)");
    _db.execSQL("CREATE TABLE IF NOT EXISTS room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)");
    _db.execSQL("INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, 'c0bc90551cfa5dbc362321d550999680')");
  }

• The generated java can be located by using the Android View. The respective class is the class that is the same as the class that is annotated with @Database but suffixed with _Impl.

Suggested actions

1. Remove the ` that enclose the indexed column
2. Use the latest Room library dependencies (2.4.3).

Demo

With the above changes then an attempt to insert a non-unique id_from_client fails as expected. Consider the following that is designed to test this :-

public class MainActivity extends AppCompatActivity {

    TheDatabase db;
    ActivityDAO dao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        db = TheDatabase.getInstance(this);
        dao = db.getActivityDAO();
        Activity a1 = new Activity();
        a1.id=0;
        a1.id_from_client = "100";

        dao.insert(a1);
        a1.id_from_client = "200";
        dao.insert(a1);
        SupportSQLiteDatabase sdb = db.getOpenHelper().getWritableDatabase();
        Cursor csr = sdb.query("SELECT * FROM sqlite_master");
        DatabaseUtils.dumpCursor(csr);

        dao.insert(a1);
        csr = sdb.query("SELECT * FROM sqlite_master");
        DatabaseUtils.dumpCursor(csr);
        csr.close();
    }
}

That is when run the first 2 Activity rows should be inserted, the schema (aka the sqlite_master will be extracted and dumped to the log). However the third insert as the id_from_client is still 200 will fail due to the UNIQUE conflict (in all three insert the id being 0 will result in the id column being generated).

• note that .allowMainThreadQueries has been used for brevity and convenience

The dependencies (in build.gradle(Module ....)) used for the demo being :-

dependencies {

    ....
    implementation 'androidx.room:room-runtime:2.4.3'
    ....
    annotationProcessor 'androidx.room:room-compiler:2.4.3'
}

The resultant log from running the above being :-

2022-11-01 06:33:57.745 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@17eeb83
2022-11-01 06:33:57.746 I/System.out: 0 {
2022-11-01 06:33:57.746 I/System.out:    type=table
2022-11-01 06:33:57.746 I/System.out:    name=android_metadata
2022-11-01 06:33:57.746 I/System.out:    tbl_name=android_metadata
2022-11-01 06:33:57.746 I/System.out:    rootpage=3
2022-11-01 06:33:57.746 I/System.out:    sql=CREATE TABLE android_metadata (locale TEXT)
2022-11-01 06:33:57.746 I/System.out: }
2022-11-01 06:33:57.746 I/System.out: 1 {
2022-11-01 06:33:57.746 I/System.out:    type=table
2022-11-01 06:33:57.746 I/System.out:    name=Activity
2022-11-01 06:33:57.746 I/System.out:    tbl_name=Activity
2022-11-01 06:33:57.746 I/System.out:    rootpage=4
2022-11-01 06:33:57.746 I/System.out:    sql=CREATE TABLE `Activity` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `id_from_client` TEXT NOT NULL)
2022-11-01 06:33:57.746 I/System.out: }
2022-11-01 06:33:57.746 I/System.out: 2 {
2022-11-01 06:33:57.746 I/System.out:    type=table
2022-11-01 06:33:57.746 I/System.out:    name=sqlite_sequence
2022-11-01 06:33:57.746 I/System.out:    tbl_name=sqlite_sequence
2022-11-01 06:33:57.747 I/System.out:    rootpage=5
2022-11-01 06:33:57.747 I/System.out:    sql=CREATE TABLE sqlite_sequence(name,seq)
2022-11-01 06:33:57.747 I/System.out: }
2022-11-01 06:33:57.747 I/System.out: 3 {
2022-11-01 06:33:57.747 I/System.out:    type=index
2022-11-01 06:33:57.747 I/System.out:    name=index_Activity_id_from_client
2022-11-01 06:33:57.747 I/System.out:    tbl_name=Activity
2022-11-01 06:33:57.747 I/System.out:    rootpage=6
2022-11-01 06:33:57.747 I/System.out:    sql=CREATE UNIQUE INDEX `index_Activity_id_from_client` ON `Activity` (`id_from_client`)
2022-11-01 06:33:57.747 I/System.out: }
2022-11-01 06:33:57.747 I/System.out: 4 {
2022-11-01 06:33:57.747 I/System.out:    type=table
2022-11-01 06:33:57.747 I/System.out:    name=room_master_table
2022-11-01 06:33:57.747 I/System.out:    tbl_name=room_master_table
2022-11-01 06:33:57.747 I/System.out:    rootpage=7
2022-11-01 06:33:57.747 I/System.out:    sql=CREATE TABLE room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)
2022-11-01 06:33:57.747 I/System.out: }
2022-11-01 06:33:57.747 I/System.out: <<<<<
2022-11-01 06:33:57.748 D/AndroidRuntime: Shutting down VM
2022-11-01 06:33:57.755 E/AndroidRuntime: FATAL EXCEPTION: main
    Process: a.a.so74267661javaroomuniqueindex, PID: 28425
    java.lang.RuntimeException: Unable to start activity ComponentInfo{a.a.so74267661javaroomuniqueindex/a.a.so74267661javaroomuniqueindex.MainActivity}: android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: Activity.id_from_client (code 2067 SQLITE_CONSTRAINT_UNIQUE)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3449)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3601)
        at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:85)
        at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:135)
        at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:95)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2066)
        at android.os.Handler.dispatchMessage(Handler.java:106)
        at android.os.Looper.loop(Looper.java:223)
        at android.app.ActivityThread.main(ActivityThread.java:7656)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:592)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:947)
     Caused by: android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: Activity.id_from_client (code 2067 SQLITE_CONSTRAINT_UNIQUE)
        at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
        at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:938)
        at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:790)
        at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:88)
        at androidx.sqlite.db.framework.FrameworkSQLiteStatement.executeInsert(FrameworkSQLiteStatement.java:51)
        at androidx.room.EntityInsertionAdapter.insert(EntityInsertionAdapter.java:64)
        at a.a.so74267661javaroomuniqueindex.ActivityDAO_Impl.insert(ActivityDAO_Impl.java:44)
        at a.a.so74267661javaroomuniqueindex.MainActivity.onCreate(MainActivity.java:33)
        at android.app.Activity.performCreate(Activity.java:7994)
        at android.app.Activity.performCreate(Activity.java:7978)
        at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1309)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3422)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3601) 
        at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:85) 
        at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:135) 
        at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:95) 
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2066) 
        at android.os.Handler.dispatchMessage(Handler.java:106) 
        at android.os.Looper.loop(Looper.java:223) 
        at android.app.ActivityThread.main(ActivityThread.java:7656) 
        at java.lang.reflect.Method.invoke(Native Method) 
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:592) 
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:947)