How to prevent Hibernate to making a column with unique = true automatically

Nitros64 · November 10, 2020, 10:10pm

Hi this is hard to explain since I don’t speak english so… This is my code

@Entity
@Table(name = "table_a")
public class TableA implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "id")
    private Long id;
    @Basic(optional = false)
    @Column(name = "name")
    private String name;
}
@Entity
@Table(name = "table_b")
public class TableB implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "id")
    private Long id;
    @Basic(optional = false)
    @Column(name = "name")
    private String name;
}
public class TableAb implements Serializable {
    private static final long serialVersionUID = 1L;    
    @EmbeddedId
    private TableAbPK tableAbPK;
        
    @ManyToOne(fetch = FetchType.EAGER, optional = false)
    @MapsId("idA")
    @JoinColumn(name = "id_a",unique = false,insertable = false, updatable = false)
    @OnDelete(action = OnDeleteAction.CASCADE) 
    private TableA tableA;
    
    @ManyToOne(fetch = FetchType.EAGER, optional = false)
    @MapsId("idB")
    @JoinColumn(name = "id_b",unique = false,insertable = false, updatable = false)    
    @OnDelete(action = OnDeleteAction.CASCADE)
    private TableB tableB;
}
@Embeddable
public class TableAbPK implements Serializable {
    //@Basic(optional = false)
    @Column(name = "id_a")
    private long idA;    
    //@Basic(optional = false)
    @Column(name = "id_b")
    private long idB;
}
@Entity
@Table(name = "table_da")
public class TableDa implements Serializable {
    private static final long serialVersionUID = 1L;    
    @EmbeddedId
    private TableDaPK tableDaPK;

    @ManyToOne(optional = false)
    @JoinColumns({
        @JoinColumn(name = "id_a", referencedColumnName = "id_a") //,insertable = false, updatable = false, unique=false
        //@JoinColumn(name = "id_b", referencedColumnName = "id_b")
    })    
    private TableAb tableAb;    
    
    @ManyToOne(optional = false)    
    @MapsId("idD")
    @JoinColumn(name = "id_d")
    private TableD tableD;
}
@Entity
@Table(name = "table_d")
public class TableD implements Serializable {
    private static final long serialVersionUID = 1L;    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "id")
    private Long id;
    
    @Basic(optional = false)
    @Column(name = "name")
    private String name;
}

Everything runs “good”, but the problem is here…

Hibernate: create table table_a (id bigint not null auto_increment, name varchar(255) not null, primary key (id)) engine=InnoDB
Hibernate: create table table_ab (id_a bigint not null, id_b bigint not null, primary key (id_a, id_b)) engine=InnoDB
Hibernate: create table table_b (id bigint not null auto_increment, name varchar(255) not null, primary key (id)) engine=InnoDB
Hibernate: create table table_c (id bigint not null auto_increment, name varchar(255) not null, id_a bigint not null, id_b bigint not null, primary key (id)) engine=InnoDB
Hibernate: create table table_d (id bigint not null auto_increment, name varchar(255) not null, primary key (id)) engine=InnoDB
Hibernate: create table table_da (ida bigint not null, id_d bigint not null, id_a bigint not null, primary key (ida, id_d)) engine=InnoDB
Hibernate: alter table table_ab add constraint UK_djv0nep3kck06gw36xd4tlt3m unique (id_a)
Hibernate: alter table table_c add constraint UKsiky7nbvaossuaai9jjpaxv7v unique (id_a, id_b)
Hibernate: alter table table_ab add constraint FKhr6nmhfq7iugl09cpvk70k33e foreign key (id_a) references table_a (id) on delete cascade
Hibernate: alter table table_ab add constraint FKcp354skj76oh5vw26lyrws45c foreign key (id_b) references table_b (id) on delete cascade
Hibernate: alter table table_c add constraint FKg9vjngl4y25kl2ij6apaedqk2 foreign key (id_a, id_b) references table_ab (id_a, id_b)
Hibernate: alter table table_da add constraint FK52svk9pn8pqcoywlwsjflphkg foreign key (id_a) references table_ab (id_a)
Hibernate: alter table table_da add constraint FK78v3oxk0bagsws5hiv0x38w4c foreign key (id_d) references table_d (id)

The problem is

Hibernate: alter table table_ab add constraint UK_djv0nep3kck06gw36xd4tlt3m unique (id_a)

