Android Native - How to add Foreign Keys to Room entities

dimitrilc 3 6K Views

Introduction

When working with Room, there might come a situation during bulk CRUD operations that it would be useful to add a Foreign Key to one of our tables. This provides two main benefits: cascading delete (must be configured manually) and enforcing data integrity on linked entities.

In this tutorial, we will learn how to add Foreign Keys into Room entities.

Goals

At the end of the tutorial, you would have learned:

  1. How to add Foreign Keys to Room entities.

Tools Required

  1. Android Studio. The version used in this tutorial is Bumblebee 2021.1.1 Patch 2.

Prerequisite Knowledge

  1. Intermediate Android.
  2. SQL.
  3. Basic Room database.
  4. Kotlin coroutines.

Project Setup

To follow along with the tutorial, perform the steps below:

  1. Create a new Android project with the default Empty Activity.

  2. Add the dependencies below for Room into the Module build.gradle.

     def room_version = "2.4.2"
 implementation "androidx.room:room-runtime:$room_version"
 kapt "androidx.room:room-compiler:$room_version"
 implementation "androidx.room:room-ktx:$room_version"
 implementation 'androidx.lifecycle:lifecycle-runtime-ktx:2.4.1'

  3. In the same file, add the kapt plugin under plugins

     id 'kotlin-kapt'

  4. Create a new @Entity called Student with the code below.

     @Entity
 data class Student(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    @ColumnInfo(name = "first_name") val firstName: String,
    @ColumnInfo(name = "last_name") val lastName: String
 )

  5. Create another @Entity called ReportCard with the code below.

     @Entity(tableName = "report_card")
 data class ReportCard(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    @ColumnInfo(name = "student_id") val studentId: Long
 )

  6. Create a new empty DAO for the Student entity.

     @Dao
 interface StudentDao {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun insertStudent(student: Student): Long

    @Query("SELECT * FROM student WHERE id=:id")
    suspend fun getStudentById(id: Long): Student?

 }

  7. Create the abstract class MyRoomDB with the code below.

     @Database(entities = [Student::class, ReportCard::class], version = 1)
 abstract class MyRoomDB : RoomDatabase() {
    abstract fun studentDao(): StudentDao
 }

  8. Append the code below to MainActivity onCreate(). This creates an instance of the database and then attempts to perform a query on the empty database. 

     val db = Room.databaseBuilder(
    applicationContext,
    MyRoomDB::class.java, "my-room-db"
 ).build()

 lifecycleScope.launch {
    db.studentDao().getStudentById(1)
 }

  9. For most of the tutorial, we will interact with the database via the Database Inspector, and not in code. The DAO query above performs a dummy connection so that the Database Inspector can keep the database connection open in debugging mode. Run the app in Debug mode and then switch to the App Inspection window. Verify that the database connection stays open. Later on, we can interact with the database directly using a Query tab.

1.png

Project Overview

For this tutorial, we are completely ignoring the frontend. We will only focus on the interaction with the database via Room.

We currently have two entities, Student and ReportCard. The Student entity is not aware of the ReportCard entity, but the ReportCard entity is dependent on the Student entity. Each ReportCard contains its own ID as well as the associated Student ID.

There is no foreign key constraint to Student in ReportCard, so it is possible that a ReportCard might be referencing a Student who does not exist in the student table. At the end of the tutorial, we should have created a Foreign Key for the ReportCard entity; this way we can ensure that each ReportCard can only reference a valid Student.

The Problem

First, in order to have a better understanding of the problems that a Foreign Key can solve, let us walk through an example where data integrity is violated.

The current student table is empty, but we can INSERT new ReportCard into report_card just fine, referencing non-existent students.

INSERT INTO report_card (id, student_id) VALUES
(1, 30),
(2, 2)

2.png

If we query the join for the two tables, we would receive nothing because those student_id do not exist in the student table.

SELECT * FROM report_card INNER JOIN student ON report_card.student_id=student.id

3.png

Foreign Key

Fortunately for us, we can create a Foreign Key to an entity so that the database can throw errors when we try to violate this constraint. One thing to keep in mind is that this does not prevent developers from writing code that violates the constraint; only via runtime testing that data inconsistencies will show up with a SQL exception.

To apply a Foreign Key, we can simply pass in ForeignKey objects to Entity’s foreignKey parameter. entity, childColumns, and parentColumns are required by ForeignKey.

@Entity(tableName = "report_card",
   foreignKeys = [ForeignKey(
       entity = Student::class,
       childColumns = ["student_id"],
       parentColumns = ["id"]
)])
data class ReportCard(
   @PrimaryKey(autoGenerate = true) val id: Long = 0,
   @ColumnInfo(name = "student_id") val studentId: Long
)

After adding the Foreign Key, you can re-install the App so Room can make use of the new constraint.

If we attempt to run the same INSERT into report_card again, Room will not allow us and throw an exception instead.

