CODEWITHSUNDEEP
springpostgresqlhibernatejpaspring-data-jpa
lapurita
lapurita

Reputation: 1175

Cascade.Type = ALL does not delete all "child rows" before trying to delete its own row

I'm trying to delete a user but every row in the database that references the user does NOT get deleted before hibernate tries to remove the user.

I have the same structure for every other entity in the application and it works just fine, all the child rows get deleted first and then the row itself get deleted, but as you see below this is not the case when trying to delete a user. Hibernate goes to this statement :

Hibernate: delete from users where user_id=?

before all comment_votes are deleted. (Posts should also be deleted before as well but I guess the comment_votes error shows up first).

This sequence of sql statements are executed before the error according to the console:

Hibernate: delete from comment_vote where vote_id=?
Hibernate: delete from comment where comment_id=?
Hibernate: delete from comment_vote where vote_id=?
Hibernate: delete from comment where comment_id=?
Hibernate: delete from comment_vote where vote_id=?
Hibernate: delete from comment where comment_id=?
Hibernate: delete from users where user_id=?

This is the error I'm getting:

org.postgresql.util.PSQLException: ERROR: update or delete on table "users" violates foreign key constraint "fkjf73ixvt1jv3wdv4ah0hkpewf" on table "comment_vote"
  Detail: Key (user_id)=(2) is still referenced from table "comment_vote".

User.java :

@Entity
@Table(name = "users") // because User is a keyword in some DBs
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "user_id", columnDefinition = "serial")
    private Long id;

    @NotEmpty
    @Column(unique = true)
    private String username;

    @OneToMany(mappedBy = "user", cascade = CascadeType.ALL)
    @JsonIgnore
    private List<Post> posts = new ArrayList<>();

    @OneToMany(mappedBy = "user", cascade = CascadeType.ALL)
    @JsonIgnore
    private List<Comment> comments = new ArrayList<>();

    @OneToMany(mappedBy = "user", cascade = CascadeType.ALL)
    @JsonIgnore
    private List<CommentVote> comment_votes = new ArrayList<>();

    @OneToMany(mappedBy = "user", cascade = CascadeType.ALL)
    @JsonIgnore
    private List<PostVote> post_votes = new ArrayList<>();

    // getters and setters
}

This is CommentVote.java :

@Entity
@Table(name = "comment_vote")
public class CommentVote {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "vote_id", columnDefinition = "serial")
    private Long id;

    @Min(value = -1, message = "A vote can not be less than -1")
    @Max(value = 1, message = "A vote can not be greater than 1")
    @Column(name = "actual_vote")
    private int actualVote;

    @ManyToOne()
    @JoinColumn(name="user_id", nullable=false)
    @JsonIgnore
    private User user;

    // getters and setters
}

I tried with orphanRemoval = true on every child field in User.java but that does not seem to change anything.

Upvotes: 1

Views: 2188

Answers (1)

SternK
SternK

Reputation: 13091

You can try to use @OnDelete. As it's stated in the documentation:

... the @OnDelete cascade is a DDL-level FK feature which allows you to remove a child record whenever the parent row is deleted.

So, when annotating the @ManyToOne association with @OnDelete(action = OnDeleteAction.CASCADE), the automatic schema generator will apply the ON DELETE CASCADE SQL directive to the Foreign Key declaration.

Taken this in mind, you can correct your mapping in the following way:

@Entity
@Table(name = "comment_vote")
public class CommentVote {

    // ...

    @ManyToOne()
    @JoinColumn(name="user_id", nullable=false)
    @OnDelete(action = OnDeleteAction.CASCADE)
    @JsonIgnore
    private User user;

    // ...
}

Upvotes: 1

Related Questions