HIbernate is creating an unique column on Ab Table, so I won’t be able to add something like this:
(1,1) and (1,2), because id_a column is unique and I’ll be getting an exception. How can I fix this issue? the behavior changes to the desired one when I delete the table_da class… see this:

Hibernate: create table table_a (id bigint not null auto_increment, name varchar(255) not null, primary key (id)) engine=InnoDB
Hibernate: create table table_ab (id_a bigint not null, id_b bigint not null, primary key (id_a, id_b)) engine=InnoDB
Hibernate: create table table_b (id bigint not null auto_increment, name varchar(255) not null, primary key (id)) engine=InnoDB
Hibernate: create table table_c (id bigint not null auto_increment, name varchar(255) not null, id_a bigint not null, id_b bigint not null, primary key (id)) engine=InnoDB
Hibernate: create table table_d (id bigint not null auto_increment, name varchar(255) not null, primary key (id)) engine=InnoDB
Hibernate: alter table table_c add constraint UKsiky7nbvaossuaai9jjpaxv7v unique (id_a, id_b)
Hibernate: alter table table_ab add constraint FKhr6nmhfq7iugl09cpvk70k33e foreign key (id_a) references table_a (id) on delete cascade
Hibernate: alter table table_ab add constraint FKcp354skj76oh5vw26lyrws45c foreign key (id_b) references table_b (id) on delete cascade
Hibernate: alter table table_c add constraint FKg9vjngl4y25kl2ij6apaedqk2 foreign key (id_a, id_b) references table_ab (id_a, id_b)

As you can see everything is great and is working as expected. I’ve tried many options, but looks like is not possible to get this working using the code posted previously.

beikov · November 11, 2020, 2:11pm

You have a few ManyToOne mappings for TableAb but none of them properly refer to the primary key, which is (id_a, id_b). You need a mapping like this:

    @ManyToOne(optional = false)
    @JoinColumns({
        @JoinColumn(name = "id_a", referencedColumnName = "id_a"),
        @JoinColumn(name = "id_b", referencedColumnName = "id_b")
    })    
    private TableAb tableAb;

If you can’t do that you are out of luck, a foreign key can only refer to a unique key.

Nitros64 · November 11, 2020, 6:32pm

I just want the column id_a on my Table_Da, the id_a column is on table_Ab, which comes from table_a originally… Well, I “fixed” the problem (I didn’t) doing this:

@Entity
@Table(name = "table_ab")
public class TableAb implements Serializable {
    private static final long serialVersionUID = 1L;
    
    @EmbeddedId
    private TableAbPK tableAbPK;
        
    @ManyToOne(fetch = FetchType.EAGER, optional = false)
    @MapsId("idA")
    @JoinColumn(name = "id_a",unique = false,insertable = false, updatable = false)
    @OnDelete(action = OnDeleteAction.CASCADE) 
    private TableA tableA;
    
    @ManyToOne(fetch = FetchType.EAGER, optional = false)
    @MapsId("idB")
    @JoinColumn(name = "id_b",unique = false,insertable = false, updatable = false)    
    @OnDelete(action = OnDeleteAction.CASCADE)
    private TableB tableB;
}
@Entity
@Table(name = "table_da")
public class TableDa implements Serializable {
    private static final long serialVersionUID = 1L;    
    @EmbeddedId
    private TableDaPK tableDaPK;

    @ManyToOne(optional = false)
    @MapsId("idA")
    @JoinColumn(name = "id_a",unique = false,insertable = false, updatable = false)       
    private TableA tableA;    
    
    @ManyToOne(optional = false)    
    @MapsId("idD")
    @JoinColumn(name = "id_d",unique = false,insertable = false, updatable = false)
    private TableD tableD;
}
@Entity
@Table(name = "table_c")
public class TableC implements Serializable {
    private static final long serialVersionUID = 1L;
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "id")
    private Long id;
    
    @Basic(optional = false)
    @Column(name = "name")
    private String name;
    
    @JoinColumns({
        @JoinColumn(name = "id_a", referencedColumnName = "id_a",insertable = false, updatable = false, unique=false), //,insertable = false, updatable = false, unique=false
        @JoinColumn(name = "id_b", referencedColumnName = "id_b",insertable = false, updatable = false, unique=false)
    })
    @ManyToOne(optional = false)
    private TableAb ab;
    
    @JoinColumns({
       @JoinColumn(name = "id_a", referencedColumnName="id_a", insertable = false, updatable = false), //a_id
       @JoinColumn(name = "id_d" , referencedColumnName="id_d", insertable = false, updatable = false) //b_id
    })    
    @ManyToOne(optional = false)
    private TableDa idD;
}

