JPA one-to-many delete and set foreign key to null

Question

I use Spring Data JPA and MySQL. I have one to many relation between Event and Category. Obviously:

• one Event can have only one Category
• one Category can be assigned many Events.

The problem appears when I try to remove Category, if any Event hold a foreign key of this Category then I get an error. I would like to set the foreign key in the Event table to null when a Category is deleted.

At the moment I update all Events by setting the foreign key explicitly in the code by updating it to null before deletion of Category. Is there any way of doing it in use of annotations?

This is the Category entity:

@Entity
@Table(name = "category")
public class Category implements Serializable {

    @OneToMany(mappedBy="category", 
               targetEntity=Event.class, 
               fetch=FetchType.LAZY, 
               cascade= {CascadeType.DETACH, CascadeType.MERGE,  
                         CascadeType.PERSIST, CascadeType.REFRESH})
    public Set<Event> getEvents_category() {
       return events_category;
    }

}

This is the Event entity:

@Entity
@Table(name = "event")
public class Event implements Serializable {

    @ManyToOne(cascade={CascadeType.DETACH, CascadeType.MERGE, 
                        CascadeType.PERSIST, CascadeType.REFRESH})
    @JoinColumn(name="events_dancestyle")
    public DanceStyle getDanceStyle() {
       return danceStyle;
    }

}

I've seen that the topic was discussed many times but I haven't seen any solution to that.

Itinerant

Answer 1

Unfortunately it is not currently possible to do this using JPA/Hibernate annotations. See these related questions:

Have JPA/Hibernate to replicate the "ON DELETE SET NULL" functionality

On delete set null in hibernate in @OneToMany

Spikelet

Hi David, Thanks for your answer. Your answer just confirmed that I was correct. I used exactly the same approach as in the links you gave. – Itinerant

Answer 2

1- the best way is to write cascade = CascadeType.DETACH

2- but you should write above Class Event

@SQLDelete(sql = "UPDATE event SET deleted=true WHERE id=?")

3- inside the Event Class

@Column(columnDefinition = "boolean default false")
    private boolean deleted;

4- Then when you delete category if CascadeType.ALl the "deleted" attribute of Event entity will affected by 1 (true). if CascadeType.DETACH the "deleted" attribute of Event entity will not affected and still 0 (false).

Eelgrass