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).
CodePudding user response：
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
- Remove the ` that enclose the indexed column
- 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
.allowMainThreadQuerieshas 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)