Foreign key with multiple rows in Android Room: @Relation without @ForeignKey definition

Question

In my database I have three tables that need to be related to each other.

I explain the context:

The entry point is the today table, there I would have a row and starting from the oSalmodiaFK column I need to obtain multiple rows from the lh_salmo table going through the lh_salmodia table.

today
--------------------------
todayDate    oSalmodiaFK
--------------------------
20220410          1
20220411          2

---

lh_salmodia
---------------------------------------------------
salmodiaId      liturgiaId     salmoFK     order
---------------------------------------------------
1                   1             1          1
2                   1             2          2
3                   1             3          3
4                   2             4          1
5                   2             5          2
6                   2             6          3

---

lh_salmo
--------------------------
salmoId         text
--------------------------
1               Text 1
2               Text 2
3               Text 3
4               Text 4
5               Text 5
6               Text 6

From oSalmodiaFK I need an ordered list of psalms, that is the meaning of lh_salmodia, for example for oSalmodiaFK = 1 i need the contento of psalms 1, 2, 3.

To get that list I have designed this class:

public class SalmodiaWithSalmos {
    @Embedded
    public SalmodiaEntity salmodia;
    @Relation(
            parentColumn = "salmoFK",
            entityColumn = "salmoId",
            entity = SalmoEntity.class
    )
    public SalmoEntity salmo;
}

And in the class where I get the data from today I have this:

@Relation(
        entity = SalmodiaEntity.class,
        parentColumn = "oSalmodiaFK",
        entityColumn = "liturgiaId"
)

public List<SalmodiaWithSalmos> salmos;

The code works, in the query results I can see my list of psalms. But, as you can see in the code block above, I am relating the columns oSalmodiaFK and liturgiaId. But, there is no relationship between those tables. If I try to define that relationship on the Today entity:

            @ForeignKey(
                    entity = SalmodiaEntity.class,
                    parentColumns = "liturgiaId",
                    childColumns = "oSalmodiaFK",
                    onDelete = ForeignKey.SET_DEFAULT,
                    onUpdate = ForeignKey.CASCADE),

I have this error:

com.domain.data.entity.Today has a foreign key (oSalmodiaFK) that references com.domain.data.entity.SalmodiaEntity (liturgiaId) but com.domain.data.entity.SalmodiaEntity does not have a unique index on those columns nor the columns are its primary key. SQLite requires having a unique constraint on referenced parent columns so you must add a unique index to com.domain.data.entity.SalmodiaEntity that has (liturgiaId) column(s).

However, by not defining the relationship in the class the code works as expected.

My question is whether the code could cause problems if I don't define the relationship between today and lh_salmodia. According to the message, liturgiaId should be unique in lh_salmodia, but I need more than one row and I haven't found a better way to define a data model that does what I want.

Answer 1

if I don't define the relationship between today and lh_salmodia

yes, that is because you appear to misunderstand what @ForeignKey does and how to implement it.

@Foreignkey does not define a relationship instead it supports a relationship by setting a rule that enforces referential integrity (that a reference does in fact exist).

• the SQLite (Room is a wrapper around SQLite) term for a rule is a constraint. The following may assist in better understanding https://sqlite.org/foreignkeys.html

The rule is that the value in the child (the entity where the foreign key is defined) MUST be a unique value in the parent.

• if you try to insert a child and break the rule then a failure will occur.
• if you try to delete a parent so that children no longer have a parent then the onDelete action determines what happens next (CASCADE will delete the children).
• if you try to update a parent and that update results in the relationship not existing then the onUpdate action determines what happens next (CASCADE will change the children to the new value and thus reference the updated parent)

A relationship is not really defined but rather used when accessing the database.

In SQLite terms you typically use a JOIN; in Room @Relation mimics a JOIN in that it runs a subsequent query for each parent that gets every child of the parent.

You can use JOIN's with @Embedded annotation BUT you then get a cartesian result which doesn't easily fit the object orientated approach (hence, I believe, why Room uses the get all children approach).

So your issue is that you have the ForeignKey defined incorrectly. You are saying that the parent is the liturgiaId column, and thus MUST be unique.

As an analogy/example

Say today has:-

20220410          1
20220411          2
20220412          1

and you have lh_salmodia with :-

1                   1             1          1
2                   1             2          2
3                   1             3          3

then are the three rows referencing 20220410 or 20220412 ???? (you cannot say), hence why the uniqueness is required.

Of course if @ForeignKey is not used then the rule is not implemented and Room doesn't care (at least at the compilation stage) about the uniqueness. However, you may then get unexpected results or the expected results.

