2

I am trying to achieve the following:

enter image description here

I am working with Android Room.

In code:

@Entity(tableName = "BookReadingSchedule")
data class BookReadingSchedule(
    @PrimaryKey
    @ColumnInfo(name = "schedule_id")
    val id: Long = 0,
    val name: String = ""
)

@Entity(tableName = "Books")
data class Books(
    @PrimaryKey
    @ColumnInfo(name = "book_id")
    val id: Long = 0,
    val title: String = ""
)

@Entity(
    tableName = "BooksToRead"
    foreignKeys = [
        ForeignKey(
            entity = BookReadingSchedule::class,
            parentColumns = ["BookReadingSchedule_id"],
            childColumns = ["to_book_id"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class BooksToRead(
    @PrimaryKey
    @ColumnInfo(name = "id")
    val id: Long = 0,
    val title: String = ""
    @ColumnInfo(name = "to_book_id", index = true)
    val book_id: Long,
)

Trying to create this relationship, but, I am not sure whether this is entirely correct or not. I have worked with LINQ C# in MS, but, never done this much on Android. I am using Kotlin.

Links I checked (before I posted here):

EDIT: Idea is that for every schedule, there will be only a handful of books that were select to be included within that schedule.

Alix Blaine
  • 585
  • 2
  • 16

1 Answers1

1

Your @ColumnInfo(name = "schedule_id") should be parentColum for the corresponding table. So you will need something like this

@Entity(
    tableName = "BooksToRead"
    foreignKeys = [
        ForeignKey(
            entity = BookReadingSchedule::class,
            parentColumns = ["schedule_id"],
            childColumns = ["to_schedule_id"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = Books::class,
            parentColumns = ["book_id"],
            childColumns = ["to_book_id"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
Vojin Purić
  • 2,140
  • 7
  • 9
  • 22
  • Hi **Mr. Purić**. thank you for posting this. Can you possibly explain? E.g., if I delete a schedule then, I don't want to delete all of the books, but, I want the relation to be deleted. I see the CASCADE I hope it only deletes the relationship right? I am a bit confused and want to be sure. Thank you. – Alix Blaine Oct 06 '22 at 14:55
  • 1
    Foreign key with a cascade means that if a parent (in your cases book or schedule) gets deleted the records from the child table will get deleted also. So basicly opposite scenario of what you described in your comment. This has to be, because data in a child table must not be left with a key that does not exist when a parent is deleted in order to preserve databse integrity – Vojin Purić Oct 06 '22 at 17:41
  • **Purić**, is embedding better approach? – Alix Blaine Oct 06 '22 at 20:08
  • Embedded sql? definitely not necessary what you have is a pretty standard thing in SQL – Vojin Purić Oct 07 '22 at 07:05