DataIntegrityViolationException Thrown for Unique Fields Sequentially, Not Simultaneously

I'm encountering an issue with my Spring Boot application where DataIntegrityViolationException is being thrown for unique fields sequentially, rather than simultaneously. This is occurring despite having unique constraints and @Column(unique = true) annotations in place for both the username and email fields. I double checked and they both have unique constraint present in the postgres as well.

Specific Behaviour:

1. Request with Duplicate Username and Email:

• Request body:

{
    "email": "test@test.com",
    "password": "test",
    "username": "test"
}

Exception thrown: DataIntegrityViolationException for email only.

2. Request with Updated Email, Duplicate Username:

• Request Body:

{
    "email": "newemail@example.com",
    "password": "test",
    "username": "test"
}

Exception thrown: DataIntegrityViolationException for username;

Expected Behavior:

I'd expect the application to throw DataIntegrityViolationException for both violating fields (username and email) simultaneously in the first request, as both fields have unique constraints.

Here are my files:

Controller:

@RestController
@RequiredArgsConstructor
@RequestMapping("/api/v1/auth")
public class AuthenticationController {
    @Autowired
    private final AuthenticationService authenticationService;

    @PostMapping("/register")
    @Transactional
    public ResponseEntity<APIResponse<AuthenticationResponseDTO>> register(
            @Valid @RequestBody RegisterRequestDTO request
    ) {
        APIResponse<AuthenticationResponseDTO> response = authenticationService.register(request);
        return ResponseEntity.status(HttpStatus.valueOf(response.getHttpStatus())).body(response);
    }

}

Service:

@Service
@RequiredArgsConstructor
public class AuthenticationService {
    @Autowired
    private final UserRepository userRepository;

    @Autowired
    private final JwtTokenService jwtService;

    @Autowired
    private final PasswordEncoder passwordEncoder;

    @Transactional
    public APIResponse<AuthenticationResponseDTO> register(RegisterRequestDTO request) {
        var user = User.builder()
                       .email(request.getEmail())
                       .username(request.getUsername())
                       .password(passwordEncoder.encode(request.getPassword()))
                       .type(request.getUserType())
                       .build();

        var jwtToken = jwtService.generateToken(user);
        user.setAuthToken(jwtToken); 

        userRepository.saveAndFlush(user); //I've Placed the debugger here
        AuthenticationResponseDTO authenticationResponse = AuthenticationResponseDTO.builder()
                                                                                    .accessToken(jwtToken)
                                                                                    .build();
        return APIResponse.ok(authenticationResponse,
                              Constant.getUserResponseHashMap(),
                              Constant.USER_RESPONSE_CODE_PREFIX.concat("7")
        );
    }
}

User class:

@Data
@Entity
@Builder
@NoArgsConstructor
@Table(name = "users", uniqueConstraints = { @UniqueConstraint(columnNames = "username"),
                                             @UniqueConstraint(columnNames = "email")
})
@AllArgsConstructor
public class User implements UserDetails {
    @Id
    @GeneratedValue(generator = "uuid2")
    @GenericGenerator(name = "uuid2", strategy = "uuid2")
    private UUID id;

    @NotEmpty(message = "Username can not be empty")
    private String username;

    private String fullname;

    @NotEmpty(message = "Email can not be empty")
    @Email(regexp = "[a-z0-9._%+-]+@[a-z0-9.-]+\\.[a-z]{2,3}", flags = Pattern.Flag.CASE_INSENSITIVE, message =
            "Invalid Email Syntax")
    private String email;

    @NotEmpty(message = "Password can not be empty")
    private String password;

    @NotNull
    @Builder.Default
    @ColumnDefault("true")
    private boolean active = true;

    @Column(name = "auth_token")
    private String authToken;

    @Column(name = "user_type")
    @Enumerated(value = EnumType.STRING)
    private UserType type;

    @Override
    public Collection<? extends GrantedAuthority> getAuthorities() {
        return type.getAuthorities();
    }

    @Override
    public boolean isAccountNonExpired() {
        return true;
    }

    @Override
    public boolean isAccountNonLocked() {
        return true;
    }

    @Override
    public boolean isCredentialsNonExpired() {
        return true;
    }

    @Override
    public boolean isEnabled() {
        return true;
    }
}

UPDATED

I think you can throw this exception for both violating fields simultaneously because DataIntegrityViolationException just reproduce SGBD side getting error on Spring. on SGBD side here is the errors you get when you try to insert data with unique constrainst violation (with mysql):

, .

Whether the unique constraint is on one or two fields, we have the same error. so I don't think we can get both errors at the same time.

To catch and handle programmatically this error, you can do that in register() method:

@Transactional
public APIResponse<AuthenticationResponseDTO> register(RegisterRequestDTO request) {
  if (Boolean.TRUE.equals(userRepository.existsByUsernameAndEmail(request.getUsername(), request.getEmail()))) {
        throw new ResponseStatusException(
                HttpStatus.CONFLICT, "Error: Username and Email is already exist for an user !", new RuntimeException("Error: Username and Email is already exist for an user !"));
    }
  var user = User.builder()
               .email(request.getEmail())
               .username(request.getUsername())
               .password(passwordEncoder.encode(request.getPassword()))
               .type(request.getUserType())
               .build();
//...
}

And in UserRepository add jpa criteria api existsByUsernameAndEmail():

public interface UserRepository extends JpaRepository<User, Long> {

  Boolean existsByUsernameAndEmail(String username, String email);
  //...
}

Another way you could also do, would be to catch error and handle it on user repository saveAndFlush() method call:

//...
try {
  userRepository.saveAndFlush(user);
} catch (DataIntegrityViolationException e) {
  // log error here if you want;
}
//...