What is the difference between @ForeignKey and @Relation annotations in Room database?

Question

I can't understand difference between those annotations. In my use case i want create one-to-many relation between tables. And found two options: one with @ForeignKey and another with @Relation

Also i found that if i update the row (e.g. with OnCoflictStrategy.Replace) i will lost foreign key for this row is it true?

Answer 1

While both of these concepts are used to bring structure to your Room database, their use case differs in that:

• @ForeignKey is used to enforce relational structure when INSERTING / MODYFING your data
• @Relation is used to enforce relational structure when RETRIEVING / VIEWING your data.

To better understand the need for ForeignKeys consider the following example:

@Entity
data class Artist(
    @PrimaryKey val artistId: Long,
    val name: String
)

@Entity
data class Album(
    @PrimaryKey val albumId: Long,
    val title: String,
    val artistId: Long
)

The applications using this database are entitled to assume that for each row in the Album table there exists a corresponding row in the Artist table. Unfortunately, if a user edits the database using an external tool or if there is a bug in an application, rows might be inserted into the Album table that do not correspond to any row in the Artist table. Or rows might be deleted from the Artist table, leaving orphaned rows in the Album table that do not correspond to any of the remaining rows in Artist. This might cause the application or applications to malfunction later on, or at least make coding the application more difficult.

One solution is to add an SQL foreign key constraint to the database schema to enforce the relationship between the Artist and Album table.

@Entity
data class Artist(
    @PrimaryKey val id: Long,
    val name: String
)

@Entity(
    foreignKeys = [ForeignKey(
        entity = Artist::class,
        parentColumns = arrayOf("id"),
        childColumns = arrayOf("artistId"),
        onUpdate = ForeignKey.CASCADE,
        onDelete = ForeignKey.CASCADE
    )]
)
data class Album(
    @PrimaryKey val albumId: Long,
    val title: String,
    val artistId: Long
)

Now whenever you insert a new album, SQL checks if there exists a artist with that given ID and only then you can go ahead with the transaction. Also if you update an artist's information or remove it from the Artist table, SQL checks for any albums of that artist and updates / deletes them. That's the magic of ForeignKey.CASCADE!

But this doesn't automatically make them return together during a Query, so enter @Relation:

// Our data classes from before
@Entity
data class Artist(
    @PrimaryKey val id: Long,
    val name: String
)

@Entity(
    foreignKeys = [ForeignKey(
        entity = Artist::class,
        parentColumns = arrayOf("id"),
        childColumns = arrayOf("artistId"),
        onUpdate = ForeignKey.CASCADE,
        onDelete = ForeignKey.CASCADE
    )]
)
data class Album(
    @PrimaryKey val albumId: Long,
    val title: String,
    val artistId: Long
)

// Now embedded for efficient querying
data class ArtistAndAlbums(
    @Embedded val artist: Artist,
    @Relation(
         parentColumn = "id",
         entityColumn = "artistId"
    )
    val album: List<Album> // <-- This is a one-to-many relationship, since each artist has many albums, hence returning a List here
)

Now you can easily fetch list of artists and their albums with the following:

@Transaction 
@Query("SELECT * FROM Artist")
fun getArtistsAndAlbums(): List<ArtistAndAlbums>

While previously you had to write long boilerplate SQL queries to join and return them.

Note: The @Transaction annotation is required to make SQLite execute the two search queries (one lookup in the Artist table and one lookup in the Album table) in one go and not separately.

Sources:

Excerpts from Android Developers Documentations:

Sometimes, you'd like to express an entity or data object as a cohesive whole in your database logic, even if the object contains several fields. In these situations, you can use the @Embedded annotation to represent an object that you'd like to decompose into its subfields within a table. You can then QUERY the embedded fields just as you would for other individual columns.

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

SQLite's ForeignKey documentation.

Answer 2

A @ForeignKey defines a constraint (aka rule) that requires that the child column(s) exist in the parent column(s). If an attempt is made to break that rule then a conflict occurs (which may be handled various ways by the onDelete/onUpdate definition).

An @Relationship is used to define a relationship where a number of child (perhaps Foreign Key children) objects are returned in the parent object.

Underneath it all @Relation automatically (effectively) joins the tables and generates the number of child objects. Whilst a @ForeignKey just affects the schema (with the exception of onDelete/onUpdate handling), it does not result in the respective tables being joined.

Perhaps Consider the following :-

Service Entity

@Entity(
    tableName = "services"
)
class Services {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "services_id")
    var id: Long = 0
    var service_date: String = ""
    var user_mobile_no: String = ""

}

and

ServiceDetail Entity :-

@Entity(
    tableName = "service_detail",
    foreignKeys = [
        ForeignKey(
            entity = Services::class,
            parentColumns = ["services_id"],
            childColumns = ["services_id"],onDelete = ForeignKey.SET_DEFAULT
        )
    ]
)
class ServiceDetail {

    @PrimaryKey
    var id: Long? = null;
    var services_id: Long = 0;
    @ColumnInfo(defaultValue = "1")
    var service_type_id: Long = 0;

    constructor()

    @Ignore
    constructor(services_id: Long, service_type_id: Long) {
        this.services_id = services_id
        this.service_type_id = service_type_id
    }
}

• This is saying that in order to add a ServiceDetail then the value of the services_id column MUST be a value that exists in the services_id column of the services Table, otherwise a conflict will happen. And additionally if a row is deleted from the services table then any rows in the service_detail table that reference that row will also be deleted (otherwise the row couldn;t be deleted from the services table).

Now consider this normal class (POJO), which is NOT an entity (aka table) :-

class ServiceWithDetail {

    @Embedded
    var services: Services? = null

    @Relation(entity = ServiceDetail::class,parentColumn = "services_id",entityColumn = "services_id")
    var serviceDetail: List<ServiceDetail>? = null
}

This is roughly saying when you ask for a ServiceWithDetail object then get a services object along with a list of the related service_detail objects

You would have a Dao such as :-

@Query("SELECT * FROM services")
fun getAllServices() :List<ServiceWithDetail>

So it will get all the services from the services table along with the related (i.e. where the services_id in the services_detail is the same as the services_id of the current services row being processed).

onConflictStrategy

REPLACE does the following :-

When a UNIQUE or PRIMARY KEY constraint violation occurs, the REPLACE algorithm deletes pre-existing rows that are causing the constraint violation prior to inserting or updating the current row and the command continues executing normally.

If a NOT NULL constraint violation occurs, the REPLACE conflict resolution replaces the NULL value with the default value for that column, or if the column has no default value, then the ABORT algorithm is used. If a CHECK constraint or foreign key constraint violation occurs, the REPLACE conflict resolution algorithm works like ABORT.

When the REPLACE conflict resolution strategy deletes rows in order to satisfy a constraint, delete triggers fire if and only if recursive triggers are enabled.

The update hook is not invoked for rows that are deleted by the REPLACE conflict resolution strategy. Nor does REPLACE increment the change counter. The exceptional behaviors defined in this paragraph might change in a future release.REPLACE

Hence, the potential for the behaviour that you have experienced. However, it depends upon what the update is doing. If the value for the ForeignKey(s) differ then they should, assuming there is no Foreign Key conflict replace the foreign key value with the new valid value. If the Foreign Key value(s) is(are) unchanged then replacement row will have the same Foreign Keys.