How can I cascade delete a collection which is par

Question

@Entity
public class Report extends Model {

    public Date date;
    public double availability;

    @ElementCollection
    @Cascade(value={CascadeType.ALL})
    public Map<FaultCategory, Integer> categories;      
}

In one of my jobs I have the following code:

int n = MonthlyReport.delete("date = ?", date);

This always fails to delete the entity with the following error:

The DELETE statement conflicted with the REFERENCE constraint "FK966F0D9A66DB1E54". The conflict occurred in database "TFADB", table "dbo.MonthlyReport_categories", column 'MonthlyReport_id'.

How can I specify the mapping so the elements from the categories collection get delete when the report is deleted?

Answer 1

Cascading delete (and cascading operations in general) is effective only when operation is done via EntityManager. Not when delete is done as bulk delete via JP QL /HQL query. You cannot specify mapping that would chain removal to the elements in ElementCollection when removal is done via query.

ElementCollection annotation does not have cascade attribute, because operations are always cascaded. When you remove your entity via EntityManager.remove(), operation is cascaded to the ElementCollection.

You have to fetch all MonthlyReport entities you want to delete and call EntityManager.remove for each of them. Looks like instead of this in Play framework you can also call delete-method in entity.

Answer 2

We found the magic ticket! Add OnDelete(action= OnDeleteAction.CASCADE) to the ElementCollection. This allows us to remove the item from SQL (outside of the entityManager).