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?

c0nst · Answer 1 · 2020-07-06T08:02:50.617

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

score 3 · Answer 2 · answered Aug 03 '20 at 08:49

The reason is that ON DELETE CASCADE is a trigger-like action and it's executed immediately. See the issue and the comment.

There's also a more detailed explanation from the PostgreSQL team:

Yeah, this is per SQL spec as far as we can tell. Constraint checks can be deferred till end of transaction, but "referential actions" are not deferrable. They always happen during the triggering statement. For instance SQL99 describes the result of a cascade deletion as being that the referencing row is "marked for deletion" immediately, and then

All rows that are marked for deletion are effectively deleted at the end of the SQL-statement, prior to the checking of any integrity constraints.

And take a look at the similar SQLite question which contains a few workarounds.

score 1 · Answer 3 · answered Oct 31 '19 at 09:34

I faced this problem before and I solved it by create new syntax called delsert.
Here is an example:

    @Query("DELETE FROM patient")
    public abstract void delete();

    @Query("DELETE FROM patient WHERE p_id IN (SELECT p_id FROM patient WHERE p_id NOT IN (:ids))")
    public abstract void deleteUnused(List<Long> ids);

    @Transaction
    public void delsert(List<Patient> patientList) {
        if (CommonUtils.isListEmpty(patientList)) {
            this.delete();
            return;
        }

        List<Long> idsPatient = new ArrayList<>();

        if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.N)
            patientList.forEach(order -> idsPatient.add(order.getId()));
        else
            for (Patient id : patientList) idsPatient.add(id.getId());

        deleteUnused(idsPatient);

        insert(patientList);
    }

Android Room with deferred foreign key

3 Answers3