Kotlin sqlite foreign key

ForeignKey

The android.arch Architecture Components packages are no longer maintained. They have been superseded by the corresponding androidx.* packages. See androidx.room.ForeignKey instead.

public abstract @interface ForeignKey
implements Annotation

android.arch.persistence.room.ForeignKey

Declares a foreign key on another Entity .

Foreign keys allows you to specify constraints across Entities such that SQLite will ensure that the relationship is valid when you modify the database.

When a foreign key constraint is specified, SQLite requires the referenced columns to be part of a unique index in the parent table or the primary key of that table. You must create a unique index in the parent entity that covers the referenced columns (Room will verify this at compile time and print an error if it is missing).

It is also recommended to create an index on the child table to avoid full table scans when the parent table is modified. If a suitable index on the child table is missing, Room will print MISSING_INDEX_ON_FOREIGN_KEY_CHILD warning.

A foreign key constraint can be deferred until the transaction is complete. This is useful if you are doing bulk inserts into the database in a single transaction. By default, foreign key constraints are immediate but you can change this value by setting deferred() to true . You can also use defer_foreign_keys PRAGMA to defer them depending on your transaction.

Please refer to the SQLite foreign keys documentation for details.

Summary

Nested classes

Constants definition for values that can be used in onDelete() and onUpdate() .

Источник

Android Native — How to add Foreign Keys to Room entities

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

Tools Required

Prerequisite Knowledge

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'

 @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 )

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

 @Database(entities = [Student::class, ReportCard::class], version = 1) abstract class MyRoomDB : RoomDatabase()

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

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)

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

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.

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

package com.codelab.daniwebandroidroomforeignkey import androidx.room.Database import androidx.room.RoomDatabase @Database(entities = [Student::class, ReportCard::class], version = 1) abstract class MyRoomDB : RoomDatabase()

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 )

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

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' >

Источник

Saved searches

Use saved searches to filter your results more quickly

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

Kotlin / anko Public archive

FOREIGN_KEY usage in SQLite createTable DSL #219

FOREIGN_KEY usage in SQLite createTable DSL #219

Comments

I find the current implementation of the FOREIGN_KEY function rather odd:

fun FOREIGN_KEY(columnName: String, referenceTable: String, referenceColumn: String): SqlType < return SqlTypeImpl("FOREIGN KEY($columnName) REFERENCES $referenceTable($referenceColumn)") >

Since createTable (the only way to create a foreign key in SQLite) takes vargs of Pair , if you want the following:

CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER, FOREIGN KEY(trackartist) REFERENCES artist(artistid) );

db.createTable("track", false, "trackid" to INTEGER, "trackname" to TEXT, "trackartist" to INTEGER, "" to FOREIGN_KEY("trackartist", "artist", "artistid"))

Wouldn’t it make more sense if FOREIGN_KEY returns a Pair instead? This seems much more natural to me:

db.createTable("track", false, "trackid" to INTEGER, "trackname" to TEXT, "trackartist" to INTEGER, FOREIGN_KEY("trackartist", "artist", "artistid"))

The text was updated successfully, but these errors were encountered:

Источник