OnConflictStrategy.REPLACE causes a FOREIGN_KEY Constraint error

Question

I've got the following dao:

@Dao
public interface IncomeRecordWithTypeValueDao extends BaseDao<IncomeRecordWithTypeValue> {

    @Query("SELECT * FROM IncomeRecordWithTypeValue")
    Flowable<List<IncomeRecordWithTypeValue>> getAll();
}

@Dao
public interface BaseDao<T> {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    List<Long> synchronousInsertOrUpdate(List<T> objs);
}

My sqlite table looks like this:

CREATE TABLE "IncomeRecordWithTypeValue" (
    "id"    INTEGER NOT NULL,
    "amount"    REAL NOT NULL,
    "incomeRecordId"    INTEGER NOT NULL,
    "incomeTypeId"  INTEGER NOT NULL,
    FOREIGN KEY("incomeTypeId") REFERENCES "IncomeType"("id") ON DELETE CASCADE,
    FOREIGN KEY("incomeRecordId") REFERENCES "IncomeRecord"("id") ON DELETE CASCADE,
    PRIMARY KEY("id" AUTOINCREMENT)
)

Basically, want I want for testing purposes is to insert a row into the table if it does not exist, otherwise I want to update it. Thus, I'm trying to update all existing rows using the following statement:

incomeRecordWithTypeValueDao().getAll().subscribe(all -> {
    //all returns [IncomeRecordWithTypeValue{id=7, incomeRecordId=7, incomeTypeId=1, amount=55.0}, IncomeRecordWithTypeValue{id=8, incomeRecordId=8, incomeTypeId=1, amount=55.0}]
    List<Long> long = incomeRecordWithTypeValueDao().synchronousInsertOrUpdate(all);
}, err -> {
    err.printStackTrace();
    //returns android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (Sqlite code 787 SQLITE_CONSTRAINT_FOREIGNKEY), (OS error - 2:No such file or directory)
});

Is there something I'm missing. If I use insert and update seperatly it might work, but I aim to use both functions in one query (OnConflict.REPLACE)

EDIT:

I've got the following trigger:

CREATE TRIGGER Trigger_DeleteIncomeRecAfterExpenseTypeDelete 
AFTER DELETE ON IncomeRecordWithTypeValue
BEGIN 
DELETE FROM IncomeRecord  
WHERE id = OLD.incomeRecordId AND NOT EXISTS(SELECT 1 FROM IncomeRecordWithTypeValue irwtv WHERE irwtv.incomeRecordId = IncomeRecord.id);  END
 

That trigger causes the issue, as the system tries to delete the incomerecord and then adding the valuetypes into the same record which already gets deleted due to the trigger.

Answer 1

You may be able to apply the changes, assuming that the end result would be FK constraints that are not in conflict, but are temporarily in conflict, by using a deferred Foreign Key constraint. That is the constraint check is left until the transaction is committed.

For CREATE SQL you would use :-

FOREIGN KEY("incomeTypeId") REFERENCES "IncomeType"("id") ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
FOREIGN KEY("incomeRecordId") REFERENCES "IncomeRecord"("id") ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,

see https://sqlite.org/foreignkeys.html#fk_deferred

For the Entity you would code deferred = true as per https://developer.android.com/reference/androidx/room/ForeignKey#deferred()

Additional re comment/edit (TRIGGER) :-

I found what raises the issue. The issue is that I have a trigger (see answer edit) that removes the 'incomerecord' in case all 'valuetypes' get deleted. so even using deferred won't prevent the foreign key constraint issue unfourtantly. Maybe deleting the trigger and implementing the trigger in code is the way to go.

The issue here is that INSERT OR REPLACE works by deleting the row to be replaced and then inserting the row, thus assigning a new rowid or alias thereof (INTEGER PRIMARY KEY, with or without AUTOINCREMENET aka autogenerate = true will result in the column being an alias of the rowid).

If instead of INSERT OR REPLACE (onConflict = REPLACE in room) using INSERT OR IGNORE (onConflict = IGNORE) and testing the result (a Long in Kotlin), which will either be a positive value greater than 0 (row inserted) or -1 (row not inserted).

In the case of -1 (or < 1) then an UPDATE could then be invoked/run to perform the update of the existing row, thus maintaining the id and also not invoking the TRIGGER unexpectedly.

• note the above would not cater for negative rowid's which can be utilised. However, utilising negative rowid's is something that would have to be forced and beyond normal use of SQLite.