Room's `onDelete = CASCADE` not working during a migration

Question

I have the following tables:

@Entity(
    tableName = "users"
)
class Users {
    @PrimaryKey(autoGenerate = true)
    var id: Long? = null

    @NonNull
    var name: String? = null
}

@Entity(
    tableName = "pets",
    foreignKeys = [
        ForeignKey(
            entity = Users::class,
            parentColumns = ["id"],
            childColumns = ["owner_id"],
            onDelete = ForeignKey.CASCADE
        )
    ]
)
class Pets {
    @PrimaryKey(autoGenerate = true)
    var id: Long? = null

    @NonNull
    var name: String? = null

    @ColumnInfo(name = "owner_id")
    var ownerId: Long? = null
}

When I run a migration that deletes all users table rows, the pets table is not affected. The rows are not automatically deleted.

object Migration_1_2 : Migration(1, 2) {

    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("""
            DELETE FROM users
        """)
    }
}

Even when I execute the following snippet of code before the migration it does not work.

database.execSQL("PRAGMA foreign_keys=ON;");

What should I do to make the onDelete = ForeignKey.CASCADE work?

Answer 1

As @sergiytikhonov indicates in his comment, enabling the foreign_keys constraint in a migration function has no effect. This is because migrations are executed as part of a transaction and the pragma is a no-op inside a transaction.

I don't see any way to get control and enable foreign_keys before the migration is executed. I think your only option is to delete the pets explicitly as part of the migration:

override fun migrate(database: SupportSQLiteDatabase) {
    database.execSQL("""
        DELETE FROM pets WHERE owner_id IN (SELECT id FROM users)
    """)

    database.execSQL("""
        DELETE FROM users
    """)
}