Spring Boot JPA: Unable to add multiple products to cart - Duplicate entry error for unique constraint

Question

I'm building an e-commerce application using Spring Boot and JPA. I'm encountering an issue where users can't add multiple products to their cart. The first product adds successfully, but trying to add a second product results in a unique constraint violation. How can I resolve it?

Entity Classes:

@Entity
@Table(name = "users")
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Data
public class AppUser implements UserDetails {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private long id;
    @Column(unique = true)
    private String username;
    private String firstname;
    @Column(unique = true)
    private String email;
    private String phone;
    private String password;
    @Enumerated(EnumType.STRING)
    private Role role;
    private String address;
    private Date createdAt;

    @OneToMany(mappedBy = "user", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Cart> carts = new ArrayList<>();
    @OneToMany(mappedBy = "user", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Product> products = new ArrayList<>();
    @OneToMany(mappedBy = "user", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Order> orders = new ArrayList<>();


@Entity
@Table(name = "carts")
public class Cart {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private long id;
    private long productId;
    private int quantity;
    private Date createdAt;

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

my cart service

public Cart addProductToCart(Cart newCart, AppUser user) {
        Optional<Cart> existingCart = cartRepo.findCartByIdAndUser(newCart.getProductId(), user);
        int productInventoryCount = productRepo.findById(newCart.getProductId()).get().getInventoryCount();

        if (existingCart.isPresent()) {
            Cart cart = existingCart.get();

            if ((cart.getQuantity() + newCart.getQuantity()) > productInventoryCount) {
                throw new InsufficientInventoryException("Insufficient inventory for the requested quantity.");
            }

            cart.setQuantity(cart.getQuantity() + newCart.getQuantity());

            return cartRepo.save(cart);
        }
        if (newCart.getQuantity() > productInventoryCount) {
            throw new InsufficientInventoryException("Insufficient inventory for the requested quantity.");
        }

        newCart.setCreatedAt(new Date());
        return cartRepo.save(newCart);
    }

This the error that I get:

2025-02-14T19:49:24.717+04:00 ERROR 35347 --- [E-CommerceAPI] [nio-8080-exec-3] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.DataIntegrityViolationException: could not execute statement [Duplicate entry '1' for key 'carts.UK64t7ox312pqal3p7fg9o503c2'] [insert into carts (created_at,product_id,quantity,user_id,id) values (?,?,?,?,?)]; SQL [insert into carts (created_at,product_id,quantity,user_id,id) values (?,?,?,?,?)]; constraint [carts.UK64t7ox312pqal3p7fg9o503c2]] with root cause

java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '1' for key 'carts.UK64t7ox312pqal3p7fg9o503c2'

Ai tells that delete index like this:

ALTER TABLE carts DROP INDEX UK64t7ox312pqal3p7fg9o503c2;

but when i try delete it i get:

Error Code: 1553. Cannot drop index 'UK64t7ox312pqal3p7fg9o503c2': needed in a foreign key constraint

Answer 1

the error message is due to unique constraint violation in your carts table.

your current findCartByIdAndUser query might not be filtering both userId and productId.

try modifying your repository method:

@Repository
public interface CartRepository extends JpaRepository<Cart, Long>{
Optional<Cart> findByProductIdAndUserId(long productId, long userId);
}

then try updating your service method:

public Cart addProductToCart(Cart newCart, AppUser user) {
    Optional<Cart> existingCart = cartRepo.findByProductIdAndUserId(newCart.getProductId(), user.getId());
    int productInventoryCount = productRepo.findById(newCart.getProductId()).get().getInventoryCount();

    if (existingCart.isPresent()) {
        Cart cart = existingCart.get();

        if ((cart.getQuantity() + newCart.getQuantity()) > productInventoryCount) {
            throw new InsufficientInventoryException("Insufficient inventory for the requested quantity.");
        }

        cart.setQuantity(cart.getQuantity() + newCart.getQuantity());
        return cartRepo.save(cart);
    }

    if (newCart.getQuantity() > productInventoryCount) {
        throw new InsufficientInventoryException("Insufficient inventory for the requested quantity.");
    }

    newCart.setUser(user);  // Ensure user is set
    newCart.setCreatedAt(new Date());
    
    return cartRepo.save(newCart);
}