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?

Solution 1

Use @UniqueConstraint:

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

Solution 2

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

Solution 3

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).

Comments

[Javier Gutierrez]

what about make author and number @id like FK ??

[Manu]

and for multiple composite uniqueConstraints, the syntax is @Table(uniqueConstraints = {@UniqueConstraint(columnNames = { "field1", "field2", "field3" }), @UniqueConstraint(columnNames = {"field4", "field5"})}) )

[Adelin]

Adding this unique constraint, hibernate is no more able to create my table when I deploy the application. Can you help some.

[Satish Patro]

The uniqueness is for (productId) column & (serial) column or for constraint of 2 column in total (productId, serial)?