Typically lh_salmodia, being an associative/reference/mapping (and other names) table you would have @ForeignKey on the columns that reference (map/associate .... ) the other tables and where the column in the lh_salmodia table is the child and the referenced column is the parent.

---

---

Working Example

Assuming that you want a resulkt that is something along the lines of:-

2022-04-11 22:00:37.456 D/DBINFO: Today is 20220410 oSalmodiaFK is 1. It has 3 they are:-
2022-04-11 22:00:37.456 D/DBINFO:    Order is 1 Salmo is TEXT1 SalmoID is 1
2022-04-11 22:00:37.457 D/DBINFO:    Order is 2 Salmo is TEXT2 SalmoID is 2
2022-04-11 22:00:37.457 D/DBINFO:    Order is 3 Salmo is TEXT3 SalmoID is 3
2022-04-11 22:00:37.457 D/DBINFO: Today is 20220411 oSalmodiaFK is 2. It has 3 they are:-
2022-04-11 22:00:37.457 D/DBINFO:    Order is 1 Salmo is TEXT4 SalmoID is 4
2022-04-11 22:00:37.457 D/DBINFO:    Order is 2 Salmo is TEXT5 SalmoID is 5
2022-04-11 22:00:37.457 D/DBINFO:    Order is 3 Salmo is TEXT6 SalmoID is 6

Then here's a working example.

Today

@Entity
class Today {
   String todayDate;
   @PrimaryKey
   Long oSalmodiaFK = null;

   Today(){}

   @Ignore
   Today(Long oSalmodiaFK, String todayDate) {
      this.oSalmodiaFK = oSalmodiaFK;
      this.todayDate = todayDate;
   }

   @Ignore
   Today(String todayDate) {
      this(null,todayDate);
   }
}

SalmoEntity

@Entity
class SalmoEntity {
    @PrimaryKey
    Long salmoId = null;
    String text;

    SalmoEntity(){}

    @Ignore
    SalmoEntity(String text) {
        this(null,text);
    }

    @Ignore
    SalmoEntity(Long salmoId, String text) {
        this.salmoId = salmoId;
        this.text = text;
    }
}

SalmodiaEntity (i.e. the mapping table)

/* Typical Many-Many aka associative/reference/mapping table */
/* BUT with an extra column order */
@Entity(
        /* no need for the salmodiaId column it serves little/no purpose */
        /* instead te primary key is a composite made up of the two mapped tables */
        /* as such a mapping cannot/should not be duplicated */
        primaryKeys = {"liturgiaId","salmoFK"},
        
        /* Enforce referential integrity, optional  */
        foreignKeys = {
                @ForeignKey(
                        entity = Today.class,
                        parentColumns = {"oSalmodiaFK"}, /* Referenced table column MUST BE unique (implied if Priamry Key) */
                        childColumns = {"liturgiaId"}, /* column in this table i.e. the FK */
                        onDelete = ForeignKey.CASCADE, /* typically the most common/useful option */
                        onUpdate = ForeignKey.CASCADE
                ),
                @ForeignKey(
                        entity = SalmoEntity.class,
                        parentColumns = {"salmoId"},
                        childColumns = {"salmoFK"},
                        onDelete = ForeignKey.CASCADE,
                        onUpdate = ForeignKey.CASCADE
                )
        }
)
class SalmodiaEntity {
   long liturgiaId;
   @ColumnInfo(index = true)
   long salmoFK;
   int order;

   SalmodiaEntity(){}

    @Ignore
    SalmodiaEntity(long liturgiaId, long salmoFK, int order) {
       this.liturgiaId = liturgiaId;
       this.salmoFK = salmoFK;
       this.order = order;
    }
}

SalmodiaWithSalmo (unchanged)

public class SalmodiaWithSalmos {
   @Embedded
   public SalmodiaEntity salmodia;
   @Relation(
           parentColumn = "salmoFK",
           entityColumn = "salmoId",
           entity = SalmoEntity.class
   )
   public SalmoEntity salmo;
}

TodayWithSalmodiaWithSalmos i.e. And in the class where I get the data from today I have this:

public class TodayWithSalmodiaWithSalmos {
   @Embedded
   Today today;
   @Relation(
           entity = SalmodiaEntity.class,
           parentColumn = "oSalmodiaFK",
           entityColumn = "liturgiaId"
   )
   List<SalmodiaWithSalmos> salmodiasWithSalmos;
}

An @Dao annotated abstract class AllDAO (could be interface and then no need for abstract)

@Dao
abstract class AllDAO {

