Spring Boot用户注册遇SQLIntegrityConstraintViolationException重复键错误求助

在Spring Boot（Java）项目中，创建过ROLE_USER、ROLE_ADMIN角色的用户后，再次创建带角色的新用户时抛出Duplicate entry for key错误，相关代码及错误信息如下：

请求体 #

{
    "username" : "admin1",
    "password" : "admin1",
    "email" : "admin1@refreshtoken.com",
    "roles" : [
            "ROLE_ADMIN"
    ]
}

实体类代码 #

@Entity
@Table(name = "Roles")
@NoArgsConstructor
@Getter
@Setter
public class Role extends IdBasedEntity implements Serializable {

    @Enumerated(EnumType.STRING)
    @Column(length = 20, unique = true)
    private ERole name;

    public Role(ERole name){
        this.name = name;
    }

    public ERole getName() {
        return name;
    }
}

@Entity
@Table(name = "Users")
@Data
@NoArgsConstructor
public class User extends IdBasedEntity implements Serializable {

    @Column(unique = true, nullable = false)
    private String username;

    @Column(unique = true, nullable = false)
    private String email;

    private String password;

    @ManyToMany(cascade = CascadeType.ALL,fetch = FetchType.EAGER)
    @JoinTable(name = "user_roles",
            joinColumns = @JoinColumn(name = "user_id"),
            inverseJoinColumns = @JoinColumn(name = "role_id"))
    private Set<Role> roles = new HashSet<>();

}

public enum ERole {
    ROLE_USER,
    ROLE_ADMIN
}

@MappedSuperclass
@Getter
@Setter
public class IdBasedEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    protected Integer id;
}

@Entity
@Table(name = "RefreshToken")
@Data
public class RefreshToken extends IdBasedEntity implements Serializable {

    @OneToOne
    @JoinColumn(name = "user_id", referencedColumnName = "id")
    private User user;

    @Column(nullable = false, unique = true)
    private String token;

    @Column(nullable = false)
    private Instant expiryDate;
}

注册接口代码 #

@PostMapping("/signup")
public ResponseEntity<?> registerUser(@RequestBody SignUpRequest signUpRequest) {

    String username = signUpRequest.getUsername();
    String email = signUpRequest.getEmail();
    String password = signUpRequest.getPassword();
    Set<String> strRoles = signUpRequest.getRoles();
    Set<Role> roles = new HashSet<>();

    if(userService.existsByUsername(username)){
        return ResponseEntity.badRequest().body(new MessageResponse("Error: Username is already taken!"));
    }

    if(userService.existsByEmail(email)){
        return ResponseEntity.badRequest().body(new MessageResponse("Error: Email is already taken!"));
    }

    User user = new User();
    user.setEmail(email);
    user.setUsername(username);
    user.setPassword(encoder.encode(password));

    if (strRoles != null) {
        strRoles.forEach(role -> {
            switch (role) {
                case "ROLE_ADMIN":
                    roles.add(new Role(ERole.ROLE_ADMIN));
                    break;
                default:
                    roles.add(new Role(ERole.ROLE_USER));
            }
        });
    }else{
        roles.add(new Role(ERole.ROLE_USER));
    }

    user.setRoles(roles);
    userService.saveUser(user);  // ERROR LINE

    return ResponseEntity.ok(new MessageResponse("User registered successfully!"));
}

错误信息 #

// Create User
java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'ROLE_USER' for key 'roles.UK_ofx66keruapi6vyqpv6f2or37'
// Create Admin
java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'ROLE_ADMIN' for key 'roles.UK_ofx66keruapi6vyqpv6f2or37'

错误原因 #

1. Role表唯一约束：Role实体的name字段设置了unique = true，数据库不允许重复的角色名称。
2. 级联操作过度：User与Role的@ManyToMany注解使用了CascadeType.ALL，保存用户时会尝试级联保存关联的所有Role实体，而这些角色已存在于数据库中，触发唯一约束冲突。
3. 注册逻辑缺陷：每次注册都直接创建新的Role对象，未先查询数据库中是否已存在对应角色，直接触发重复插入。

解决方案 #

1. 调整级联操作类型 #

修改User实体中@ManyToMany的级联类型，移除会触发重复插入的CascadeType.PERSIST之外的不必要操作，推荐保留PERSIST和MERGE：

@ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE}, fetch = FetchType.EAGER)
@JoinTable(name = "user_roles",
        joinColumns = @JoinColumn(name = "user_id"),
        inverseJoinColumns = @JoinColumn(name = "role_id"))
private Set<Role> roles = new HashSet<>();

2. 复用已存在的角色 #

第一步：创建Role查询Repository

public interface RoleRepository extends JpaRepository<Role, Integer> {
    Optional<Role> findByName(ERole name);
}

第二步：修改注册接口的角色处理逻辑

注入RoleRepository，查询数据库中已存在的角色，不存在时再创建新角色：

@Autowired
private RoleRepository roleRepository;

@PostMapping("/signup")
public ResponseEntity<?> registerUser(@RequestBody SignUpRequest signUpRequest) {

    String username = signUpRequest.getUsername();
    String email = signUpRequest.getEmail();
    String password = signUpRequest.getPassword();
    Set<String> strRoles = signUpRequest.getRoles();
    Set<Role> roles = new HashSet<>();

    if(userService.existsByUsername(username)){
        return ResponseEntity.badRequest().body(new MessageResponse("Error: Username is already taken!"));
    }

    if(userService.existsByEmail(email)){
        return ResponseEntity.badRequest().body(new MessageResponse("Error: Email is already taken!"));
    }

    User user = new User();
    user.setEmail(email);
    user.setUsername(username);
    user.setPassword(encoder.encode(password));

    if (strRoles != null) {
        strRoles.forEach(role -> {
            switch (role) {
                case "ROLE_ADMIN":
                    Role adminRole = roleRepository.findByName(ERole.ROLE_ADMIN)
                            .orElseGet(() -> roleRepository.save(new Role(ERole.ROLE_ADMIN)));
                    roles.add(adminRole);
                    break;
                default:
                    Role userRole = roleRepository.findByName(ERole.ROLE_USER)
                            .orElseGet(() -> roleRepository.save(new Role(ERole.ROLE_USER)));
                    roles.add(userRole);
            }
        });
    }else{
        Role userRole = roleRepository.findByName(ERole.ROLE_USER)
                .orElseGet(() -> roleRepository.save(new Role(ERole.ROLE_USER)));
        roles.add(userRole);
    }

    user.setRoles(roles);
    userService.saveUser(user);

    return ResponseEntity.ok(new MessageResponse("User registered successfully!"));
}

3. 初始化基础角色（可选） #

项目启动时预创建ROLE_USER和ROLE_ADMIN角色，避免首次注册时才创建：

@Component
public class RoleInitializer implements ApplicationRunner {

    @Autowired
    private RoleRepository roleRepository;

    @Override
    public void run(ApplicationArguments args) throws Exception {
        if(!roleRepository.findByName(ERole.ROLE_USER).isPresent()){
            roleRepository.save(new Role(ERole.ROLE_USER));
        }
        if(!roleRepository.findByName(ERole.ROLE_ADMIN).isPresent()){
            roleRepository.save(new Role(ERole.ROLE_ADMIN));
        }
    }
}

内容的提问来源于stack exchange，提问作者Sercan Noyan Germiyanoğlu