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?

score 35 · Accepted Answer · edited Nov 15 '17 at 23:52

35

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;
}

edited Nov 15 '17 at 23:52

JP Ventura

5,564
6
52
69

answered Aug 31 '17 at 21:15

joao86

2,056
1
21
23

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`. I keep getting this error: `android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787)` – yalematta Jan 22 '18 at 23:04
2

@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: http://www.tomjewett.com/dbdesign/dbdesign.php?page=manymany.php. Nevertheless if you wish to add the `List` 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: https://developer.android.com/training/data-storage/room/referencing-data.html – joao86 Jan 23 '18 at 10:43
How do I create a return type for this table having two foreign key(s)? The fetch was done using JOIN statement (maybe ?). – Naveen Niraula Sep 30 '18 at 05:53
@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 – joao86 Sep 30 '18 at 11:00
@joao86 yes I meant to ask how do I implement join in this exactly ? – Naveen Niraula Sep 30 '18 at 11:02
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` – joao86 Sep 30 '18 at 12:00

score 2 · Answer 2 · answered Jun 09 '20 at 10:30

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"])
    ]
)

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

2 Answers2

Linked