Creating a composite Unique constraints on multiple columns

Question

This is my model:

class User {...}
class Book {
  User author;
  int number;
}

Every book number starts at 1 per author and increments upwards. So we'll have Books 1,2,3 by John Grisham, Book 1..5 by George Martin, etc...

Is there a unique constraint I can place on Book, that would guarantee we don't have two books with the same number by the same author? Similar to @Column(unique = true), but the constraint only applies on the composite of Author X number?

Possible duplicate of [How to introduce multi-column constraint with JPA annotations?](http://stackoverflow.com/questions/2772470/how-to-introduce-multi-column-constraint-with-jpa-annotations) — Devendra Bhatte, Jan 25 '17 at 13:58

score 152 · Accepted Answer · answered Nov 23 '11 at 17:17

152

Use @UniqueConstraint:

@Table(
    uniqueConstraints=
        @UniqueConstraint(columnNames={"author_id", "number"})
)
@Entity
class Book extends Model {
   @ManyToOne
   @JoinColumn(name = "author_id")
   User author;
   int number; 
}

answered Nov 23 '11 at 17:17

axtavt

228,184
37
489
472

what about make author and number @id like FK ?? – Javier Gutierrez Apr 10 '13 at 17:35
1

and for multiple composite uniqueConstraints, the syntax is @Table(uniqueConstraints = {@UniqueConstraint(columnNames = { "field1", "field2", "field3" }), @UniqueConstraint(columnNames = {"field4", "field5"})}) ) – Manu Jul 26 '14 at 09:47
Adding this unique constraint, hibernate is no more able to create my table when I deploy the application. Can you help some. – Adelin Feb 24 '15 at 07:38
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:20

score 3 · Answer 2 · answered Sep 14 '15 at 09:33

3

When table is created before, it is necessary to remove it. Unique key is not added to existing table.

answered Sep 14 '15 at 09:33

Tomasz Janisiewicz

589
8
9

score 1 · Answer 3 · answered Mar 20 '18 at 11:15

As @axtavt has answered, you can use the @UniqueConstraint approach. But in case of an existing table, there are multiple possibilities. Not all the times, but in general you may get an SQLException. The reason is that you may have some existing data in your table that is conflicting to the Composite Unique key. So all you can do to avoid this is to first manually check (By using simple SQL query) if all your existing data is good to go with Composite Unique Key. If not, of course, remove the data causing the violation. (Another way is to remove the whole existing table but can be used only it doesn't contain any important data).

Creating a composite Unique constraints on multiple columns

3 Answers3

Linked