Room Persistence Deletes Child On Update

Question

I am using Room Library to persist data in my Android App. I have 2 main table, which are Task and Group. The relation is One-to-Many, where a task can belong only to 1 group, but a group can belong to many tasks.

The persistence works fine, but the problem is that when I try to update information from a Group, every Task related to that group is deleted.

Here follows my Entity and DAO configuration:

Task Entity

@Entity(tableName = "task",
        foreignKeys = {
                @ForeignKey(
                        entity = Group.class,
                        parentColumns = "id",
                        childColumns = "groupId",
                        onDelete = CASCADE,
                        onUpdate = RESTRICT
                )},
        indices = {@Index(value = "id"), @Index(value = "groupId")}
)
public class Task {

    @PrimaryKey(autoGenerate = true)
    private int id;
    private String name;
    private int groupId;

    public Task(int id, String name, int groupId) {
        this.id = id;
        this.name = name;
        this.groupId = groupId;
    }

}

Group Entity

 @Entity(tableName = "group")
public class Group {

    @PrimaryKey(autoGenerate = true)
    private int id;
    private String name;

    public Group(int id, String name) {
        this.id = id;
        this.name = name;
    }

}

Task DAO

@Dao
public interface TaskDao {

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    void addTask(Task task);

    @Query("select * from task")
    public List<Task> listAllTasks();

    @Query("select * from task where id = :taskId")
    public Task getTask(int taskId);

    @Update(onConflict = OnConflictStrategy.REPLACE)
    void updateTask(Task task);

    @Query("delete from task")
    void removeAllTasks();

    @Delete
    void delete(Task task);
}

Group DAO

@Dao
public interface GroupDao {

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    void addGroup(Group group);

    @Query("select * from `group`")
    public List<Group> listAllGroups();

    @Query("select * from `group` where id = :groupId")
    public Group getGroup(long groupId);

    @Update(onConflict = OnConflictStrategy.REPLACE)
    void updateGroup(Group group);

    @Query("delete from `group`")
    void removeAllGroups();

    @Delete
    void delete(Group group);

}

Changing the OnUpdate to RESTRICT, CASCADE or NO_ACTION at the Entity declaration in the Task class doesn't help.

I will appreciate any help. Thank you.

Answer 1

As in SQLite Documentation: 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.

And as in google Documentations: By default, all RoomDatabases use in memory storage for TEMP tables and enables recursive triggers.

Conclusion:**Since you are using REPLACE ConflictStrategy, and the foreign key **CASCADE the delete to parent entity, each child row will delete when you try to insert parent row already exists.

Solutions: Use @Query for updating the group like this

@Query("UPDATE groups SET columnToUpdate1 = :value1, columnToUpdate2 = :value2 WHERE id=:id")
void updateGroup(int id, value1,value2);