    /* IGNORE will not result in failure upon a duplicate */
    /* however should check the returned value */
    /* -1 indicates skipped insertion, > 0 inserted (if 0 and -1 are not specified as values)*/
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract long insert(Today today);
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract long insert(SalmoEntity salmoEntity);
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract long insert(SalmodiaEntity salmodiaEntity);

    @Query("SELECT * FROM today")
    abstract List<TodayWithSalmodiaWithSalmos> getTodayWithSalmodiaWithSalmosList();

}

An @Database annotated class suitable for the example TheDatabase

@Database(entities = {Today.class,SalmoEntity.class,SalmodiaEntity.class}, version = 1, exportSchema = false)
abstract class TheDatabase extends RoomDatabase {
    abstract AllDAO getAllDAO();

    private static volatile TheDatabase instance = null;
    public static TheDatabase getInstance(Context context) {
        if (instance == null) {
            instance = Room.databaseBuilder(context,TheDatabase.class,"the_database.db")
                    .allowMainThreadQueries()
                    .build();
        }
        return instance;
    }
}

And last an activity that inserts some data

• 3 Today's (on more than your data to demonstrate salmos being resused)
• 6 Salmos (as per your data) and
• the original 6 Salmodias
• with another 6 applying all the Salmos to the new 3rd Today.

Finally extracting all of the Todays along with the order from the lh_salmodia table and the respective Salmos.

public class MainActivity extends AppCompatActivity {

    TheDatabase db;
    AllDAO dao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        db = TheDatabase.getInstance(this);
        dao = db.getAllDAO();

        dao.insert(new Today(1L,"20220410"));
        dao.insert(new Today(2L,"20220411"));
        dao.insert(new Today(3L,"20220412")); /* Extra */

        dao.insert(new SalmoEntity(1L,"TEXT1"));
        dao.insert(new SalmoEntity(2L,"TEXT2"));
        dao.insert(new SalmoEntity(3L,"TEXT3"));
        dao.insert(new SalmoEntity(4L,"TEXT4"));
        dao.insert(new SalmoEntity(5L,"TEXT5"));
        dao.insert(new SalmoEntity(6L,"TEXT6"));

        dao.insert(new SalmodiaEntity(1,1,1));
        dao.insert(new SalmodiaEntity(1,2,2));
        dao.insert(new SalmodiaEntity(1,3,3));
        dao.insert(new SalmodiaEntity(2,4,1));
        dao.insert(new SalmodiaEntity(2,5,2));
        dao.insert(new SalmodiaEntity(2,6,3));

        /* Extra */
        dao.insert(new SalmodiaEntity(3,6,2));
        dao.insert(new SalmodiaEntity(3,5,1));
        dao.insert(new SalmodiaEntity(3,4,4));
        dao.insert(new SalmodiaEntity(3,3,3));
        dao.insert(new SalmodiaEntity(3,2,5));
        dao.insert(new SalmodiaEntity(3,1,6));

        for(TodayWithSalmodiaWithSalmos twsws: dao.getTodayWithSalmodiaWithSalmosList()) {
            Log.d("DBINFO","Today is " + twsws.today.todayDate + " oSalmodiaFK is " + twsws.today.oSalmodiaFK + ". It has " + twsws.salmodiasWithSalmos.size() + " they are:-");
            for(SalmodiaWithSalmos sws: twsws.salmodiasWithSalmos) {
                Log.d("DBINFO","\t Order is " + sws.salmodia.order + " Salmo is " + sws.salmo.text +  " SalmoID is " + sws.salmo.salmoId);
            }
        }
    }
}

The result as above plus the extras (new 3rd Today):-

2022-04-11 22:00:37.457 D/DBINFO: Today is 20220412 oSalmodiaFK is 3. It has 6 they are:-
2022-04-11 22:00:37.457 D/DBINFO:    Order is 6 Salmo is TEXT1 SalmoID is 1
2022-04-11 22:00:37.457 D/DBINFO:    Order is 5 Salmo is TEXT2 SalmoID is 2
2022-04-11 22:00:37.457 D/DBINFO:    Order is 3 Salmo is TEXT3 SalmoID is 3
2022-04-11 22:00:37.457 D/DBINFO:    Order is 4 Salmo is TEXT4 SalmoID is 4
2022-04-11 22:00:37.457 D/DBINFO:    Order is 1 Salmo is TEXT5 SalmoID is 5
2022-04-11 22:00:37.457 D/DBINFO:    Order is 2 Salmo is TEXT6 SalmoID is 6