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.

Have u tried without ondelete=cascade When the REPLACE conflict resolution strategy deletes rows in order to satisfy a constraint, delete triggers fire if and only ifrecursive triggers are enabled. — lib4backer, Dec 27 '17 at 18:54
Yes, it works. But onDelete must be CASCADE, otherwise I will have foreign key constrains failures. Your comment lead me to find the issue. I was using addGroup() either to create a new or update an existing row. That's why the REPLACE was being called and triggering onDelete behavior every time. Thank you! — Herbert Souza Silva, Dec 27 '17 at 20:32
https://sqlite.org/lang_conflict.html here is the link where I found the conflict strategy resolution, nice one — lib4backer, Dec 28 '17 at 02:31
So what was exactly solution of this problem? You had to change this method: @Insert(onConflict = OnConflictStrategy.REPLACE) void addGroup(Group group); ? — Tom Wayne, Sep 19 '18 at 16:23
@JosefHruška No. All methods above are correct. The only thing wrong was that I was using the addGroup when trying to update information. It's simple. Use add for new resources and update for existing ones. — Herbert Souza Silva, Oct 29 '18 at 02:34

score 9 · Answer 1 · answered Aug 18 '19 at 05:11

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);

Room Persistence Deletes Child On Update

Task Entity

Group Entity

Task DAO

Group DAO

1 Answers1