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

38
votes

I've been able to have JPA/Hibernate to replicate the ON DELETE CASCADE functionality successfully (seems like the default behaviour) but I'm now trying to replicate the ON DELETE SET NULL functionality and I'm facing problems.

These are my two classes:

@Entity
@Table(name = "teacher")
public class Teacher
{
    @Id
    @GeneratedValue
    @Column(name = "id", nullable = false, length = 4)
    private int id;

    @OneToMany(mappedBy = "teacher")
    private List<Student> studentList;

    // ...
}

@Entity
@Table(name = "student")
public class Student
{
    @Id
    @GeneratedValue
    @Column(name = "id", nullable = false, length = 4)
    private int id;

    @ManyToOne(optional = true)
    @JoinColumn(name = "teacher_id", nullable = true)
    private Teacher teacher;

    // ...
}

When I try to delete a teacher, the following error appears:

org.springframework.dao.DataIntegrityViolationException: Could not execute JDBC batch update; SQL [delete from teacher where teacher_id=?]; constraint [null]
...
Caused by: org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
...
Caused by: java.sql.BatchUpdateException: Batch entry 0 delete from teacher where teacher_id='1' was aborted. Call getNextException to see the cause.

Am I doing something wrong? Is it something achievable?

Thank you.

3
javahibernatejpa
do you want to set all the columns of that record to null ? or all the fields of the student entity - kommradHomer
I want the student's teacher column to become null after the student's teacher is deleted from the system. - satoshi
Not that you want to change your schema for JPA, but I'd be curious the effect of changing it to a ManyToMany with cascade deletion on the join table. It should delete the association and leave the student. - kevingallagher

3 Answers

43
votes

It doesn't appear to be possible at the moment with jpa/hibernate.

On delete set null in hibernate in @OneToMany

JBs solution seems clean though:

for (Department child : parent.getChildren()) {
    child.setParentDepartment(null);
}
session.delete(parent);

You should also be able to put it in a PreRemove:

@PreRemove
private void preRemove() {
    for (Student s : studentList) {
        s.setTeacher(null);
    }
}
5
votes

What about defining

@ForeignKey(name = "fk_student_teacher",
            foreignKeyDefinition = " /*FOREIGN KEY in sql that sets ON DELETE SET NULL*/")

?

3
votes

I think that the best solution is a user SQL statement for setting on delete action as follow:

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...

  CONSTRAINT fk_column
     FOREIGN KEY (column1, column2, ... column_n)
     REFERENCES parent_table (column1, column2, ... column_n)
     ON DELETE SET NULL
);

when user deletes a row by other cascading delete where you use a table reference to this deleted row, you could not use hibernate solution and return SQL exception.