E/SQLiteQuery: exception: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY); query: INSERT INTO report_card (id, student_id) VALUES
    (1, 30),
     (2, 2)

In order for the INSERT into report_card to work, valid students must exist. INSERT new Students with the statement below.

INSERT INTO student VALUES
(2, "Mary", "Anne"),
(30, "John", "Doe")

After this, you can INSERT into report_card for the valid student IDs.

INSERT INTO report_card (id, student_id) VALUES
(1, 30),
(2, 2)

If we run the JOIN query again, we can see that it returns all of the data correctly.

4.png

Solution Code

MainActivity.kt

package com.codelab.daniwebandroidroomforeignkey

import androidx.appcompat.app.AppCompatActivity
import android.os.Bundle
import androidx.lifecycle.lifecycleScope
import androidx.room.Room
import kotlinx.coroutines.launch

class MainActivity : AppCompatActivity() {
   override fun onCreate(savedInstanceState: Bundle?) {
       super.onCreate(savedInstanceState)
       setContentView(R.layout.activity_main)

       val db = Room.databaseBuilder(
           applicationContext,
           MyRoomDB::class.java, "my-room-db"
       ).build()

       lifecycleScope.launch {
           db.studentDao().getStudentById(1)
       }

   }
}

MyRoomDB.kt

package com.codelab.daniwebandroidroomforeignkey

import androidx.room.Database
import androidx.room.RoomDatabase

@Database(entities = [Student::class, ReportCard::class], version = 1)
abstract class MyRoomDB : RoomDatabase() {
   abstract fun studentDao(): StudentDao
   abstract fun reportCardDao(): ReportCardDao
}

ReportCard.kt

package com.codelab.daniwebandroidroomforeignkey

import androidx.room.ColumnInfo
import androidx.room.Entity
import androidx.room.ForeignKey
import androidx.room.PrimaryKey

@Entity(tableName = "report_card",
   foreignKeys = [ForeignKey(
       entity = Student::class,
       childColumns = ["student_id"],
       parentColumns = ["id"]
)])
data class ReportCard(
   @PrimaryKey(autoGenerate = true) val id: Long = 0,
   @ColumnInfo(name = "student_id") val studentId: Long
)

Student.kt

package com.codelab.daniwebandroidroomforeignkey

import androidx.room.ColumnInfo
import androidx.room.Entity
import androidx.room.PrimaryKey

@Entity(tableName = "student")
data class Student(
   @PrimaryKey(autoGenerate = true) val id: Long = 0,
   @ColumnInfo(name = "first_name") val firstName: String,
   @ColumnInfo(name = "last_name") val lastName: String
)

StudentDao.kt

package com.codelab.daniwebandroidroomforeignkey

import androidx.room.Dao
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.Query

@Dao
interface StudentDao {

   @Insert(onConflict = OnConflictStrategy.IGNORE)
   suspend fun insertStudent(student: Student): Long

   @Query("SELECT * FROM student WHERE id=:id")
   suspend fun getStudentById(id: Long): Student?

}

Module build.gradle

plugins {
   id 'com.android.application'
   id 'org.jetbrains.kotlin.android'
   id 'kotlin-kapt'
}

android {
   compileSdk 32

   defaultConfig {
       applicationId "com.codelab.daniwebandroidroomforeignkey"
       minSdk 21
       targetSdk 32
       versionCode 1
       versionName "1.0"

       testInstrumentationRunner "androidx.test.runner.AndroidJUnitRunner"
   }

   buildTypes {
       release {
           minifyEnabled false
           proguardFiles getDefaultProguardFile('proguard-android-optimize.txt'), 'proguard-rules.pro'
       }
   }
   compileOptions {
       sourceCompatibility JavaVersion.VERSION_1_8
       targetCompatibility JavaVersion.VERSION_1_8
   }
   kotlinOptions {
       jvmTarget = '1.8'
   }
}

dependencies {
   //Room deps
   def room_version = "2.4.2"
   implementation "androidx.room:room-runtime:$room_version"
   kapt "androidx.room:room-compiler:$room_version"
   implementation "androidx.room:room-ktx:$room_version"
   implementation 'androidx.lifecycle:lifecycle-runtime-ktx:2.4.1'

   implementation 'androidx.core:core-ktx:1.7.0'
   implementation 'androidx.appcompat:appcompat:1.4.1'
   implementation 'com.google.android.material:material:1.5.0'
   implementation 'androidx.constraintlayout:constraintlayout:2.1.3'
   testImplementation 'junit:junit:4.13.2'
   androidTestImplementation 'androidx.test.ext:junit:1.1.3'
   androidTestImplementation 'androidx.test.espresso:espresso-core:3.4.0'
}

Summary

We have learned how to create Foreign Keys on Room Entities. The full project code can be found at https://github.com/dmitrilc/DaniwebAndroidRoomForeignKey

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Edit Preview