I get a foreign key constraint error in JPA

Question

I am currently working a small project. I am using java and Spring Data JPA. My issue is the following. When I try to delete a blog, which has connections to other entities like favourites, comment, tag etc. I get a foreign constraint error. I do understand what issue is, that when trying to delete or remove an entity and the table behind it is connected to another via a foreign key, it is not possible to delete, only when one removes the child entities etc. Because blog has a one to many connection with comment and a many to many connection with tag and is a subclass of the superclass Post which has a many to one relatiuonship with the User entity, deletion cannot be done by using cascade remove etc.Theorethically in my service code I did everything to remove the connections manually, but somehow it is not working, I get a foreign contraint error when I run the rest controller:

{ "message": "could not execute statement [(conn=8) Cannot delete or update a parent row: a foreign key constraint fails (blogdb.comment, CONSTRAINT FKhvh0e2ybgg16bpu229a5teje7 FOREIGN KEY (parent_comment_id) REFERENCES comment (id))] [delete from comment where id=?]", "httpError": 404 }

In one of my previous builds, where I did not have "comment", it gave me the same for the "favorites" table, so it is something I am doing systemathically wrong and it is not entity specific. Below my service code, thank you for your help:

    public void deleteById(Long id) {

        final Blog blogToDelete = blogRepository.findById(id).orElseThrow(EntityNotFoundException::new);

        // remove blog comments
        removeBlogComments(blogToDelete);

        // remove favorite blogs
        removeFavoriteBlogs(id);

        // remove the blog of the user
        blogToDelete.getUser().getPosts().removeIf(x -> x.getId().equals(id));
        userRepository.save(blogToDelete.getUser());

        blogRepository.delete(blogToDelete);
    }

    private void removeBlogComments(Blog blogToDelete) {

        final List<Comment> blogCommentsToDelete = blogToDelete.getComments();
        blogCommentsToDelete.forEach(x -> x.setParentComment(null));
        commentRepository.saveAll(blogCommentsToDelete);

        blogCommentsToDelete.forEach(x -> {
            x.getUser().getPosts().removeIf(y->y.getId().equals(x.getId()));
            commentRepository.delete(x);
            entityManager.flush();
        });
        blogToDelete.getComments().clear();
        blogRepository.save(blogToDelete);
        entityManager.flush();
    }

    private void removeFavoriteBlogs(Long blogId) {
        final List<User> allByFavoriteBlog = userRepository.findAllByFavoriteBlog(blogId);
        allByFavoriteBlog.forEach(x -> {
            boolean b = x.getFavoriteBlogs().removeIf(y -> y.getId().equals(x.getId()));
            if (b) {
                userRepository.save(x);
                entityManager.flush();
            }
        });
    }

The Post entity:

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Inheritance;
import jakarta.persistence.InheritanceType;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.Lob;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.OneToMany;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.experimental.SuperBuilder;

import java.util.ArrayList;
import java.util.List;

@Entity
@AllArgsConstructor
@NoArgsConstructor
@Inheritance(strategy = InheritanceType.JOINED)
@SuperBuilder
public class Post {

    @Getter
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @Getter
    @Setter
    @Column(name = "likes")
    @Builder.Default
    private int likes = 0;

    @Getter
    @Setter
    @Lob
    @Column(name = "content", nullable = false, columnDefinition = "LONGTEXT")
    private String content;

    @Getter
    @Setter
    @ManyToOne
    @JoinColumn(name = "user_id", referencedColumnName = "id")
    private User user;

}

The Blog entity:

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.JoinTable;
import jakarta.persistence.Lob;
import jakarta.persistence.ManyToMany;
import jakarta.persistence.OneToMany;
import jakarta.persistence.PrimaryKeyJoinColumn;
import jakarta.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.experimental.SuperBuilder;

import java.util.ArrayList;
import java.util.List;

@EqualsAndHashCode(callSuper = true)
@Entity
@PrimaryKeyJoinColumn(name = "id")
@Table(name = "blog")
@AllArgsConstructor
@NoArgsConstructor
@SuperBuilder
@Getter
@Setter
public class Blog extends Post {

