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

Question

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.

Resolvent

do you want to set all the columns of that record to null ? or all the fields of the student entity – Clercq

I want the student's teacher column to become null after the student's teacher is deleted from the system. – Resolvent

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. – Doriadorian

Here's a feature request on the Hibernate Jira for supporting @OnDelete(onDeleteAction=SET_NULL): hibernate.atlassian.net/browse/HHH-4410 – Tartan

Answer 1

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);
    }
}

Doriadorian

Thank you, both the solutions work perfectly. I personally prefer the second one :) – Resolvent

The @PreRemove solution is not portable. See JPA spec section 3.5. In general, the lifecycle method of a portable application should not [...] modify relationships within the same persistence context. – Filipe

This works, but when I delete 'Student' from 'Teacher' and I want to add the same 'Student' to different 'Teacher' - it simply doesn't work - 'Student' has null 'Teacher' in DB. – Confession

Answer 2

What about defining

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

?

Farlay

This is only used to specify foreign key constraints in the associated database, when schema generation is in effect. – Alack

This is not database neutral, other than that it is the best approach as it is clear, logical and fast. – Meyers

Note that the @ForeignKey annotation cannot be used directly on the field or property, it must used within a another annotation, e.g. @JoinColumn(foreignKey = @ForeignKey(...)) – Tartan

Answer 3

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.

Succinylsulfathiazole