Room Dao Returns Insert IDs but Data is Missing from Database

Question

When saving a list of objects in my room database using a Dao

@Insert()
fun saveCharmRankMaterialCosts(materialCosts: List<CharmRankCraftingCost>) : List<Long>

And this is used from my repository class to save results from an API call:

val charmRankCosts = CharmRankCraftingCost.fromJsonCraftingCost(
    charmRankId.toInt(),
    jsonCharmRank.crafting
)

// save crafting/upgrade costs for the rank
val results = charmDao.saveCharmRankMaterialCosts(charmRankCosts)
Log.d("CharmRepository", "Saved charm material costs: ${results.toString()}");
assert(!results.contains(-1))

When running this code, insert ID's are returned and the assertion is never triggered (i.e. no inserts fail).

But when I inspect the data base on the device, most of the supposedly inserted IDs are missing from the table. I'm very confused as to what is going on here. I've debugged this issue for many hours and have been unsuccessful in getting this to work. Is there something obvious I'm missing?

Answer 1

The issue seems to have been related to foreign key constraints. I had a CharmRank data class with multiple related data objects. See below:

/**
 * Copyright Paul, 2020
 * Part of the MHW Database project.
 *
 * Licensed under the MIT License
 */
@Entity(tableName = "charm_ranks")
data class CharmRank(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "charm_rank_id")
    var id: Int = 0,

    @ColumnInfo(name = "charm_id")
    var charmId : Int,

    @ColumnInfo(name = "charm_rank_level")
    var level: Int = 0, // 3

    @ColumnInfo(name = "charm_rank_rarity")
    var rarity: Int = 0, // 6

    @ColumnInfo(name = "charm_rank_name")
    var name: String = "",

    @ColumnInfo(name = "craftable")
    var craftable: Boolean
)

Each charm rank has associated skills and items to craft said rank. These objects are simply relational objects in that they hold the ID of the CharmRank and a SkillRank in the case of the skills object, or the ID of the CharmRank and the ID of the Item object.

data class CharmRankSkill(

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "charm_rank_skill_id")
    var id: Int,

    var charmRankId : Int,

    var skillRankId: Int
)

data class CharmRankCraftingCost(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "charm_rank_crafting_cost_id")
    var id: Int,

    @ColumnInfo(name = "charm_rank_id")
    var charmRankId: Int,

    @ColumnInfo(name = "charm_rank_crafting_cost_item_quantity")
    val quantity: Int,

    val itemId: Int
)

Originally in CharmRankCraftingCost, I had a foreign key constraint on the Item object and the CharmRank object. Below is the foreign key constraint on the Item object:

ForeignKey(
  entity = Item::class,
  parentColumns = ["item_id"],
  childColumns = ["itemId"],
  onDelete = ForeignKey.CASCADE
)

The Item data object has IDs provided by the remote data source, so when I insert items into it's respective table, the conflict resolution is set to Replace. During the process of saving the relational items to the data base for the CharmRanks, I also have to save the Item objects prior to saving CharmRankCraftingCosts. It seems that what was happening is that when the Item objects are inserted, sometimes the items would get replaced, which would trigger the cascade action of the foreign key resulting in the CharmRankCraftingCosts items I just saved for the CharmRank to be deleted due to the cascading effect.

Removing the foreign key constraint on the Item table solved my issue.