Dexter's log

When I started using foreign keys in SQLite, I was surprised to find an empty table after updating another one. I was using the REPLACE conflict resolution algorithm, and the empty table had foreign keys with cascading delete. This behavior was unexpected, so let’s dig in.

TL;DR: The REPLACE conflict resolution deletes the original row first and then inserts the new one. This triggers cascading delete on foreign keys that are set with such behavior. Use UPSERT instead. With Android’s Room, you can use the @Upsert annotation on your DAO:

@Upsert suspend fun upsert(entity: T): Long

Documentation

SQLite documentation for the REPLACE conflict resolution states: “When a UNIQUE or PRIMARY KEY constraint violation occurs, the REPLACE algorithm deletes pre-existing rows that are causing the constraint violation prior to inserting or updating the current row and the command continues executing normally.”. This is clear, documented, and confusing. What can we do instead? Use the UPSERT syntax!

Android’s Room implementation

I use Android’s Room persistence library , as a layer on top of SQLite, so I don’t have to fiddle with raw SQL syntax for everything. Here is a simple update to my base DAO interface; instead of @Insert(onConflict = OnConflictStrategy.REPLACE):

interface BaseDao<T> {
    @Insert(onConflict = OnConflictStrategy.REPLACE) suspend fun insert(entity: T): Long
    @Insert(onConflict = OnConflictStrategy.REPLACE) suspend fun insertAll(entities: Collection<T>)
}

I use the @Upsert:

interface BaseDao<T> {
    @Upsert suspend fun upsert(entity: T): Long
    @Upsert suspend fun upsertAll(entities: Collection<T>)
}

That’s all. Enjoy your upserts and non-disappearing records with foreign keys! And don’t forget to read documentation :)