Search code examples
foreign-keysandroid-roomcomposite-key

How to use foreign key with composite keys in android room inside an entity, which only saves one key and the other one is given implicitly

Let's say I have a Category Entity, of which only two types exist (category A and category B). The primary key is a composite key consisting of the category name and a boolean stating, if the category belongs to category A (B otherwise).

An Item can belong to these two category types at the same time. In my Item Entity, I therefore have two foreign keys (categoryAName and categoryBName), both of which may be null, if they don't belong to any category. Is there a way to map the foreign key with its two composite keys inside the Item Entity, if the category type is only given implicitly?

My code looks as follows:

@Entity(tableName = "category_table", primaryKeys = ["name", "isCategoryA"])
data class CategoryEntity(val name: String, val isCategoryA: Boolean)


@Entity(
    tableName = "item_table",
    foreignKeys = [ForeignKey(
        entity = CategoryEntity::class,
        parentColumns = ["name", "isCategoryA"],
        childColumns = ["categoryAName", "???"], // how would one set the colum, if we don't want to save the value, when it's known implicitly?
        onDelete = ForeignKey.SET_NULL,
        onUpdate = ForeignKey.CASCADE
    ), ForeignKey(
        entity = CategoryEntity::class,
        parentColumns = ["name", "isCategoryA"],
        childColumns = ["categoryBName", "???"], // isCategoryA would always be false
        onDelete = ForeignKey.SET_NULL,
        onUpdate = ForeignKey.CASCADE
    )]
)
data class ItemEntity(
    @PrimaryKey val name: String,
    var categoryAName: String?,
    var categoryBName: String?
)

Is there a way to achieve this, without adding two additional columns, which just contain redundant information? Or is there a better way to implement this in general?

Solution

  • Or is there a better way to implement this in general?

    I believe the better way is to incorporate a many-many relationship and thus a table (Entity) that facilitates this. Although I do find the explanation you provide difficult to understand in that you are saying that Categories belong to a Category.

    That is you either have 2 or 4 categories (the example assumes 4 each either belong to itself or the other).

    An Item would appear to be able to have 1,2, 3 or 4 Categories.

    As such you could have :-

    • an Entity for the Categories
    • an Entity for the Items
    • and an Entity for the mapping of an Item to the 4 (or 2) Categories that it may have.

    So :-

    CategoryEntity could be :-

    @Entity(tableName = "category_table",
    indices = [
        Index(
            value = ["name","isCategoryA"],
            unique = true
        )
    ])
data class CategoryEntity(
    @PrimaryKey(autoGenerate = true) val categoryId: Long,
    val name: String,
    val isCategoryA: Boolean
    )
    • the addition of an id column that uniquely identifies the category.

    ItemEntity could be :-

    @Entity(
    tableName = "item_table"
)
data class ItemEntity(
    @PrimaryKey val name: String
)
    • greatly simplified

    ItemCategoryMap

    @Entity(
        primaryKeys = ["categoryId","itemName"],
    foreignKeys = [
        ForeignKey(
            entity = CategoryEntity::class,
            parentColumns = ["categoryId"],
            childColumns = ["categoryId"]
        ),
        ForeignKey(
            entity = ItemEntity::class,
            parentColumns = ["name"],
            childColumns = ["itemName"]
        )
    ]
)
data class ItemCategoryMap(
    val categoryId: Long, val itemName: String
)

    A POJO that combines the Item with the respective categories as per the map table ItemWithCategories :-

    data class ItemWithCategories(
    @Embedded
    val item: ItemEntity,
    @Relation(
        entity = CategoryEntity::class,
        entityColumn = "categoryId",
        parentColumn = "name",
        associateBy = Junction(
            ItemCategoryMap::class,
            parentColumn = "itemName",
            entityColumn = "categoryId")
    )
    val categories: List<CategoryEntity>
)

    A Dao AllDao :-

    @Dao
interface AllDao {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(categoryEntity: CategoryEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(itemEntity: ItemEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(itemCategoryMap: ItemCategoryMap): Long
    @Transaction
    @Query("SELECT * FROM item_table")
    fun getAllItemsWithCategories(): List<ItemWithCategories>
    @Query("SELECT name FROM ITEM_TABLE WHERE rowid=:rowid")
    fun getItemNameByRowid(rowid: Long): String
}

    an @Database TheDatabase :-

    @Database(entities = [CategoryEntity::class, ItemEntity::class, ItemCategoryMap::class], version = 1, exportSchema = false)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDao(): AllDao

    companion object {
        private var instance: TheDatabase? = null
         public fun getInstance(context: Context): TheDatabase {
             if (instance == null) {
                 instance = Room.databaseBuilder(context,TheDatabase::class.java,"mydb")
                     .allowMainThreadQueries()
                     .build()
             }
             return instance as TheDatabase
         }
    }
}
    • note for demo run on main thread

