[FIXED] Why Android Room Update is not working with composite Primary Key?

Issue

I'm trying to update a table that has a composite primary key, but it doesn't work.

This is the definition of the entity:

@Entity(tableName = LH_OFFICE_BIBLICAL,
        primaryKeys = {"groupFK","readingFK","responsoryFK"},

        foreignKeys =
        {
           @ForeignKey(
                   entity = LHOfficeBiblicalJoinEntity.class,
                   parentColumns = "groupID",
                   childColumns = "groupFK",
                   onDelete = ForeignKey.CASCADE,
                   onUpdate = ForeignKey.CASCADE),
            @ForeignKey(
                    entity = BibleReadingEntity.class,
                    parentColumns = "readingID",
                    childColumns = "readingFK",
                    onDelete = ForeignKey.CASCADE,
                    onUpdate = ForeignKey.CASCADE),
            @ForeignKey(
                    entity = LHResponsoryEntity.class,
                    parentColumns = "responsoryID",
                    childColumns = "responsoryFK",
                    onDelete = ForeignKey.CASCADE,
                    onUpdate = ForeignKey.CASCADE)
        }
)

public class LHOfficeBiblicalEntity {
    @NonNull
    @ColumnInfo(name = "groupFK")
    public Integer groupFK=0;

    @NonNull
    @ColumnInfo(name = "readingFK")
    public Integer readingFK =0;

    @NonNull
    @ColumnInfo(name = "responsoryFK")
    public Integer responsoryFK =0;

    @NonNull
    @ColumnInfo(name = "theme")
    public String theme ="";

    @NonNull
    @ColumnInfo(name = "order", defaultValue= "1")
    public Integer order =0;
}

In the DAO, this is the method to update:

@Update(entity = LHOfficeBiblicalEntity.class,
        onConflict = OnConflictStrategy.REPLACE)
Integer lhOfficeBiblicalUpdateAll(List<LHOfficeBiblicalTable> u);

And the class LHOfficeBiblicalTable is:

public class LHOfficeBiblicalTable {
    public Integer groupFK;
    public Integer readingFK;
    public Integer responsoryFK;
    public String theme;
    public Integer order;
}

If I try an update with this JSON it doesn't work:

[
  {
    "groupFK":708280201,
    "readingFK":2578,
    "theme":"Pongamos s\u00f3lo en Dios nuestra confianza",
    "order":1,
    "responsoryFK":690,"crud":"u"
  }
]

I suspect that it is because of the composite primary key, because in the case of other entities that do not have a composite primary key, the updates work without problems.

How could I make it work? In this example, the value that changes is readingFK, if I try to put that value manually in the table it accepts it, therefore it is not a referential integrity problem in the table.

Solution

When using the convenience @Update method Room uses the primary key to determine what row to update (i.e. Room builds the updates's WHERE clause from the primary key as this allows the unique row to be located). So changing a value within the primary key will result in nothing (or another row) being updated.

You would need to use an @Query with an appropriate UPDATE statement.

e.g.

@Query("UPDATE " + LH_OFFICE_BIBLICAL + " SET readingFK=:new_readingFK WHERE groupFK=:groupFK AND readingFK=:original_readingFK AND responsoryFK=:responsoryFK")
void lhOfficeBiblicalUpdate(int groupFK, int original_readingFK, int new_readingFK, int responsoryFK);

• Note this will only update the single row, if you need to do many then you would have to use this within a loop.

Answered By - MikeT
Answer Checked By - Robin (JavaFixing Admin)