Android Room with deferred foreign key

Question

I have several tables that are periodically updated from the server (Benefit, Branch, Coupon) and two more tables that are only local (FavoriteBenefit, UsedCoupon). ER diagram looks like this:

Whenever a Benefit is deleted on the server, I also want to delete an appropriate entity from FavoriteBenefit. For that, I can use onDelete = ForeignKey.CASCADE and whenever parent Benefit no longer exists in the database, FavoriteBenefit gets deleted as well. Sounds good.

A problem arises whenever I use @Insert(onConflict = OnConflictStrategy.REPLACE) to update benefits in the database. REPLACE actually performs a DELETE and INSERT but DELETE internally triggers onDelete of FavoriteBenefit and as a result, all data in that table gets deleted as well.

(A similar problem occurs with Coupon and UsedCoupon tables.)

---

I am looking for a way to temporarily disable Foreign Key constraints until the end of the transaction. That is, do not validate Foreign Keys during the transaction but only at the end of the transaction. I still want Room to automatically delete entities that do not have a valid parent.

---

It seems that marking foreign key as defferred by setting deferred = true on the @ForeignKey definition should do exactly what I am trying to achieve.

boolean deferred ()

A foreign key constraint can be deferred until the transaction is complete. This is useful if you are doing bulk inserts into the database in a single transaction. By default, foreign key constraints are immediate but you can change it by setting this field to true.

But even when I set the deferred flag it seems to have no effect because FavoriteBenefit is still being deleted every time.

Am I understanding the deferred flag incorrectly?

Answer 1

I don't know if it's still relevant to you but I had a similar problem. I've tried to put deferred flag in both places: relation class itself and as a pragma. And in the both cases items were deleted due OnConflictStrategy.REPLACE strategy (which executes DELETE operation as you have mentioned). The workaround I've found is to use an "UPSERT-like" query. UPSERT statement support was added in SQLite last year, so Room does not support it yet, but you can write something like this:

@Dao
abstract class BaseDao<T> {

    /**
     * Insert an item in the database.
     *
     * @param item the item to be inserted.
     * @return The SQLite row id
     */
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(item: T): Long

    /**
     * Insert an array of items in the database.
     *
     * @param items the items to be inserted.
     * @return The SQLite row ids
     */
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(items: List<T>): List<Long>

    /**
     * Update an item from the database.
     *
     * @param item the item to be updated
     */
    @Update
    abstract fun update(item: T)

    /**
     * Update an array of items from the database.
     *
     * @param item the item to be updated
     */
    @Update
    abstract fun update(item: List<T>)

    @Transaction
    fun upsert(item: T) {
        val id = insert(item)
        if (id == -1L) {
            update(item)
        }
    }

    @Transaction
    fun upsert(items: List<T>) {
        val insertResult = insert(items)
        val updateList = mutableListOf<T>()

        for (i in insertResult.indices) {
            if (insertResult[i] == -1L) {
                updateList.add(items[i])
            }
        }

        if (updateList.isNotEmpty()) {
            update(updateList)
        }
    }
}

The logic behind the code is simple - if the table already contains records (this is checked after insert by filtering rowids) - we should update them.

Credits