    @Column(name = "title", nullable = false)
    private String title;

    @Column(nullable = false)
    @Builder.Default
    @ManyToMany
    @JoinTable(
            name = "blog_tags",
            joinColumns = @JoinColumn(name="blog_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name="tag_id", referencedColumnName = "id"))
    private List<Tag> tags = new ArrayList<>();

    @Lob
    @Column(columnDefinition = "LONGTEXT")
    private String thumbnail;

    @OneToMany(mappedBy = "blog")
    @Builder.Default
    private List<Comment> comments = new ArrayList<>();

}

The User entity:

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.JoinTable;
import jakarta.persistence.ManyToMany;
import jakarta.persistence.OneToMany;
import jakarta.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

import java.util.ArrayList;
import java.util.List;

@Entity
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Table(name = "benutzer")
public class User {

    @Getter
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @Getter
    @Setter
    @Column(name = "username", nullable = false, unique = true)
    private String username;

    @Getter
    @Setter
    @Column(name = "email", nullable = false, unique = true)
    private String email;

    @Getter
    @Setter
    @Column(name = "password", nullable = false)
    private String password;

    @Getter
    @Setter
    @OneToMany(mappedBy = "user")
    @Builder.Default
    private List<Post> posts = new ArrayList<>();

    @Getter
    @Setter
    @Builder.Default
    @ManyToMany
    @JoinTable(
            name = "favorites",
            joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "blog_id", referencedColumnName = "id")
    )
    private List<Blog> favoriteBlogs = new ArrayList<>();
}

The Tag entity:

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@Entity
@Table(name = "tag")
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class Tag {

    @Getter
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @Getter
    @Setter
    @Column(name = "title", nullable = false)
    private String title;
}

The Comment entity:

import jakarta.persistence.*;
import jakarta.validation.constraints.NotNull;
import lombok.AllArgsConstructor;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.experimental.SuperBuilder;

import java.util.ArrayList;
import java.util.List;

@EqualsAndHashCode(callSuper = true)
@Entity
@PrimaryKeyJoinColumn(name = "id")
@Table(name = "comment")
@AllArgsConstructor
@NoArgsConstructor
@SuperBuilder
@Getter
@Setter
public class Comment extends Post {

    @ManyToOne
    @JoinColumn(name = "blog_id", updatable = false, referencedColumnName = "id")
    //@JsonBackReference("blog_comment")
    private Blog blog;

    @ManyToOne()
    @JoinColumn(name = "parent_comment_id", referencedColumnName = "id")
    //@JsonBackReference("comment_comment")
    private Comment parentComment;

    @OneToMany(mappedBy = "parentComment")
    //@JsonManagedReference("comment_comment")
    private List<Comment> childComments = new ArrayList<>();

}

Answer 1

It should not be necessary to delete comments by hand. As you mentioned this should be done by cascade option in foreign key definition. For some relations "delete" option seems sufficient but looks at your code it looks like for parent comment you need "set null" cascade options.

However, how to set it up depends on how you manage your schema. If you configure your tables using SQL DDL then those options can usually be set on along with foreign key defintion. ex.

CREATE TABLE users (
    user_id integer PRIMARY KEY
);

CREATE TABLE comments (
    comment_id integer PRIMARY KEY,
    user_id integer REFERENCES users ON DELETE CASCADE, -- will delete comments when user is deleted
    parent_id integer REFERENCES comments ON DELETE SET NULL, -- will set to null when parent is deleted
);

If you use spring annotation then you need to set specific options. See:

1. one-to-many https://www.baeldung.com/spring-jpa-unidirectional-one-to-many-and-cascading-delete

    @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
    private Set<Comment> comments = new HashSet<>();

2. many-to-one JPA: unidirectional many-to-one and cascading delete

    @ManyToOne
    @OnDelete(action = OnDeleteAction.CASCADE)
    private Parent parent;

3. "set null" is more complicated, I would recommend setting foreign key constraint accordingly Have JPA/Hibernate to replicate the "ON DELETE SET NULL" functionality

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