It is very common that you want to add some unique key constraint in your database schema. Say for a online shopping system you have the following entity:
public class CreditCard {
private UUID id;
private long number;
private long customerId;
private String nickname;
}It is general to think that a customer should have only one credit card with a certain nickname. Otherwise, when the customer see two credit cards with the same nickname, how can he/she tell that which one ends in 1057 and which one ends in 0493?
Then you may consider add the following annotation:
public class CreditCard {
...
@Column(unique = true)
private String nickname;
}This will add a unique key constraint to the nickname column in the credit_card table, in the DDL schema.
Sometimes this is enough, but in our case consider the following question: Should two different customer be allowed to have credit cards with same nickname? Obviously the answer is Yes. But in our definition above that will be prevented, so we need to do something more.
Before revealing the correct answer, let's see another incorrect practice first.
public class CreditCard {
...
@Column(unique = true)
private long customerId;
@Column(unique = true)
private String nickname;
}Did you see what is wrong in the above implementation? the unique constraints are separately defined. Instead of doing what we want, the above actually define that "a customer can only hold one credit card, and no two credit cards share the same nickname, even if they belong to different customers".
It turns out that if we want to define a composite unique key by more than one column, we cannot use `@Column` annotation on fields, but instead we use `@Table` annotation on the class in the following way
@Table(uniqueConstraints = { @UniqueConstraint(columnNames = { "customer_id", "nickname" }) })
public class CreditCard {
...
private long customerId;
private String nickname;
}This will do exactly what we want: no duplicate nickname on cards for a single customer, but across customers nickname can be repeated used.
Eventually, remember that these annotations only help defining the DDL schema. If you have an existing application and created database and you want to add the constraint, only adding the annotations will make no change. In addition, you may need to run the following SQL command in your database
alter table `credit_card` add constraint `UK5cpweuif6sc84fqm30wekjwkl` unique (`customer_id`, `nickname`);