Hibernate execution order with bidirectional references

Question

Setup

My environment is Spring Boot 2.1 with MS SQL Server and Hibernate. My setup contains a simple parent-child-association with a special feature. A parent has many children. A parent may have a favourite child. The parent holds a reference to the favourite child.

I have no influence on the database design. The database schema:

PARENT
------------------------------------------------------------------------------------
ID NOT NULL,
FAVOURITE_CHILD_ID NULL,
CONSTRAINT FK_PARENT_FAVOURITE_CHILD_ID FOREIGN KEY(FAVOURITE_CHILD_ID) ON CHILD(ID)

CHILD
------------------------------------------------------------------------------------
ID NOT NULL,
PARENT_ID NOT NULL,
CONSTRAINT FK_CHILD_PARENT_ID FOREIGN KEY(PARENT_ID) ON PARENT(ID)

The models (simplified code):

@Entity
public class Parent {

    @Id
    @GeneratedValue(...)
    @GenericGenerator(...)
    @Column(name = "ID", updatable = false, nullable = false, unique = true)
    private Long id;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "parent", orphanRemoval = true)
    private Set<Child> children;

    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "FAVOURITE_CHILD_ID", insertable = false, updatable = false)
    private Child favourite;
}

@Entity
public class Child {

    @Id
    @GeneratedValue(...)
    @GenericGenerator(...)
    @Column(name = "ID", updatable = false, nullable = false, unique = true)
    private Long id;

    @Column(name = "PARENT_ID")
    @NotNull
    private Long parentId;

    @ManyToOne(optional = false)
    @JoinColumn(name = "PARENT_ID", insertable = false, updatable = false, nullable = false)
    private Parent parent;
}

The parent is configured to cascade delete its children.

The repository:

public interface ParentRepository extends CrudRepository<Parent, Long> {
}

The pom.xml has a reference to:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-rest</artifactId>
</dependency>

With Spring Data REST enabled the application creates an HTTP endpoint serving requests for parents.

Problem

I get an error when I delete a parent that has a favourite child. Sample data contain parent #1 that has no favourite child and parent #2 that has a favourite child:

PARENT

ID FAVOURITE_CHILD_ID
-- ------------------
1  NULL
2  202

CHILD

ID  PARENT_ID
--- ---------
101 1
102 1
201 2
202 2

Request DELETE on http://localhost:8080/parents/1 runs well and deletes the first parent and its children.

Request DELETE on http://localhost:8080/parents/2 throws an exception. SQL Server's message is:

The DELETE statement conflicted with the REFERENCE constraint "FK_PARENT_FAVOURITE_CHILD_ID". The conflict occurred in database "test", table "dbo.PARENT", column 'FAVOURITE_CHILD_ID'.

Profiler shows that Hibernates runs the delete statement for the CHILD table first:

exec sp_executesql N'delete from child where id=@P0',N'@P0 bigint',2
go

I'd expect Hibernate to remove the reference to the favourite child first, i.e. to UPDATE PARENT SET FAVOURITE_CHILD_ID = NULL WHERE ID = 2.

But it doesn't. Is there a way to configure Hibernate that it can resolve this by its own?

Attempt at a solution

I'd prefer a solution by configuration. Since I couldn't find any I tried to take over more control by overriding Spring's repository REST endpoint with a custom controller:

@RepositoryRestController
public class RepositoryRestMethodOverrideController {

    @DeleteMapping(path = "/parents/{parentId}")
    @Transactional
    public ResponseEntity<?> deleteParent(@PathVariable Long parentId) {

        Parent parent = parentRepository.findById(parentId).orElseThrow(NotFoundException::new);

        if (parent.getFavouriteChildId() != null) {
            parent.setFavouriteChildId(null);
            parent.setFavouriteChild(null);
            parentRepository.save(parent);
        }

        parentRepository.delete(parent);

        return new ResponseEntity<>(HttpStatus.NO_CONTENT);
    }
}

Sadly, I see the same results (error messages). Is there a way to tell Hibernate to write the update (parentRepository.save(parent)) before it runs the delete command?

Answer 1

You want to set the child's id to null, but you have forbidden the column to change:

@JoinColumn(name = "FAVOURITE_CHILD_ID", insertable = false, updatable = false)
private Child favourite;

Simply remove updatable = false.