Define relationships between objects

Because SQLite is a relational database, you can specify relationships between objects. Even though most object-relational mapping libraries allow entity objects to reference each other, Room explicitly forbids this. To learn about the technical reasoning behind this decision, see Understand why Room doesn't allow object references.

Define one-to-many relationships

Even though you cannot use direct relationships, Room still allows you to define Foreign Key constraints between entities.

For example, if there's another entity called Book, you can define its relationship to the User entity using the @ForeignKey annotation, as shown in the following code snippet:

Kotlin

@Entity(foreignKeys = arrayOf(ForeignKey(
            entity = User::class,
            parentColumns = arrayOf("id"),
            childColumns = arrayOf("user_id"))
       )
)
data class Book(
    @PrimaryKey val bookId: Int,
    val title: String?,
    @ColumnInfo(name = "user_id") val userId: Int
)

Java

@Entity(foreignKeys = @ForeignKey(entity = User.class,
                                  parentColumns = "id",
                                  childColumns = "user_id"))
public class Book {
    @PrimaryKey public int bookId;

    public String title;

    @ColumnInfo(name = "user_id") public int userId;
}

Since zero or more instances of Book can be linked to a single instance of User through the user_id foreign key, this models a one-to-many relationship between User and Book.

Foreign keys are very powerful, as they allow you to specify what occurs when the referenced entity is updated. For instance, you can tell SQLite to delete all books for a user if the corresponding instance of User is deleted by including onDelete = CASCADE in the @ForeignKey annotation.

Create nested objects

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.

For instance, your User class can include a field of type Address, which represents a composition of fields named street, city, state, and postCode. To store the composed columns separately in the table, include an Address field in the User class that is annotated with @Embedded, as shown in the following code snippet:

Kotlin

data class Address(
    val street: String?,
    val state: String?,
    val city: String?,
    @ColumnInfo(name = "post_code") val postCode: Int
)

@Entity
data class User(
    @PrimaryKey val id: Int,
    val firstName: String?,
    @Embedded val address: Address?
)

Java

public class Address {
    public String street;
    public String state;
    public String city;

    @ColumnInfo(name = "post_code") public int postCode;
}

@Entity
public class User {
    @PrimaryKey public int id;

    public String firstName;

    @Embedded public Address address;
}

The table representing a User object then contains columns with the following names: id, firstName, street, state, city, and post_code.

If an entity has multiple embedded fields of the same type, you can keep each column unique by setting the prefix property. Room then adds the provided value to the beginning of each column name in the embedded object.

Define many-to-many relationships

There is another kind of relationship which you often want to model in a relational database: a many-to-many relationship between two entities, where each entity can be linked to zero or more instances of the other. For instance, consider a music streaming app where users can organize their favorite songs into playlists. Each playlist can have any number of songs, and each song can be included in any number of playlists.

To model this relationship, you will need to create three objects:

  1. An entity class for the playlists.
  2. An entity class for the songs.
  3. An intermediate class to hold the information about which songs are in each playlist.

You can define the entity classes as independent units:

Kotlin

@Entity
data class Playlist(
    @PrimaryKey var id: Int,
    val name: String?,
    val description: String?
)

@Entity
data class Song(
    @PrimaryKey var id: Int,
    val songName: String?,
    val artistName: String?
)

Java

@Entity
public class Playlist {
    @PrimaryKey public int id;

    public String name;
    public String description;
}

@Entity
public class Song {
    @PrimaryKey public int id;

    public String songName;
    public String artistName;
}

Then, define the intermediate class as an entity containing foreign key references to both Song and Playlist:

Kotlin

@Entity(tableName = "playlist_song_join",
        primaryKeys = arrayOf("playlistId","songId"),
        foreignKeys = arrayOf(
                         ForeignKey(entity = Playlist::class,
                                    parentColumns = arrayOf("id"),
                                    childColumns = arrayOf("playlistId")),
                         ForeignKey(entity = Song::class,
                                    parentColumns = arrayOf("id"),
                                    childColumns = arrayOf("songId"))
                              )
        )
data class PlaylistSongJoin(
    val playlistId: Int,
    val songId: Int
)

Java

@Entity(tableName = "playlist_song_join",
        primaryKeys = { "playlistId", "songId" },
        foreignKeys = {
                @ForeignKey(entity = Playlist.class,
                            parentColumns = "id",
                            childColumns = "playlistId"),
                @ForeignKey(entity = Song.class,
                            parentColumns = "id",
                            childColumns = "songId")
                })
public class PlaylistSongJoin {
    public int playlistId;
    public int songId;
}

This produces a many-to-many relationship model that allows you to use a DAO to query both playlists by song and songs by playlist:

Kotlin

@Dao
interface PlaylistSongJoinDao {
    @Insert
    fun insert(playlistSongJoin: PlaylistSongJoin)

    @Query("""
           SELECT * FROM playlist
           INNER JOIN playlist_song_join
           ON playlist.id=playlist_song_join.playlistId
           WHERE playlist_song_join.songId=:songId
           """)
    fun getPlaylistsForSong(songId: Int): Array<Playlist>

    @Query("""
           SELECT * FROM song
           INNER JOIN playlist_song_join
           ON song.id=playlist_song_join.songId
           WHERE playlist_song_join.playlistId=:playlistId
           """)
    fun getSongsForPlaylist(playlistId: Int): Array<Song>
}

Java

@Dao
public interface PlaylistSongJoinDao {
    @Insert
    void insert(PlaylistSongJoin playlistSongJoin);

    @Query("SELECT * FROM playlist " +
           "INNER JOIN playlist_song_join " +
           "ON playlist.id=playlist_song_join.playlistId " +
           "WHERE playlist_song_join.songId=:songId")
    List<Playlist> getPlaylistsForSong(final int songId);

    @Query("SELECT * FROM song " +
           "INNER JOIN playlist_song_join " +
           "ON song.id=playlist_song_join.songId " +
           "WHERE playlist_song_join.playlistId=:playlistId")
    List<Song> getSongsForPlaylist(final int playlistId);
}