Getting "UNIQUE constraint failed" when using @Upsert in Android Room

Question

Getting net.sqlcipher.database.SQLiteConstraintException: error code 19: UNIQUE constraint failed with : appointment.activity_id: when trying to do @Upsert with Android Room.

My understanding with @Upsert is that it will update records if a row exists or insert if it doesn't.

Here is the definition of the table:

data class AppointmentEntity(

   @PrimaryKey
   @field:ColumnInfo(name = "activity_id")
   val activityId: Long,

   @field:ColumnInfo(name = "selected_owner")
   val selectedOwner: String,

   val activityStartTimestamp: Instant,

   val activityEndTimestamp: Instant,

   val activityRevisionTimestamp: Instant?,

   val appointmentTypeCode: AppointmentTypeCode,

   val contactName: String?,

   val activityDescription: String?,

   val recurringId: Long?,

   val cancelled: Boolean,

   val personalAppointment: Boolean,

   val outlookIndicator: Boolean,

   val allDayEvent: Boolean,

   val deleteDate: Instant?
 )

Here is the code in the @Dao:

@Upsert
fun insertAppointments(appointment: List<AppointmentEntity>)

Why am I getting a unique constraint error. Wouldn't an existing record just be updated instead of reinserted?

From the android docs:

If the target entity contains a PrimaryKey that is auto generated, then the POJO class doesn't need an equal primary key field, otherwise primary keys must also be present in the POJO. If the primary key already exists, only the columns represented by the partial entity fields will be updated

Answer 1

There appears to be a conflict between Android room 2.5.0 and sqlcipher. As soon as I removed the sqlcipher SupportFactory from my room database the upsert worked as expected.

This appears to be the root cause: https://github.com/sqlcipher/android-database-sqlcipher/issues/588. SqlCipher isn't extending android.database.SQLException so when Room checks if the exception is a foreign/unique key here: https://android-review.googlesource.com/c/platform/frameworks/support/+/2191947/9/room/room-runtime/src/main/java/androidx/room/EntityUpsertionAdapter.kt#209 it just always throws the net.sqlcipher.database.SQLException Unique Key Exception rather than Upserting the existing record.