Constraint violation in Hibernate unidirectional OneToMany mapping with JoinTable and OrderColumn when removing elements

Question

I have a problem when removing elements from a list mapped as described above. Here is the mapping:

@Entity
@Table( name = "foo")
class Foo {

    private List bars;

    @OneToMany
    @OrderColumn( name = "order_index" )
    @JoinTable( name = "foo_bar_map", joinColumns = @JoinColumn( name = "foo_id" ), inverseJoinColumns =  @JoinColumn( name = "bar_id" ) )
    @Fetch( FetchMode.SUBSELECT )
    public List getBars() {
        return bars;
    }
}

Inserting Bar-instances and saving the Foo works fine, but when I remove an element from the list and save again, the unique constraint on bar_id in the mapping table is violated. The following SQL-statements are issued by hibernate, and these look quite odd:

LOG:  execute : delete from foo_bar_map where foo_id=$1 and order_index=$2
DETAIL:  parameters: $1 = '4', $2 = '6'
LOG:  execute S_5: update foo_bar_map set bar_id=$1 where foo_id=$2 and order_index=$3
DETAIL:  parameters: $1 = '88', $2 = '4', $3 = '0'
ERROR:  duplicate key value violates unique constraint "foo_bar_map_bar_id_key"

The error perfectly makes sense, given the statements generated by Hibernate (there are five items in the list, I remove the first one and Hibernate deletes the mapping row with the LAST index and the tries to updates the remaining ones, starting with the first).

What is wrong with the mapping above?

score 18 · Accepted Answer · edited Aug 23 '16 at 12:16

Your mapping is totally valid and works with EclipseLink as JPA 2.0 implementation (without the Fetch annotation of course), but indeed fails with Hibernate.

Here is the DDL with Hibernate:

create table foo_bar_map (foo_id bigint not null, bar_id bigint not null, order_index integer not null, primary key (foo_id, order_index), unique (bar_id))
alter table foo_bar_map add constraint FK14F1CB7FA042E82 foreign key (bar_id) references Bar4022509
alter table foo_bar_map add constraint FK14F1CB7B6DBCCDC foreign key (foo_id) references Foo4022509

So let's say Foo#1 holds a list with Bar#1, Bar#2, Bar#3, the join table contains:

foo_id | bar_id | order_index
     1 |      1 |           1
     1 |      2 |           2
     1 |      3 |           3

When removing, say the first item from the list, Hibernate first delete the last row (WTF?) from the join table:

foo_id | bar_id | order_index
     1 |      1 |           1
     1 |      2 |           2

And then tries to update the bar_id column in the join table instead of the order_index (WTF!?) to reflect the "new" ordering of the items in the list. First (schematically):

foo_id | bar_id | order_index
     1 |      2 |           1
     1 |      2 |           2

where the next step would result in:

foo_id | bar_id | order_index
     1 |      2 |           1
     1 |      3 |           2

Obviously, this approach doesn't sound right and doesn't work because of the unique constraint on bar_id. More generally, why the hell does Hibernate mess with the bar_id instead of updating the order_index column?

I consider this to be an Hibernate bug (reported as ~~HHH-5694~~, see HHH-1268 now).

Thanks, yeah, that is what I also found out after quite some time spent in the debugger. As a fix I'm now using ManyToMany (which drops the uniqueness constraint), but it feels ugly. Interestingly enough, that nobody else has come across this issue, as I'd consider this a common use case ... — tbk, Oct 29 '10 at 19:29
@tbh Yes, making the association a many-to-many will drop the unique constraint (to allow the "many"). But this is indeed a workaround, Hibernate should handle a one-to-many properly. You might want to vote for the Jira issue :) — Pascal Thivent, Oct 29 '10 at 21:14

score 1 · Answer 2 · answered Oct 26 '10 at 09:58

1

Usually when joining through a join table the relationship is ManyToMany not OneToMany. Try this

@ManyToMany
@OrderColumn( name = "order_index" )
@JoinTable( name = "foo_bar_map", joinColumns = @JoinColumn( name = "foo_id" ), inverseJoinColumns =  @JoinColumn( name = "bar_id" ) )
@Fetch( FetchMode.SUBSELECT )
public List getBars() {
    return bars;
}

answered Oct 26 '10 at 09:58

mR_fr0g

8,462
7
39
54

But from my domain model perspective, it just is OneToMany. Replacing this with ManyToMany would leverage some constraints, which clearly is not what I want. I want the schema to be as safe as possible. The main reason to use JoinTable here is to keep track of the order index, which should not be known in the Bar class. – tbk Oct 26 '10 at 10:57
With standard JPA, a unidirectional OneToMany is mapped using a join table (although Hibernate allows to map this without a join table using a `JoinColumn`, which is now also supported in JPA 2.0). – Pascal Thivent Oct 26 '10 at 19:51

score 0 · Answer 3 · answered Oct 26 '10 at 10:02

0

I guess what you need is correct inverse mapping.
http://docs.jboss.org/hibernate/core/3.3/reference/en/html/tutorial.html#tutorial-associations-bidirectional

answered Oct 26 '10 at 10:02

ch4nd4n

4,110
2
21
43

2

But I want this association to be unidirectional. The Bar class should not know anything about the Foo's referencing it. – tbk Oct 26 '10 at 10:58

score 0 · Answer 4 · answered Sep 08 '11 at 13:42

No I don't think it's a hibernate bug, and as you will see if you do searches this hibernate bug quoted by Pascal Thivent is a bug known since 2006 and has never been solved.

Why ?

Cause I think the problem is just in the constraints on the table and not in hibernate.

I don't understand why there is a unique constraint on bar_id

Using an order index means your collection is a List (and not a Set !). And a List is a collection where you can specify index to elements you add (it corresponds to OrderColumn).
The difference between a List and a Set is that you and can the same data twice (or more), but the same data will be at different indexes. Then you can have the same bar_id a different indexes, you don't have to specify a unique constraint on bar_id. And the primary key can't be (foo_id, order_index) cause the pattern List authorize the same data at different indexes. Maybe your PK should be (foo_id, bar_id, order_index) ?

I think the problem is in this way :)

Ever heard of an ordered set? The two constraints are orthogonal: A defined order and uniqueness. — tbk, Jun 19 '12 at 13:37
true it can be done by SortedSet http://docs.jboss.org/hibernate/orm/4.1/manual/en-US/html_single/#collections-sorted — Nico, Mar 27 '18 at 13:52

Constraint violation in Hibernate unidirectional OneToMany mapping with JoinTable and OrderColumn when removing elements

4 Answers4

Linked