cascade delete in android room database KOTLIN

Issue

There are a bunch of questions like this in StackOverflow but most of that arent about room database, so I had to ask a new question.

I have an app that uses room database and that has near 4 tables and a big relationship between those tables, so for instance when I delete a user in user list fragment, that user delete(only userName and some personal info) but the user's TRANSACTIONS and LOANS hadn't been deleted. Someone told me I have to use Cascade delete but I didn't find much info about it.

My User class model:

@Entity(tableName = "user_info")
data class UserInfo(
@PrimaryKey(autoGenerate = true)
@ColumnInfo(name = "user_id")
var userId: Long =0L,
@ColumnInfo(name = "full_name")
var fullName:String?,
@ColumnInfo(name= "account_id")
var accountId: String?,
@ColumnInfo(name = "mobile_number")
var mobileNumber:String?,
@ColumnInfo(name = "phone_number")
var phoneNumber:String?,
@ColumnInfo(name = "date_of_creation")
var dateOfCreation:String?,
@ColumnInfo(name = "address")
var address:String?,
)

Transactions model class:

@Entity(tableName = "transactions")
data class Transactions(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "trans_id")
    var transId: Long = 0L,
    @ColumnInfo(name = "user_id")
    var userId: Long?,
    @ColumnInfo(name = "create_date")
    var createDate: String?,
    @ColumnInfo(name = "bank_id")
    var bankId: Long?,
    @ColumnInfo(name = "description")
    var description: String?,
    @ColumnInfo(name = "increase")
    var increase: String?,
    @ColumnInfo(name = "decrease")
    var decrease: String?,
    @ColumnInfo(name = "loan_number")
    var loanNumber: String?,
    @ColumnInfo(name = "total")
    var total: Long?,
    @ColumnInfo(name = "type")
    var type: String?
)

User DAO:

 @Insert
suspend fun insert(ui: UserInfo): Long

@Update
suspend fun update(ui: UserInfo)

@Insert
suspend fun insertList(ui: MutableList<UserInfo>)

@Delete
suspend fun deleteUser(ui: UserInfo)

@Query("DELETE FROM user_info")
fun deleteAllUser()

@Query("SELECT user_info.user_id, user_info.full_name, transactions.total From user_info JOIN transactions ")
fun joinTable(): LiveData<List<UserAndMoney>>?

@Query("SELECT * from user_info WHERE user_id = :key")
fun get(key: Long): LiveData<UserInfo>?

@Query("SELECT * FROM user_info ORDER BY full_name DESC")
fun getAllUserInfo(): LiveData<List<UserInfo>>

@Query("SELECT * FROM user_info where full_name like '%' || :fullName || '%' ORDER BY full_name ASC")
fun searchUserName(fullName: String): LiveData<List<UserInfo>>

If It was not clear for you till now, let me makes it easy for you:

I need cascade delete that delets every thing about user and a record.

Solution

CASCADE is an option of a Foreign Key constraint. So you would need to define Foreign Key constraints. You define Foreign Key constraints in Room via the @Entity annotation.

As an example, as it would appear that a Transactions is related to a UserInfo via var userId: Long?, (column name user_id) you could have :-

@Entity(tableName = "transactions",
    foreignKeys = [
        ForeignKey(
            entity = UserInfo::class,
            parentColumns = ["user_id"],
            childColumns = ["user_id"],
            onDelete = ForeignKey.CASCADE, //<<<<<
            onUpdate = ForeignKey.CASCADE // Optional
        )
    ]
)
data class Transactions(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "trans_id")
    var transId: Long = 0L,
    @ColumnInfo(name = "user_id", index = true) // <<<<< best to have an index on the column, not required
    var userId: Long?,
    @ColumnInfo(name = "create_date")
    var createDate: String?,
    @ColumnInfo(name = "bank_id")
    var bankId: Long?,
    @ColumnInfo(name = "description")
    var description: String?,
    @ColumnInfo(name = "increase")
    var increase: String?,
    @ColumnInfo(name = "decrease")
    var decrease: String?,
    @ColumnInfo(name = "loan_number")
    var loanNumber: String?,
    @ColumnInfo(name = "total")
    var total: Long?,
    @ColumnInfo(name = "type")
    var type: String?
)

Note

The constraint enforces referential integrity, that is a transaction can not be inserted/updated if the user_id value is not a value that exists in the user_id column of the user_info table.

The CASCADE onUpdate will cascade a change to the user_id value in the user_info table to the respective transactions.

Additional

Someone told me I have to use Cascade delete but I didn't find much info about it.

What you have been told is incorrect. You could replicate the functionality without ON DELETE CASCADE or without the Foreign Key constraint.

You could use

@Query("DELETE FROM transaction WHERE user_id=:userId")
fun cascadeDeletionsFromUser(userId: Long)

• noting that if the Foreign Key constraint exists in the transactions table but didn't have an onDelete action specified, then the cascadeDeletionsFromUser function would have to be run before the user_info row is deleted. Otherwise the user_info row could not be deleted as the FK constraint would inhibit the deletion.

If you had an abstract class rather than interface then you could have:-

@Query("DELETE FROM user_info WHERE user_id=:userId")
abstract fun deleteUserById(userId: Long)

@Query("DELETE FROM transactions WHERE user_id=:userId")
abstract fun cascadeDeletionsFromUser(userId: Long)

@Transaction
@Query("")
fun deleteUserWithCascade(userId: Long) {
    cascadeDeletionsFromUser(userId)
    deleteUserById(userId)
}

and use the deleteUserWithCascade function to delete the transactions and user in one go.

It is more convenient to use ON DELETE CASCADE, and especially so if you have multiple depths of relationships (when it gets a little more complex ascertaining children)

Answered By - MikeT