7. Mastering Relations

Written by Aldo Olivares

In the previous chapter, you learned all you need to know about tables, entities and annotations. You also learned how to create your database and how to get a runtime instance of it by using the Room.databaseBuilder method.

In this chapter, you are going to learn even more about entities by creating relations between them using foreign keys and the @Relation annotation. Along the way, you will learn:

• How to create a relationship using primary keys and foreign keys.
• How to define a one to many relationship in Room.
• How to represent different kinds of relationships using entity-relationship diagrams.
• How to use the @Embedded annotation.
• How to use the @ForeignKey annotation.
• How to use the @Relationship annotation.

Ready? Let’s get started.

Note: This chapter assumes you have basic knowledge of Kotlin and Android. If you’re new to Android, check out our Android tutorials. If you know Android, but are unfamiliar with Kotlin, take a look at Kotlin For Android: An Introduction.

Getting started

If you are following along with your own app, open it up. If not, don’t worry you can use the starter project for this chapter, which you can find in the attachments. Now, open the app in Android Studio 3.2 or greater by going to File ▸ New ▸ Import Project, and selecting the build.gradle file in the root of the project.

Once the starter project finishes loading and building, run the app on a device or emulator.

Great! The app is working as expected.

The code is basically the same as the previous chapter. But, if you are just getting started, here is a quick recap of the packages and the code:

• The data package contains the db package and the model package. The db package contains the class that creates your Room database, while the model package contains all of the code for the entities created in the previous chapter.
• The view package contains the code for all of the activities of your app.

You are no doubt eager to start writing some code. But, before that, you will need to learn a bit of theory first!

Relations and entity-relationship diagrams

In this chapter, we are going to create a relation between the Question entity and the Answer entity that you created in the previous chapter. The only problem is… you know… relationships are always hard to understand even if it is just between two single tables. Therefore, in this section, we are going to talk about a little tool that will help you to better understand the different kinds of relations between tables: entity-relationship diagrams.

You might have heard of entity-relationship diagrams before. They are quite common in software design and it’s one of the first things they teach in college in courses such as Databases 101 or Introduction to Relational Databases. An entity-relationship diagram, ER diagram or ERD is a kind of flowchart that illustrates the relationships between the components of a system representing something like a school or a company using a set of symbols that include rectangles, ovals and connecting lines.

ER diagrams are commonly created during the initial design of a database schema to determine the tables, their fields and the nature of the relationship between them.

Many different ERD notations have been created over the years to serve different purposes. The notation that we are using in this section is called Crow’s Foot notation. Although ER diagrams may have different elements depending on the notation system, they usually share similar components that include the following:

Entity

Represents a component, object or a concept of a system. Concepts described by an entity can be concrete, such as a student or a car, or abstract, such as an event or a schedule. Entities are translated as tables when creating your database schema. They are commonly illustrated as rectangles in most ER diagrams:

Entities in Crow’s Foot notation also include a list of attributes or properties that define them. For a user entity, its attributes could be username, password and email.

Relationship

A relationship tells you how two entities interact with each other and it’s usually represented as a verb surrounded by a diamond. For example, think about a student entity and a class entity. Their relationship could be described as follows:

In this ER diagram, the relationship is described as “takes” and you could read it left to right:

A student takes a class.

Or right to left:

A class is taken by a student.

Note: Not all ER diagrams illustrate the relationship between their entities since it is often easy to infer from the context. In Crow’s Foot notation, it is usually omitted.

Cardinality

Last but not least, the cardinality tells you the kind of relationship two entities have. There are three main cardinal relationships:

One to one: When one entity can only be related to one and only one instance of the other entity. For example, a department on a company can only have one head of department, and that head of department can only lead one department:

One to many: When one entity can be related to many instances of another entity. For example, a teacher can teach many classes in a single semester, but a class can only have one teacher:

Many to many: When many instances of an entity can also be related to many instances of another entity. For example, a book (like this one) can have many authors, and authors can write many books:

Cardinalities can also have constraints that indicate the minimum and maximum numbers in the relationships: One and only one, zero or one, zero or many and one or many.

Here is the full list of cardinalities and constraints that you can find:

Now that you know how ER diagrams work, you will now learn how to create relationships using Roms Entities.

Creating your relations

As briefly mentioned in the previous chapter, you will only need to create one relationship between the entities in your app, and its ER diagram looks like this:

Why don’t you use your recently acquired knowledge to guess which kind of relationship is this?

Well, if you said “a one to many relationship,” you are correct.

With the above ER diagram, you are saying: “One question can have one or more answers and each answer can only be related to one and only one question”. Just think about it and it makes a lot of sense since each question of the DroidQuiz app will have at least one correct answer and two incorrect answers. The beauty of this design is that you can easily expand it or modify it to have something like two correct answers and two incorrect answers or 3 correct answers and 2 incorrect answers… You get the point.

Now, you will see how easy it is to define foreign keys and one to many relationships between your Room entities. In fact, you can do it by adding a single line of code.

Open the answer.kt file under the data ▸ model package.

The Answer entity already has a question_id field that you can use as a foreign key that points to the question_id field of your Question entity. The problem is that you still have not told Room that this is actually a foreign key. For this, you need to use the foreignKeys property of the @Entity annotation to define the relationships.

Add a foreignKeys property to the @Entity annotation of your Answer class like this:

