5 Data Layer — Caching, Implementing the cross-reference table

📚 Official Books Real-World Android by Tutorials
1

Hi all,
I’m a little bit confused by this:

@Entity(
    primaryKeys = ["animalId", "tag"], // 1
    indices = [Index("tag")] // 2
)
data class CachedAnimalTagCrossRef(
    val animalId: Long,
    val tag: String
)
  1. You’re defining a composite primary key with animalId and tag. So, the primary key of this table is always a combination of the two columns.
  2. While primary keys are indexed by default, you’re explicitly indexing tag, and tag only. You need to index both, because you use both to resolve the relationship. Otherwise, Room will complain.

What’s happening here?

It has to do with how SQLite works. A query can use a composite index or a subset of that index, as long as the subset matches the original index from the beginning. So in this case, since the index is (animalId, tag), both (animalId) and (animalId, tag) are valid. If you change the primary key above to ["tag", "animalId"], then you’d have to index animalId instead of tag:

Could somebody expand on this matter? I don’t understand why we add explicitly to index the second column?It was said that we need to index both, because we use both to resolve the relationship…

Thanks in advance for any help.