Constraint foreign key exception when having conflicting operations inside one transaction

[antailyaqwer]

Problem

If we have several tables with foreign key relations, then inside one transaction do some operations influences on each other and get exception android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY).

Unlikely, in Room library the same tables and relations such transaction is considered valid.

Environment: kabin: 0.1.0-alpha04;
SQLite version (according to documentation): 3.39 (API 34).

Example

We have 2 tables and a dao.

@Entity
data class UserEntity(
    @PrimaryKey
    val id: Int,
    val name: String
)

@Entity(
    primaryKeys = ["id", "userId"],
    foreignKeys = [
        ForeignKey(
            entity = UserEntity::class,
            parentColumns = ["id"],
            childColumns = ["userId"],
            onDelete = ForeignKey.Action.CASCADE,
            onUpdate = ForeignKey.Action.CASCADE
        )
    ],
)
data class UserAvatarEntity(
    val userId: Int,
    val id: String,
    val url: String
)

@Dao
interface IUserDaoSpecial {

    @Transaction
    suspend fun doSomeOperations() {
        val userEntity = UserEntitySpecial(
            id = 0,
            name = "John"
        )
        val userAvatarEntity = UserAvatarEntitySpec(
            userId = 0,
            id = "d64c2cd8-fd5f-4f06-844e-b4006b9464bb",
            url = "https://icon.jpg"
        )

        insertAvatar(userAvatarEntity)
        insertUser(userEntity)
    }

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertUser(user: UserEntitySpecial)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertAvatar(avatarEntitySpec: UserAvatarEntitySpec)
}

If we will invoke dao method, we will catch an exception.

android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY)

Expected behavior: successful operations inside one transaction.
I guess this error is connected with deferred flag in ForeignKey.

[tamimattafi]

According to this answer and many others, we need to make these changes:

1. Make all constraints DEFERRED inside one transaction
2. Adjust insert order for compounds and lists and call notifyQuery only after all SQL operations
3. Wrap database operations such as create, migrate, clear and drop inside a transaction and turn off foreign keys using PRAGMA

[tamimattafi]

1. Make all constraints DEFERRED inside one transaction

In order to make the first point work correctly, all foreign keys should be created as DEFERRABLE INITIALLY IMMEDIATE by default, and DEFERRABLE INITIALLY DEFERRED

Changed here: 022636f

This means that projects adopting these changes, should update their database version

[tamimattafi]

1. Make all constraints DEFERRED inside one transaction

Some deeper research showed that room uses PRAGMA syntax to make foreign keys DEFERRED inside a transaction, which seems like a better option than SET CONSTRAINTS ALL DEFERRED since it's rolled back once the transaction ends, and you don't have to do it manually.

More info: https://sqlite.org/pragma.html#pragma_defer_foreign_keys

[tamimattafi]

1- Make all constraints DEFERRED inside one transaction
3- Wrap database operations such as create, migrate, clear and drop inside a transaction and turn off foreign keys using PRAGMA

In order to achieve this type of control over foreign keys, KabinDatabaseConfiguration had to be changed to include more flags. configuration is now passed to Database, Scheme and Dao, which means this commit might introduce breaking changes for some projects.

Changed here: 132af15

[tamimattafi]

2. Adjust insert order for compounds and lists and call notifyQuery only after all SQL operations

Further research showed that notifyQuery inside one transaction delays triggers until the transaction is successful, therefore, these changes were postponed until future refactoring of the compiler and processor modules.