Hibernate UnUniqueify a column in table

database, hibernate, hql, java, postgresql

Solution

Short answer: replace the `@ElementCollection` by a `@ManyToMany` relation with a `@JoinTable` like this:

@ManyToMany
@JoinTable(
name="tbl_settings_objecteproxy_v2",
joinColumns = @JoinColumn(name = "id"),
inverseJoinColumns = @JoinColumn( name = "objectproxy_id"))
private Set<SomeObject> objectproxy;

See "2.2.5.3.2.1. Definition" in Hibernate Annotation Documentation

This results in a same side table but then without the unique constraint. So now this is possible:

|-------------------------------|
| tbl_object_proxy              |
| ------------------------------|
| Id (pk)| object_proxy_id (pk) |
|-------------------------------|
| 1      | 150 --               |
| 1      | 149  |= It works! The unique constraint is gone! 
| 2      | 150 --               |
| 2      | 151                  |
|-------------------------------|

Detailed answer and cause description: Somehow the `@ElementCollection` created a collectiontable with a one to many relation of the referenced key (collection | inverse join) which adds a unique constraint to the key referencing the other side table to reflect the one to many relationship which I didn't want. So I dropped the `@ElementCollection` and replaced it by a `@ManyToMany` relation with a `@JoinTable` annotation. I have also tried to declare the `@ManyToMany` relation in the `@ElementCollection` but it kept adding the Unique constraint to the referenced key.

My Settings class does now look like this:

@Entity
@Table(name = "tbl_user_settings", uniqueConstraints = {@UniqueConstraint(columnNames={"user_id"})})
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)

public class Settings
{
@Id
@SequenceGenerator(name="someSequence", sequenceName="SEQ_SOMENAME", allocationSize =1)
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="someSequence")
@Column(name="id")
private int setting_id;

@OneToOne
private User user;

@ManyToOne
private SomeObject someobject;

@ManyToMany
@JoinTable(
name="tbl_settings_objecteproxy_v2",
joinColumns = @JoinColumn(name = "id"),
inverseJoinColumns = @JoinColumn( name = "objectproxy_id"))
private Set<SomeObject> objectProxy;

/*...the rest...*/
}

Problem

Hibernate UnUniqueify a column in table(Solved) I want a field set to be non-unique on itself but to be unique in combination with the other field, I got this table with two columns(composite primary keys); id (primary key) and object_proxy_id (primary key), this is exactly what I need but hibernate sets the object_proxy_id to be unique on itself so that value cant be duplicate in the table, and I need this column to accept duplicate values. Because every user has its own object proxy and these proxy's don't have to be necessarily unique. This is what I want to achieve: ``` |-------------------------------| | tbl_object_proxy | | ------------------------------| | Id (pk)| object_proxy_id (pk) | |-------------------------------| | 1 | 150 -- | | 1 | 149 |= must be able to be DUPLICATE which is not the case right now. | 2 | 150 -- | | 2 | 151 | |-------------------------------| ``` Current code: ``` @Entity @Table(name = "tbl_user_settings", uniqueConstraints = {@UniqueConstraint(columnNames={"user_id"})}) @Inheritance(strategy = InheritanceType.TABLE_PER_CLASS) public class Settings implements Serializable { @Id @SequenceGenerator(name="someSequence", sequenceName="SEQ_SOMENAME", allocationSize =1) @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="someSequence") @Column(name="id") private int setting_id; @OneToOne private User user; @ManyToOne private SomeObject someobject; @ElementCollection @CollectionTable(name="tbl_collection_name", joinColumns= @JoinColumn(name="id"), uniqueConstraints = {@UniqueConstraint(columnNames={"id", "object_proxy_id"})}) @Column(name="SomeObject") private Set<SomeObject> objectProxy; /*...constructors and methods...*/ } ``` Results in: ``` -- Table schema |-------------------| | tbl_user_settings | |-------------------| | id |PK <<Unique>> | user_id |FK reference tbl_user <<Unique>> | object_id |FK reference tbl_object |-------------------| |------------------| | tbl_object_proxy | |------------------| | id |PK reference tbl_user_settings | object_proxy_id |PK reference tbl_object <<Unique>> BUT I DON'T WANT THIS TO BE UNIQUE ON ITSELF !!!! |------------------| ``` EDIT: The two primary key's in tbl_object_proxy are composite primary key's I have tried Xeon's solution but it didn't work.

Original source