How to create a table with a two or more foreign keys using Android Room?

Question

According the entity-relationship model, the relationship between tbl_post and tbl_category could be specified using Room Persistency Library as follows:

@Entity(foreignKeys = @ForeignKey(
    entity = TblPost.class,
    parentColumns = "id",
    childColumns = "tbl_post_id")
)
class TblPostCategory {
    @PrimaryKey
    public String id;

    @ColumnInfo(name = "user_id")
    public String postId;
}

However TblPostCategory depends on two foreign keys: post_id and category_id from TblPost and TbCategory.

How the relationship should be described using Room annotations?

Solution 1

TblCategory.java

@Entity
class TblCategory {
    @PrimaryKey
    @ColumnInfo(name="cat_id")
    public String id;

    @ColumnInfo(name = "cat_name")
    public String name;
}

TblPost.java (It is missing the foreign key reference but it is not important for the case)

@Entity
class TblPost {
    @PrimaryKey
    @ColumnInfo(name="post_id")
    public String id;

    public String title, content, create_time, author_id;
}

TblPostCategory.java

@Entity(foreignKeys = {
    @ForeignKey(
        entity = TblPost.class,
        parentColumns = "post_id",
        childColumns = "tbl_post_id"
    ),
    @ForeignKey(
        entity = TblCategory.class,
        parentColumns = "cat_id",
        childColumns = "tbl_category_id"
    )
})
class TblPostCategory {
    @PrimaryKey
    @ColumnInfo(name="tbl_post_id")
    public String id;

    @ColumnInfo(name = "tbl_category_id")
    public String categoryId;
}

Solution 2

In Kotlin:

@Entity(
    tableName = "some_table",
    indices = [Index("id"), Index("brand_id"), Index("model_id")],
    foreignKeys = [
        ForeignKey(entity = BrandEntity::class, parentColumns = ["id"],
            childColumns = ["brand_id"]),
        ForeignKey(entity = ModelEntity::class, parentColumns = ["id"],
            childColumns = ["model_id"]),
        ForeignKey(entity = Table1Entity::class, parentColumns = ["id"],
            childColumns = ["table1_id"]),
        ForeignKey(entity = Table2Entity::class, parentColumns = ["id"],
            childColumns = ["table2_id"])
    ]
)

Comments

[yalematta]

What if I only have tbl_category and tbl_post tables without tbl_post_category. And each Category has a list of Posts. I added a Foreign key to tbl_post referring to the category_id. But I still can't retrieve the List<Post>. I keep getting this error: android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787)

[joao86]

@yalematta what you are trying to do goes "against" database modelation rules. This is a situation of many to many relation (n:m). Read this: tomjewett.com/dbdesign/dbdesign.php?page=manymany.php. Nevertheless if you wish to add the List<Post> to tbl_category you don't need to define a foreign key in tbl_post. You only need to define Converters for the Post object because Room only accepts primitive types. See this: developer.android.com/training/data-storage/room/…

[Naveen Niraula]

How do I create a return type for this table having two foreign key(s)? The fetch was done using JOIN statement (maybe ?).

[joao86]

@NaveenNiraula what do you mean by return type? From TblPostCategory table you can only retrieve the ids from the other two tables. To retrieve other fields you need to use JOINS

[Naveen Niraula]

@joao86 yes I meant to ask how do I implement join in this exactly ?

[joao86]

It would probably be something like this select c.name from category c inner join post_category pc on c.id = pc.tbl_category_id where pc_tbl_post_id = 1 and pc_tbl_category_id=1