Unique index or primary key violation Spring JPA

Question

I'm getting this primary key constraint error

Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.DataIntegrityViolationException: could not execute statement [Unique index or primary key violation: "PUBLIC.CONSTRAINT_INDEX_3 ON PUBLIC.USER_TACO_ORDER_REF(TACO_ORDER_ID NULLS FIRST) VALUES ( /* 1 */ '52aca972-aeac-4305-9021-ad17ea1a0b07' )"; SQL statement:
insert into user_taco_order_ref (user_id,taco_order_id) values (?,?) [23505-220]] [insert into user_taco_order_ref (user_id,taco_order_id) values (?,?)]; SQL [insert into user_taco_order_ref (user_id,taco_order_id) values (?,?)]; constraint ["PUBLIC.CONSTRAINT_INDEX_3 ON PUBLIC.USER_TACO_ORDER_REF(TACO_ORDER_ID NULLS FIRST) VALUES ( /* 1 */ '52aca972-aeac-4305-9021-ad17ea1a0b07' )"

I'm saving more than one TacoOrder objects in one session. In first order controller works fine. It is saved in database and no errors. After that, I add it to the User's tacoOrder list collection, and as its Cascade.ALL, its saved automatically. Database is reset everytime so it's just one object in the list.

In second Order, I reset the current sessional TacoOrder object and it goes through same controller and is added in User's list and when hibernate persists the User. The list is persisted too, but it now has 2 tacoOrders, one of which is already persisted and saving that is giving me error.

These are the Controller and entities

@Transactional
@PostMapping("/current")
    public String processOrder(@ModelAttribute("order") @Valid TacoOrder order, Errors errors,
                               @AuthenticationPrincipal User user) {
        if(errors.hasErrors()){
            return "orderForm";
        }


        order.setUser(user);
        order.setPlacedAt(new Date());
        user.addTacoOrder(order);

        log.info("4 {} {}", order, user);  //LOG

        TacoOrder tacoOrder  = orderRepository.save(order);
        userService.updateUserFromOrder(user, tacoOrder);
        userRepository.save(user);

        log.info("Order submitted: {}", tacoOrder);   //LOG
        return "redirect:/orders";
    }

@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    @JoinTable(
            name = "User_Taco_Order_Ref",
            joinColumns = @JoinColumn(name = "user_id"),
            inverseJoinColumns = @JoinColumn(name = "tacoOrder_id"))
    private List<TacoOrder> tacoOrders = new ArrayList<>();

These are the logs:- First Order:

4 TacoOrder(id=52aca972-aeac-4305-9021-ad17ea1a0b07, placedAt=Fri Mar 29 04:07:34 IST 2024, deliveryName=a, deliveryStreet=a, deliveryCity=a, deliveryState=a, deliveryZip=a, ccNumber=4417123456789113, ccExpiration=11/2024, ccCVV=111, tacos=[Taco(id=1, createdAt=Fri Mar 29 04:07:24 IST 2024, name=Classic Beef Taco, ingredients=[Ingredient(id=FLTO, name=Flour Tortilla, type=WRAP), Ingredient(id=GRBF, name=Ground Beef, type=PROTEIN), Ingredient(id=CHED, name=Cheddar, type=CHEESE), Ingredient(id=LETC, name=Lettuce, type=VEGGIES), Ingredient(id=SLSA, name=Salsa, type=SAUCE)])])

User(id=1, username=admin, password=$2a$10$zpjL0V253nLpa1cH0j5sNOWKrfyikWjMZcXzc1TQ5HtttSgQgGi7O, roles=[ROLE_USER, ROLE_ADMIN], fullName=null, street=null, city=null, state=null, zip=null, phoneNumber=null, tacoOrders=[TacoOrder(id=52aca972-aeac-4305-9021-ad17ea1a0b07, placedAt=Fri Mar 29 04:07:34 IST 2024, (rest is same)

Second Order:

4 TacoOrder(id=37808e6b-ed1d-4f38-8652-8c672b88d5e5, placedAt=Fri Mar 29 04:07:52 IST 2024, deliveryName=1, deliveryStreet=1, deliveryCity=1, deliveryState=1, deliveryZip=1, ccNumber=4417123456789113, ccExpiration=11/2024, ccCVV=111, tacos=[Taco(id=2, createdAt=Fri Mar 29 04:07:39 IST 2024, name=Spicy Carnitas Delight, ingredients=[Ingredient(id=COTO, name=Corn Tortilla, type=WRAP), Ingredient(id=CARN, name=Carnitas, type=PROTEIN), Ingredient(id=JALA, name=Jalapeños, type=VEGGIES), Ingredient(id=LETC, name=Lettuce, type=VEGGIES), Ingredient(id=SLSA, name=Salsa, type=SAUCE)])])

User(id=1, username=admin, password=$2a$10$zpjL0V253nLpa1cH0j5sNOWKrfyikWjMZcXzc1TQ5HtttSgQgGi7O, roles=[ROLE_USER, ROLE_ADMIN], fullName=null, street=a, city=a, state=a, zip=a, phoneNumber=null, tacoOrders=[TacoOrder(id=52aca972-aeac-4305-9021-ad17ea1a0b07, placedAt=Fri Mar 29 04:07:34 IST 2024,

, TacoOrder(id=37808e6b-ed1d-4f38-8652-8c672b88d5e5, placedAt=Fri Mar 29 04:07:52 IST 2024,

as per logs first order @id is

52aca972-aeac-4305-9021-ad17ea1a0b07

and second order @id is

37808e6b-ed1d-4f38-8652-8c672b88d5e5

Now in second Order, User has both the first Order (which was already persisted in User_Taco_Order_Ref table) and second new Order which is yet to be added in the User_Taco_Order_Ref table. Hibernate is saving the whole collection again and giving me this error while saving already persisted instanced of taco orders. How can I fix it?

Answer 1

Error was due to @JoinTable annotation on the tacoOrder list in USER entity. Hibernate, when making this new table was adding already persisted tacoOrders whenever i saved the User object due to cascade. By using mappedBy on this table like this, we remove this extra table.

@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER, mappedBy = "user")
    private List<TacoOrder> tacoOrders = new ArrayList<>();

As its pretty uncommon to use joinTable on OneToMany relation and hibernate does not add the already persisted entity again. On the tacoOrder entity, we use the foreign key element instead.

@ManyToOne
    @JoinColumn(name = "user_id")
    @ToString.Exclude
    private User user;