Use on delete set null for Foreign key

Question

In this case answer_id is a foriegn key in ratings table and answer_id is primary key in answers table. I need to delete answers but "Cannot delete or update a parent row: a foreign key constraint fails. error occurred. How to set ON DELETE SET NULL option for this Foreign key.

@Entity
@Table(name = "ratings")
public class Ratings {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long rating_id;

    @Column(nullable = false, unique = false, length = 45)
    private Short ratingValue;

    @ManyToOne(cascade = fetch = FetchType.LAZY)
    @JoinColumn(name = "userId")
    private User user;

    @ManyToOne(cascade = fetch = FetchType.LAZY)
    @JoinColumn(name = "answer_id")
    private Answer answer;

    @ManyToOne(cascade = fetch = FetchType.LAZY)
    @JoinColumn(name = "question_id")
    private Question question;
//getters and setters

@Entity
@Table(name = "answers")
public class Answer {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long answer_id;

    @Column(nullable = false, unique = false, length = 100)
    private String fullAnswer;

    /*Many to one mapping question*/
    @ManyToOne(cascade = FetchType.LAZY)
    @JoinColumn(name = "question_id")
    private Question question;

    /* Many to One mapping with users*/
    @ManyToOne(cascade = FetchType.LAZY)
    @JoinColumn(name = "userId")
    private User user;
//getters and setters

I assume that the Answer id must be associated with other rows in the Rating table. — Arpit Asati, Jun 25 '22 at 16:47

score 1 · Answer 1 · edited Jul 15 '23 at 01:43

There is no support in Hibernate for a SET NULL action.

The feature request was created in 2006, and it's still in an "open" state.

@OnDelete -> action = OnDeleteAction.SET_NULL (like in MySql 5)

A workaround is to create a @PreRemove method on the parent entity, to set the foreign keys in the child entity to NULL. I suppose if you add other child entities, you'd have to modify your @PreRemove method. See example in this answer:

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

Use on delete set null for Foreign key

1 Answers1