The Table_c was in the project from the beginning, I didn’t post it because it wasn’t necessary. As you can see Column id_a is on table_Ab and on table_Da, the two columns merged magically on Table_c. So, every time that I make an insert, the id_a on table_ab and the id_a on table_da have to be the same, so I reached my goal. It was hard, you know I think hibernate have its limitations. Looks like is not possible to have a foreing key from another foreing key. On MySql this is posible, but on Hibernate is not.

My conclusion is, this isn’t possible and I have to find other ways to do this. I found a way, maybe there are few others options. Right now I’m out of time, so I can’t do further researchment.

beikov · November 11, 2020, 6:50pm

I don’t know how your table mappings look like and with all the id_a, id_b stuff it’s hard to figure out what you want to do. Hibernate is pretty flexible and you should be able to model almost any relational structure. I just didn’t understand yet what the issue is exactly or what you want to achieve.

Nitros64 · November 11, 2020, 8:25pm

I’ve posted every table, even the table_c. I just wanna have in the table_c the foreigns keys from table_ab (id_a and id_b). On the other hand, in the table_da I wanna have ONLY the id_a foreign key from the table_ab. and of course, the foreign key from TableD, TableDa = TableAb + TableD .
Also, in the table_c I wanna have the foreign key id_d from table_da.

Table_c should looks like this: id , name, id_a , id_b, id_d
Table_Ab = id_a, id_b
Table_Da = id_a, id_d

Note: Strong ones or black ones, are the attribute that I want in Table_c…

The id_a and id_b are in the Tabla_ab, the id_d is in Table_da, BUT id_d have a relationship with id_a which is also in tableAb… The mapping written in the class is very clear I think.

What I did…? welll… I re-wrote the class table_da. This time I transformed the property TableAb tableAb to TableA tableA. I wanted to have the id_a from TableAb, not from TableA, but as shown before, Hibernate set the id_a from Table_Ab to unique = true,and I dont want that. I explained this early in this post.

Nitros64 · November 11, 2020, 8:26pm

this is BAD

Hibernate: alter table table_ab add constraint UK_djv0nep3kck06gw36xd4tlt3m unique (id_a)

beikov · November 11, 2020, 9:42pm

The problem is all this ab and da naming is pretty generic and makes it hard to understand your intent, but let me try again.

Why can’t you use the following mapping which is 1:1 what you have shown me so far.

@Entity
@Table(name = "table_a")
public class TableA implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "id")
    private Long id;
    @Basic(optional = false)
    @Column(name = "name")
    private String name;
}
@Entity
@Table(name = "table_b")
public class TableB implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "id")
    private Long id;
    @Basic(optional = false)
    @Column(name = "name")
    private String name;
}
public class TableAb implements Serializable {
    private static final long serialVersionUID = 1L;    
    @EmbeddedId
    private TableAbPK tableAbPK;
        
    @ManyToOne(fetch = FetchType.EAGER, optional = false)
    @JoinColumn(name = "id_a", insertable = false, updatable = false)
    @OnDelete(action = OnDeleteAction.CASCADE) 
    private TableA tableA;
    
    @ManyToOne(fetch = FetchType.EAGER, optional = false)
    @JoinColumn(name = "id_b", insertable = false, updatable = false)    
    @OnDelete(action = OnDeleteAction.CASCADE)
    private TableB tableB;
}
@Embeddable
public class TableAbPK implements Serializable {
    @Column(name = "id_a", nullable = false)
    private long idA;    
    @Column(name = "id_b", nullable = false)
    private long idB;
}
@Entity
@Table(name = "table_da")
public class TableDa implements Serializable {
    private static final long serialVersionUID = 1L;    
    @EmbeddedId
    private TableDaPK tableDaPK;

    @ManyToOne(optional = false)
	@JoinColumn(name = "id_a")
    private TableA tableAb;
    
    @ManyToOne(optional = false)
    @JoinColumn(name = "id_d")
    private TableD tableD;
}
@Embeddable
public class TableDaPK implements Serializable {
    @Column(name = "id_a", nullable = false)
    private long idA;    
    @Column(name = "id_d", nullable = false)
    private long idD;
}
@Entity
@Table(name = "table_d")
public class TableD implements Serializable {
    private static final long serialVersionUID = 1L;    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "id")
    private Long id;
    
    @Basic(optional = false)
    @Column(name = "name")
    private String name;
}

Nitros64 · November 11, 2020, 9:58pm

