How to introduce multi-column constraint with JPA annotations?

Question

I am trying to introduce a multi-key constraint on a JPA-mapped entity:

public class InventoryItem {
    @Id
    private Long id;

    @Version 
    private Long version;

    @ManyToOne
    @JoinColumn("productId")
    private Product product;

    @Column(nullable=false);
    private long serial;
}

Basically (product, serial) pair should be unique, but I only found a way to say that serial should be unique. This obviously isn't a good idea since different products might have same serial numbers.

Is there a way to generate this constraint via JPA or am I forced to manually create it to DB?

score 210 · Accepted Answer · edited May 19 '16 at 13:39

210

You can declare unique constraints using the @Table(uniqueConstraints = ...) annotation in your entity class, i.e.

@Entity
@Table(uniqueConstraints={
    @UniqueConstraint(columnNames = {"productId", "serial"})
}) 
public class InventoryItem {
    ...
}

Note that this does not magically create the unique constraint in the database, you still need a DDL for it to be created. But seems like you are using some sort of automated tool for creating the database based on JPA entity definitions.

edited May 19 '16 at 13:39

russellhoff

1,599
3
26
56

answered May 05 '10 at 10:56

psp

2,904
1
17
17

1

Is this sort of thing needed for an existing database with constraints already in place? – Rob Apr 27 '17 at 00:39
I do believe that the constraint will be created the JPA provider is creating the data base. – AlanObject Jan 12 '19 at 20:48
The uniqueness is for (productId) column & (serial) column or for constraint of 2 column in total (productId, serial)? – P Satish Patro Aug 21 '19 at 05:19

score 73 · Answer 2 · edited Feb 26 '21 at 07:21

73

As already answered, multi-column index can be added using @Table annotation. However, columnNames needs to be the name of actual DB columns, not the class attribute. So, if the column is like the following:

@Column(name="product_id")
Long productId;

Then the @Table annotation should be like the following

@Table(uniqueConstraints=
       @UniqueConstraint(columnNames = {"product_id", "serial"}))

edited Feb 26 '21 at 07:21

GreenROBO

4,268
3
21
38

answered Aug 14 '14 at 19:03

SJha

1,280
1
9
15

11

This is a very important clarification: table names and not object names. – Calabacin Jun 30 '17 at 11:48
1

The uniqueness is for (productId) column & (serial) column or for constraint of 2 column in total (productId, serial)? – P Satish Patro Aug 21 '19 at 05:19
Kotlin: Take a look on this answer to find an example for kotlin: https://stackoverflow.com/a/47000044/285431 – Dirk Nov 21 '19 at 16:04
Syntax Error. You're missing the closing parenthesis on the @Table annotation. – Evvo Dec 11 '19 at 03:27

How to introduce multi-column constraint with JPA annotations?

2 Answers2

Linked