    An finally an Activity that puts it all together and outputs the extracted Items with the Categories (0-4) :-

    class MainActivity : AppCompatActivity() {

    lateinit var db: TheDatabase
    lateinit var dao: AllDao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = TheDatabase.getInstance(this)
        dao = db.getAllDao()

        val catATrueID =dao.insert(CategoryEntity(0,"CatA",true))
        val catAFalseId = dao.insert(CategoryEntity(0,"CatA",false))
        val catBTrueId = dao.insert(CategoryEntity(0,"CatB",true))
        val catBFalseId = dao.insert(CategoryEntity(0,"CatB",false))
        val catOoops = dao.insert(CategoryEntity(0,"CatB",false))

        val item1name = dao.getItemNameByRowid(dao.insert(ItemEntity("Item1")))
        dao.insert(ItemCategoryMap(catATrueID,item1name))
        dao.insert(ItemCategoryMap(catAFalseId,item1name))
        dao.insert(ItemCategoryMap(catBTrueId,item1name))
        dao.insert(ItemCategoryMap(catBFalseId,item1name))

        val item2name = dao.getItemNameByRowid(dao.insert(ItemEntity("Item2")))
        dao.insert(ItemCategoryMap(catBFalseId,item2name))
        dao.insert(ItemCategoryMap(catATrueID,item2name))

        val item3name = dao.getItemNameByRowid(dao.insert(ItemEntity("Item3")))
        dao.insert(ItemCategoryMap(catBTrueId,item3name))

        val item4name = dao.getItemNameByRowid(dao.insert(ItemEntity("item4")))

        for(iwc: ItemWithCategories in dao.getAllItemsWithCategories()) {
            Log.d("DBINFO",
                "Item is ${iwc.item.name} has ${iwc.categories.size} categories." )
            for (c: CategoryEntity in iwc.categories) {
                Log.d("DBINFO","Category is ${c.name} ID is ${c.categoryId} IsCategoryA is ${c.isCategoryA}")
            }
        }
    }
}

    Result (only designed to be run once for the demo) :-

    2021-06-24 14:56:02.614 D/DBINFO: Item is Item1 has 4 categories.
