JPA How add unique contraint on column for @OneToMany relation like on username

Question

I have a Class Site that represents a website and a Class User. A Site can have multiple Users.

class Site {

    private int site_ID;

    @OneToMany // with a join table
    private List<User> users;
    // ...
}

class User {

    private int user_ID;

    private String name;

    private String lastname;

    private String username;

    private String password;

}

I want to allow same username to exist on all Sites, but only one by site.

Site/User/username
1   /1   /username1
1   /2   /username2
2   /3   /username1

How can I do that?

Answer 1

Let the user have a Site reference:

@ManyToOne(optional=false)
private Site site;

Now add the constraint to user:

@Table(uniqueConstraints = {
    @UniqueConstraint(columnNames = { "username", "site" })})
@Entity
public class User{
// etc
}

You will also have to change the Site mapping:

@OneToMany(mappedBy="site")
private List<User> users;

Answer 2

By default, the primary index on the join table is unique and based on the site and user FK So, you cannot have the same user with the same site duplicated.

But if you want to force a constraint :

class Site {

    private int site_ID;

    @OneToMany // with a join table
    @JoinTable(
        uniqueConstraints=@UniqueConstraint(columnNames={"Site_ID","users_ID"})
    )
    private List<User> users;
    // ...
}