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

}

Can you also share your entities' JPA related configuration? — Ale Zalazar, Jul 11 '23 at 15:38

score 0 · Accepted Answer · answered Jul 11 '23 at 16:09

0

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:

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

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

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

"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*/")

answered Jul 11 '23 at 16:09

Michał Dębski

527
4
16

Hi, I just copied the entities in, hope it makes things clearer. – StingerWolf Jul 11 '23 at 16:10
try to replace places where you delete something with "on cascade delete" constraint option and places where you set something to null with "on cascade set null" constrain option. This should allow you to avoid any problems with transactions and relations you might have forgot about. – Michał Dębski Jul 11 '23 at 16:22
But what about many to many? I have read here, that cascade.all in general is bad idea for many to many because of cascade remove. Here: https://vladmihalcea.com/a-beginners-guide-to-jpa-and-hibernate-cascade-types/ – StingerWolf Jul 11 '23 at 16:59
I saw some solutions online to do it in hibernate but they were over complicated. I think the article you mention summarizes it pretty well at the end. I don’t remember if I ever used many to many without an intermediate entity because there is always something to save besides foreign keys. If I were you I would create it because: it gives you more control over hibernate and is more forward looking (you can add columns without costly migrations). I admit it is not as pure but I found it really hard to go all the way with hibernate - it is better to keep it simple. – Michał Dębski Jul 11 '23 at 21:00
And the passage from the blog I mentioned: “Practical test cases for real many-to-many associations are rare. Most of the time you need additional information stored in the link table. In this case, it is much better to use two one-to-many associations to an intermediate link class. In fact, most associations are one-to-many and many-to-one. For this reason, you should proceed cautiously when using any other association style.” – Michał Dębski Jul 11 '23 at 21:01
Thank you, yes, it helped to use an intermediate entity, no more "raw" many to many connections. – StingerWolf Jul 12 '23 at 10:43

I get a foreign key constraint error in JPA

1 Answers1