How to fix "violates foreign key constraint" in Spring-Boot when trying to delete a record. Using @ManyToMany

Question

I am trying to delete some records from my database but I recieve the following error: "update or delete on table "role" violates foreign key constraint "fk3qjq7qsiigxa82jgk0i0wuq3g" on table "users_role" "

I did some research and I found that it might have something to do with one of my relationships being set to @ManyToMany.

I tried modifying my code from other examples in this site but with no luck.

public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "users_id")
    private long id;
    @Column(name = "email")
    @Email(message = "*Please provide a valid Email")
    @NotEmpty(message = "*Please provide an email")
    private String email;
    @Column(name = "password")
    @Length(min = 5, message = "*Your password must have at least 5 characters")
    @NotEmpty(message = "*Please provide your password")
    private String password;
    @Column(name = "name")
    @NotEmpty(message = "*Please provide your name")
    private String name;
    @Column(name = "surname")
    @NotEmpty(message = "*Please provide your surname")
    private String surname;
    @Column(name = "active")
    private int active;

    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "users_role", joinColumns = @JoinColumn(name = "users_id"), inverseJoinColumns = @JoinColumn(name = "role_id"))
    private Set<Role> roles;

    @Column(name = "position")
    private String position;

public class Role {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "role_id")
    private int id;

public class UserController {
.
.
.
.
@GetMapping("/admin/deleteuser/{id}")
    public String deleteUser(@PathVariable("id") long id, Model model) {
        User user = userRepository.findById(Long.valueOf(id)).orElseThrow(() -> new IllegalArgumentException("Invalid user Id:" + id));

        userRepository.delete(user);
        model.addAttribute("users", userRepository.findAll());
        return "/admin/show-users";
    }

Answer 1

The problem appears to be here:

    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "users_role", joinColumns = @JoinColumn(name = "users_id"), inverseJoinColumns = @JoinColumn(name = "role_id"))
    private Set<Role> roles;

In particular, CascadeType.ALL means that when you try to delete a User, that deletion will cascade to that User's roles. It is highly unlikely that you want that, for the whole point of making the relationship many-to-many instead of one-to-many would be that more than one user can have a given role. If you try to delete a role (whether directly or via cascading delete) there are then two alternatives:

1. the deletion cascades on to all other users having the same role (and then back to all those users' roles, etc., or
2. the deletion fails.

You're seeing (2). The specific constraint violation reported is related to other users' rows in the user_role table when you try to delete roles.

It's unclear to me whether you really want to cascade any persistence operations from Users to their assigned Roles. I'm inclined to think not, in which case you don't need to specify the cascade attribute of your ManyToMany at all:

    @ManyToMany
    @JoinTable(name = "users_role", joinColumns = @JoinColumn(name = "users_id"), inverseJoinColumns = @JoinColumn(name = "role_id"))
    private Set<Role> roles;

If you do cascade, however, then you'll need to specify the individual cascade types you want, omitting CascadeType.REMOVE, maybe something like

    @ManyToMany(cascade = {CascadeType.DETACH, CascadeType.REFRESH})
    @JoinTable(name = "users_role", joinColumns = @JoinColumn(name = "users_id"), inverseJoinColumns = @JoinColumn(name = "role_id"))
    private Set<Role> roles;