Unique constraint on multiple columns with JPA annotation

Question

I have a problem when i add a unique constraint on multiple columns with JPA annotation.
I use PostgresSQL

I'd like to add unique constraint on 3 columns : "user_id", "science_id" and "difficulty"

In other words : I can have

0, 1, "EASY"
0, 1, "HARD" <= because difficulty is different
0, 2, "EASY"
1, 2, "EASY"

But not
0, 1, "EASY"
0, 1, "EASY" <= Not unique
0, 2, "EASY"
1, 2, "EASY"

Here is my Entity :

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "user_basket",
        uniqueConstraints = { @UniqueConstraint(
                columnNames = { "user_id", "science_id", "difficulty" }) })
public class UserBasket {

    @Id
    @GeneratedValue
    private Integer id;

    @JsonIgnore
    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id")
    private User user;

    @JsonIgnore
    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "science_id")
    private Science science;

    @Enumerated(EnumType.STRING)
    private Difficulty difficulty;

    @Column(nullable = false, updatable = false)
    @CreationTimestamp
    private Timestamp addToBasket;

}

So, i add with postman :

{
    "scienceId": 1,
    "difficulty": "EASY"
}

And add an other but with an other difficulty :

{
    "scienceId": 1,
    "difficulty": "HARD"
}

I got error when second is inserted :

2023-09-06T16:04:00,284 ERROR [http-nio-8080-exec-4] o.h.e.j.s.SqlExceptionHelper: ERREUR: la valeur d'une clé dupliquée rompt la contrainte unique « uk_5cesgns8lwu884yww505jep8t »
  Détail : La clé « (science_id)=(1) » existe déjà.
2023-09-06T16:04:00,295 ERROR [http-nio-8080-exec-4] c.d.x.ExceptionHandler: could not execute statement [ERREUR: la valeur d'une clé dupliquée rompt la contrainte unique « uk_5cesgns8lwu884yww505jep8t »
  Détail : La clé « (science_id)=(1) » existe déjà.] [insert into user_basket (add_to_basket,difficulty,science_id,user_id,id) values (?,?,?,?,?)]
2023-09-06T16:04:00,295 ERROR [http-nio-8080-exec-4] c.d.x.ExceptionHandler: org.springframework.dao.DataIntegrityViolationException: could not execute statement [ERREUR: la valeur d'une clé dupliquée rompt la contrainte unique « uk_5cesgns8lwu884yww505jep8t »
  Détail : La clé « (science_id)=(1) » existe déjà.] [insert into user_basket (add_to_basket,difficulty,science_id,user_id,id) values (?,?,?,?,?)]; SQL [insert into user_basket (add_to_basket,difficulty,science_id,user_id,id) values (?,?,?,?,?)]; constraint [null]

Thanks a lot, Best regards

Answer 1

I think it's because of your OneToOne mappings. If I change those to @ManyToOne(fetch = FetchType.EAGER) I'm able to successfully insert two records with only Difficulty being different. If I try to do a third record with the same Difficulty I get the unique key constraint error.