How can I cascade delete a collection which is part of a JPA entity?

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 deleted when the report is deleted?

JPA doesn't support cascading deletes on elementcollections (see also [here](http://stackoverflow.com/q/3903202/741249)) — THelper, Oct 21 '13 at 10:15

score 31 · Accepted Answer · edited Apr 11 '17 at 10:54

31

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.

edited Apr 11 '17 at 10:54

naXa stands with Ukraine

35,493
19
190
259

answered Oct 08 '11 at 10:16

Mikko Maunu

41,366
10
132
135

So what would be the right way to delete the categories? I have noticed that if I set the foreign key to cascade on the database itself, the MonthyReport.delete actually succeeds. – emt14 Oct 08 '11 at 10:26
Added to answer, just fetch them with your date=? -criteria and remove/delete one by one. Then delete will be cascaded to the categories. – Mikko Maunu Oct 08 '11 at 10:34
What to do if operation does not cascaded? – Dims Feb 01 '21 at 21:43

score 10 · Answer 2 · answered Jul 11 '20 at 11:00

The answer provided by J.T. is correct, but was incomplete for me and for sebge2 as pointed out in his/her comment. The combination of @ElementCollection and @OnDelete further requires @JoinColumn().

Follow-up example:

@Entity
public class Report extends Model {
    @Id
    @Column(name = "report_id", columnDefinition = "BINARY(16)")
    public UUID id; // Added for the sake of this entity having a primary key
    public Date date;
    public double availability;

    @ElementCollection
    @CollectionTable(name = "report_category", joinColumns = @JoinColumn(name = "report_id")) // choose the name of the DB table storing the Map<>
    @MapKeyColumn(name = "fault_category_key") // choose the name of the DB column used to store the Map<> key
    @Column(name = "fault_category_value")     // choose the name of the DB column used to store the Map<> value
    @JoinColumn(name = "report_id")            // name of the @Id column of this entity
    @OnDelete(action = OnDeleteAction.CASCADE)
    @Cascade(value={CascadeType.ALL})
    public Map<FaultCategory, Integer> categories;      
}

This setup will create a table called report and another table report_category with three columns: report_id, fault_category_key, fault_category_value. The foreign key constraint between report_category.report_id and report.report_id will be ON DELETE CASCADE. I tested this setup with Map<String, String>.

so how u delete the element collection? I use @Query annotation and JPQL to delete the parent entity. However, the child element collection would not be delete. — Rocas Yeh, Dec 15 '20 at 10:48
@RocasYeh Technically both scenarios should work as you expect: Scenario 1) You remove the parent entity using the `delete()` function of `CrudRepository` which means JPA deletes the parent entry and all children for you. Or scenario 2) You use native SQL with @Query and perform a `DELETE` for the parent entity row and the RDBMS automatically delete all child entities due to the `ON DELETE CASCADE` setting. Feel free to ask a separate question and to tag me if you're not able to isolate the issue. — simple414, Dec 17 '20 at 06:58
This is a terrific answer +1, and from my own testing I needed at least both `@JoinColumn` and `@OnDelete` to get it to work. — Tim Biegeleisen, Mar 17 '21 at 10:58

J.T. · Answer 3 · 2018-10-06T14:31:41.733

8

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

edited Oct 06 '18 at 14:31

answered Oct 05 '18 at 14:38

J.T.

129
2
7

2

org.hibernate.MappingException: only inverse one-to-many associations may use on-delete="cascade" – sebge2 Nov 12 '18 at 12:52
3

org.hibernate.AnnotationException: Unidirectional one-to-many associations annotated with `@OnDelete` must define `@JoinColumn`. Additional `@JoinColumn` helps, see above answer – Grigory Kislin Dec 24 '20 at 19:35

score 0 · Answer 4 · answered Dec 15 '20 at 11:40

I met the same problem, and here is my code sample.

@ElementCollection
@CollectionTable(name = "table_tag", joinColumns=@JoinColumn(name = "data_id"))
@MapKeyColumn(name = "name")
@Column(name = "content")
private Map<String, String> tags

After a lot of tries, finally, I just add foreign key constraint for the table_tag.data_id to the parent table's primary key. Notice that you should set ON DELETE CASCADE to the constraint. You can delete the parent entity by any ways, and the child element collection would be deleted too.

score 0 · Answer 5 · answered Aug 20 '21 at 09:13

As an alternative to the hibernate-specific annotation @org.hibernate.annotations.OnDelete, you can also provide the constraint via @javax.persistence.ForeignKey to customize automatic schema generation:

@CollectionTable(name = "foo_bar", foreignKey = @ForeignKey(
        name = "fk_foo_bar", 
        foreignKeyDefinition = "foreign key (foo_id) references Foo (id) on delete cascade"))
private List<String> bar;

How can I cascade delete a collection which is part of a JPA entity?

5 Answers5

Linked