@Entity(tableName = "answer",
    foreignKeys = [//1
        ForeignKey(entity = Question::class,//2
            parentColumns = ["question_id"],//3
            childColumns = ["question_id"],//4
            onDelete = CASCADE)//5
    ])

Taking each commented section in turn:

1. foreignKeys allows you to define a relationship between this and another entity. This property accepts an array of ForeignKey objects that you can use to define foreign key constraints.
2. The first parameter in the constructor of a ForeignKey object accepts the entity to which this entity is related. In this case, you are passing the Question class since you want to create a foreign key constraint to the Question entity.
3. parentColumns accepts the column names in the parent entity as an array. Since you want to match each answer to a single question, you are going to pass the primary key of your Question entity: question_id.
4. childColumns accepts the column names in the current entity to use as foreign keys.
5. onDelete tells Room what to do in case the parent entity is deleted from the database. For example, what would happen to your answers if the respective question is deleted? When you enter CASCADE for the value of onDelete, be sure to use the import for androidx.room.ForeignKey.CASCADE.

There are several options for onDelete:

1. CASCADE: If a record is deleted from the parent entity each row in the child entity that was associated with the parent entity is also deleted. For this app, you are using this option since you want to delete the answers if the question is deleted.

2. NO_ACTION: If the parent record is deleted or modified, no action is taken.

3. RESTRICT: This constraint means that, if a record in the parent entity has one or more records mapped to it in the child entity, the app is prohibited from deleting or updating the parent record.

4. SET_DEFAULT: If the parent record is deleted, the foreign key in the child record gets a default value.

5. SET_NULL: If the parent record is deleted or updated, the foreign key in the child record gets a NULL value.

One important thing to remember is that, if you define a foreign key constraint, SQLite requires that you create a unique index in the parent entity for the mapped columns. It is also recommended in the documentation that you create an index on the child table to avoid full table scans when the parent table is updated. If you don’t, Room will throw a compile time warning.

Therefore, modify your annotation like this:

@Entity(tableName = "answer",
    foreignKeys = [
        ForeignKey(entity = Question::class,
            parentColumns = ["question_id"],
            childColumns = ["question_id"],
            onDelete = CASCADE)
    ],
    indices = [Index("question_id")])//only this line changes

You will need to add the import for androidx.room.Index, or you can just import andoidx.room.*.

The indices property allows you to define an index for one or more columns in your entity by passing an array with the column names. This takes care of the index for the child entity, now you need to define it for the parent entity.

Open Question.kt and modify the @Entity annotation like below:

@Entity(tableName = "question", indices = [Index("question_id")])

Again, you will need to add the import for androidx.room.Index.

Just like the Answer entity, this code is telling Room that you want to create an index for the question_id primary key field.

Build and run your app to verify everything is working properly.

Now, say you want to retrieve a list of all the questions with their respective answers. To do this, you would need to write two different queries: One to retrieve the list of all the questions and another to retrieve the answers based on the question_id. Your Daos would look like this:

@Query("SELECT * FROM question ORDER BY question_id")
fun getAllQuestions(): LiveData<List<Question>>

@Query("SELECT * FROM answer WHERE question_id = :questionId")
fun getAnswersForQuestion(questionId: Int): List<Answer>

While the above approach is not bad, Room offers a better way to work with one-to-many relations: The @Relation annotation.

@Relation is a very handy annotation that automatically retrieves records from related entities. You can apply it to a List or Set of objects and Room will take care of the rest for you. To see the @Relation in action, create a new class under the data ▸ model package and name it QuestionAndAllAnswers.

Replace everything inside with the following:

class QuestionAndAllAnswers {
    @Embedded//1
    var question: Question? = null

    @Relation(parentColumn = "question_id",//2
              entityColumn = "question_id")
    var answers: List<Answer> = ArrayList()//3
}

Step by step:

1. Since you want to be able to access all the fields in the Question entity, we are using the @Embedded annotation to retrieve all the properties from that entity. If you need a reminder of how annotations like @Embedded work, take a look at the Tables and Entities section of the previous chapter.
2. The @Relation annotation accepts at least two parameters:

• parentColumn indicates the primary key of the parent entity.

• entityColumn indicates the field (usually the foreign key) on this entity that maps to the primary key of the parent entity.

3. The answers list will contain the Answer objects related to the question property.

Note: It is important to remember that the @Relation annotation can only be used in Pojo classes. Entities in Room can’t have relations.

And that’s it! build and run the app again to verify everything is still working fine.

While the app has not visually changed, you now have a good foundation about how Room works and your Database is almost ready.

In the next chapter, you will learn how to interact with your Entities by creating your first Database Access Objects.

Key points

• An entity relation diagram, ER diagram or ERD is a kind of flowchart that illustrates the relation between the components of a system.
• Entities represent a component, object or a concept of a system. They are usually translated as tables in your database.
• Entities in Crow’s Foot notation also include a list of attributes or properties that define them.
• An attribute that can uniquely identify a record of your entity is known as a key attribute and they usually become primary keys in your database.
• A relationship tells you how two entities interact with each other and it is usually represented as a verb.
• The cardinality of an ERD tells you the kind of relationship that two entities have.
• One to one relationship: When one entity can only be related to one and only one instance of the other entity.
• One to many relationship: When one entity can be related to many instances of another entity.
• Many to many relationship: When many instances of an entity can also be related to many instance of another entity.