Creating a composite Unique constraints on multipl

问题:

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?

回答1:

Use @UniqueConstraint:

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

回答2:

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

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