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.

do you want to set all the columns of that record to null ? or all the fields of the student entity — kommradHomer, Apr 02 '12 at 14:23
I want the `student`'s `teacher` column to become `null` after the `student`'s `teacher` is deleted from the system. — satoshi, Apr 02 '12 at 15:05
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, Apr 05 '12 at 14:32
Here's a feature request on the Hibernate Jira for supporting `@OnDelete(onDeleteAction=SET_NULL)`: https://hibernate.atlassian.net/browse/HHH-4410 — Dario Seidl, Mar 09 '22 at 14:39

score 48 · Accepted Answer · edited May 23 '17 at 12:17

48

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

edited May 23 '17 at 12:17

Community

1
1

answered Apr 05 '12 at 14:49

kevingallagher

908
1
8
9

Thank you, both the solutions work perfectly. I personally prefer the second one :) – satoshi Apr 06 '12 at 17:41
8

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.* – SpaceTrucker Sep 06 '13 at 09:53
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. – Michał Stochmal Feb 28 '19 at 13:05

score 7 · Answer 2 · edited Sep 23 '15 at 05:02

7

What about defining

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

?

edited Sep 23 '15 at 05:02

Tiny

27,221
105
339
599

answered Jun 08 '15 at 12:54

Michał Ziobro

10,759
11
88
143

4

This is only used to specify foreign key constraints in the associated database, when schema generation is in effect. – Tiny Sep 23 '15 at 05:02
1

This is not database neutral, other than that it is the best approach as it is clear, logical and fast. – Eugene Dec 08 '21 at 11:49
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(...))` – Dario Seidl Mar 09 '22 at 15:00

score 4 · Answer 3 · edited Dec 12 '18 at 23:37

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.

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

3 Answers3

Linked

Related