2021-06-24 14:56:02.614 D/DBINFO: Category is CatA ID is 1 IsCategoryA is true
2021-06-24 14:56:02.614 D/DBINFO: Category is CatA ID is 2 IsCategoryA is false
2021-06-24 14:56:02.615 D/DBINFO: Category is CatB ID is 3 IsCategoryA is true
2021-06-24 14:56:02.615 D/DBINFO: Category is CatB ID is 4 IsCategoryA is false
2021-06-24 14:56:02.615 D/DBINFO: Item is Item2 has 2 categories.
2021-06-24 14:56:02.615 D/DBINFO: Category is CatB ID is 4 IsCategoryA is false
2021-06-24 14:56:02.615 D/DBINFO: Category is CatA ID is 1 IsCategoryA is true
2021-06-24 14:56:02.615 D/DBINFO: Item is Item3 has 1 categories.
2021-06-24 14:56:02.615 D/DBINFO: Category is CatB ID is 3 IsCategoryA is true
2021-06-24 14:56:02.615 D/DBINFO: Item is item4 has 0 categories.

    Addtional

    Regarding

    As shown with my code example, an item may belong to only ONE category for EACH type. Therefore, the item may only belong to 2 categories at most. Moreover, two categories may have the same name, but ONLY, if they belong to a different type (that's why there is a composite key). There should be no two categories with the same name, having the same type.

    Then you can limit to a unique category name by having a unique index on the category name item name combination. As such the ItemCategoryMap can be amended to be :-

    @Entity(
        primaryKeys = ["categoryId","itemName"],
    indices = [Index("itemName","categoryName",unique = true)] /* ADDED FOR ADDITIONAL */,
    foreignKeys = [
        ForeignKey(
            entity = CategoryEntity::class,
            parentColumns = ["categoryId"],
            childColumns = ["categoryId"]
        ),
        ForeignKey(
            entity = ItemEntity::class,
            parentColumns = ["name"],
            childColumns = ["itemName"]
        )
    ]
)
data class ItemCategoryMap(
    val categoryId: Long , val categoryName: String /* ADDED FOR ADDITIONAL */, val itemName: String
)

    As the category name is now required then the category name is required as an index has to be specific to a single table. As such when inserting the categoryName is required.

    So amending the invoking (MainActivity) to be changed to use (rest of the code is as it was) :-

        val item1name = dao.getItemNameByRowid(dao.insert(ItemEntity("Item1")))
    dao.insert(ItemCategoryMap(catATrueID,"CatA",item1name))
    dao.insert(ItemCategoryMap(catAFalseId,"CatA",item1name))
    dao.insert(ItemCategoryMap(catBTrueId,"CatB",item1name))
    dao.insert(ItemCategoryMap(catBFalseId,"CatB",item1name))

    val item2name = dao.getItemNameByRowid(dao.insert(ItemEntity("Item2")))
    dao.insert(ItemCategoryMap(catBFalseId,"CatB",item2name))
    dao.insert(ItemCategoryMap(catATrueID,"CatA",item2name))

    val item3name = dao.getItemNameByRowid(dao.insert(ItemEntity("Item3")))
    dao.insert(ItemCategoryMap(catBTrueId,"CatB",item3name))

    val item4name = dao.getItemNameByRowid(dao.insert(ItemEntity("item4")))

    The Result is now :-

    2021-06-25 10:01:22.671 D/DBINFO: Item is Item1 has 2 categories.
2021-06-25 10:01:22.671 D/DBINFO: Category is CatA ID is 1 IsCategoryA is true
2021-06-25 10:01:22.671 D/DBINFO: Category is CatB ID is 3 IsCategoryA is true
2021-06-25 10:01:22.671 D/DBINFO: Item is Item2 has 2 categories.
2021-06-25 10:01:22.672 D/DBINFO: Category is CatA ID is 1 IsCategoryA is true
2021-06-25 10:01:22.672 D/DBINFO: Category is CatB ID is 4 IsCategoryA is false
2021-06-25 10:01:22.672 D/DBINFO: Item is Item3 has 1 categories.
2021-06-25 10:01:22.672 D/DBINFO: Category is CatB ID is 3 IsCategoryA is true
2021-06-25 10:01:22.672 D/DBINFO: Item is item4 has 0 categories.

    That is:-

    • The attempt to insert a duplicate CatB false using val catOoops = dao.insert(CategoryEntity(0,"CatB",false)) is IGNORED so the duplicate is added (i.e. just the 4 Category rows) as per :-

    • enter image description here

    • Item1 has only the first CatA (i.e. CatA true, the CatA false) was IGNORED. Likewise for CatB.

    • Item2 has both as no rules were broken when inserting both.

    • Item3 has just the one no rules were broken.

    • Item has none.

    If you wanted to get Categories with the associated Items then you could have CategoryWithItems :-

    data class CategoryWithItems (
    @Embedded
    val category: CategoryEntity,
    @Relation(
        entity = ItemEntity::class,
        entityColumn = "name",
        parentColumn = "categoryId",
        associateBy = Junction(
            ItemCategoryMap::class,
            parentColumn = "categoryId",
            entityColumn = "itemName")
    )
    val items: List<ItemEntity>
)

    And a dao such as :-

    @Query("SELECT * FROM category_table")
fun getAllCategoriesWithItems(): List<CategoryWithItems>

    Using (with the data loaded above) :-

        for(cwi: CategoryWithItems in dao.getAllCategoriesWithItems()) {
        Log.d("DBINFO", "Category is ${cwi.category.name} isCategoryA is ${cwi.category.isCategoryA} id is ${cwi.category.categoryId}, it has ${cwi.items.size} associated Items")
        for(i: ItemEntity in cwi.items) {
            Log.d("DBINFO","\tItem is ${i.name}")
        }
    }

    The result would be :-

    2021-06-25 10:53:24.852 D/DBINFO: Category is CatA isCategoryA is true id is 1, it has 2 associated Items
2021-06-25 10:53:24.852 D/DBINFO:   Item is Item1
2021-06-25 10:53:24.852 D/DBINFO:   Item is Item2
2021-06-25 10:53:24.852 D/DBINFO: Category is CatA isCategoryA is false id is 2, it has 0 associated Items
2021-06-25 10:53:24.853 D/DBINFO: Category is CatB isCategoryA is true id is 3, it has 2 associated Items
2021-06-25 10:53:24.853 D/DBINFO:   Item is Item1
2021-06-25 10:53:24.853 D/DBINFO:   Item is Item3
2021-06-25 10:53:24.853 D/DBINFO: Category is CatB isCategoryA is false id is 4, it has 1 associated Items
2021-06-25 10:53:24.853 D/DBINFO:   Item is Item2

    of course WHERE clauses could be utilised.

    NOTE/WARNING the use in the code of

    val item1name = dao.getItemNameByRowid(dao.insert(ItemEntity("Item1")))

    (etc i.e. getting the name of the inserted item via the rowid will result in a failure if rerun, it's use is just intended to simplify the demo code).

    The above solution also caters for more that just the 2 category names, that is there is no limit to the category names (other than storage limitations).