THat is the new version, the old version is different and it’s in the first post.

@Entity
@Table(name = "table_da")
public class TableDa implements Serializable {
    private static final long serialVersionUID = 1L;    
    @EmbeddedId
    private TableDaPK tableDaPK;

    @ManyToOne(optional = false)
    @JoinColumns({
        @JoinColumn(name = "id_a", referencedColumnName = "id_a") //,insertable = false, updatable = false, unique=false
        //@JoinColumn(name = "id_b", referencedColumnName = "id_b")
    })    
    private TableAb tableAb;    //OLD VERSION
    
    @ManyToOne(optional = false)    
    @MapsId("idD")
    @JoinColumn(name = "id_d")
    private TableD tableD;
}

This version of Table_da is forcing Hibernate to set unique = true in the Table_Ab

Hibernate: alter table table_ab add constraint UK_djv0nep3kck06gw36xd4tlt3m unique (id_a)

I’m using a new version of the table_da

@Entity
@Table(name = "table_da")
public class TableDa implements Serializable {

    private static final long serialVersionUID = 1L;
    
    @EmbeddedId
    private TableDaPK tableDaPK;

    @ManyToOne(optional = false)
    @MapsId("idA")
    @JoinColumn(name = "id_a",unique = false,insertable = false, updatable = false)       
    private TableA tableA;    //NEW VERSION
    
    @ManyToOne(optional = false)    
    @MapsId("idD")
    @JoinColumn(name = "id_d",unique = false,insertable = false, updatable = false)
    private TableD tableD;
}

This doesn’t fix the problem about Hibernate creating a unique key = true, it just avoids the problem.

Nitros64 · November 11, 2020, 10:05pm

So… I can’t put a foreign key in Table_da from Table_Ab (only id_a), because Hibernate is seting the id_a column from Table_Ab to unique = true automatecally. Making impossible to insert (1,1) and (1,2)

TableAb
id_a | id_b
1 | 1
1 | 2 ----> ERROR

Hibernate: alter table table_ab add constraint UK_djv0nep3kck06gw36xd4tlt3m unique (id_a)

It’s not possible from Hibernate. In mysql is perfectly possible. Too Bad

beikov · November 12, 2020, 7:42am

Why do you need TableAb directly in TableDa? If you change TableA to this:

@Entity
@Table(name = "table_a")
public class TableA implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "id")
    private Long id;
    @Basic(optional = false)
    @Column(name = "name")
    private String name;
    @OneToMany(mappedBy = "tableA")
    Set<TableAb> tableAbs;
}

you can access the TableBs as well, but through the proper associations. A @ManyToOne essentially is like a FK and a FK requires that the target side is unique, that’s required by any sane DBMS AFAIK.

You could try to map TableAbs as @OneToMany but I guess Hibernate then rightfully forces a unique constraint on table_da(id_a) which you probably don’t want.

@Entity
@Table(name = "table_da")
public class TableDa implements Serializable {
    private static final long serialVersionUID = 1L;    
    @EmbeddedId
    private TableDaPK tableDaPK;

    @OneToMany
    @JoinColumn(name = "id_a", referencedColumn = "id_a")
    private Set<TableAbs> tableAbs;
    
    @ManyToOne(optional = false)
    @JoinColumn(name = "id_d")
    private TableD tableD;
}

The problem here is that JPA/Hibernate forces a bidirectional mapping so that it can keep state in sync. What you want here is not safe. One id_a might refer to multiple rows in table_ab so you can’t map this as @ManyToOne.

Nitros64 · December 3, 2020, 3:03am

I used Entitymanager to solve my problem.

Edual_Dan_Sarmiento · November 5, 2021, 1:23am

No se si podrias pasar la solucion mi buen amigo.

Gachoud_Philippe · September 5, 2022, 2:52pm

How did you solve that? am in a similar problem I cant modify the entity though…

Topic		Replies	Views
Avoid having constraint created by hibernate without having access to the entity Hibernate ORM	1	296	September 6, 2022
How to map a column to a JPA property if it was already mapped in an @ManyToOne association when using Hibernate Hibernate ORM	5	45682	April 2, 2021
Making subclasses have transient fields Hibernate ORM	4	802	June 2, 2021
Hibernate Revenge Ignores column exclude="true" Hibernate Tools	0	1061	March 26, 2019
Auto incrementation not working for @Id tagged field Hibernate ORM	19	24872	July 24, 2018

How to prevent Hibernate to making a column with unique = true automatically

Related Topics