Unique Constraint Violation on Hibernate @ManyToMany Relationship

Question

I have 3 entities in my database, let's call them A, B, and C.

A and B share a many-to-many relationship with one another. A has a SortedSet of Bs, but B does not reference A (no collection or w/e configured). So we have the following.

// Inside class A
@ManyToMany
@JoinTable(name = "a_b", 
           joinColumns = {@JoinColumn(name = "a_id")}, 
           inverseJoinColumns = {@JoinColumn(name = "b_id")})
@LazyCollection(LazyCollectionOption.FALSE)
@SortNatural
private SortedSet<B> bSet = new TreeSet<B>();

B and C have a one-to-many relationship with one another (1 B to many Cs). C has a B in it's entity, but B does not have a reference to it's many C entities. So we have the following

// Inside class C
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "b_id", nullable = false, updatable = true)
protected B b;

We have a sync process that runs a nightly job to update the A entities and their associations to B entities (which doesn't change very often). We end up having something similar to the following (really, it's a lot more complicated with DAOs, services, etc).

// Get the A value to be updated
A aToUpdate = entityManager.find(A.class, idForA); 

// Out of scope of the question, but we need to figure out B via a string field on C
C cValue = myDao.getByProperty("fieldName", fieldValue);

// Determine the B values to set on aToUpdate
B bToSetOnA = cValue.getB();
TreeSet<B> bSet = new TreeSet<>();
bSet.add(bToSetOnA);

// Update aToUpdate
aToUpdate.setBSet(bSet);
aToUpdate = entityManager.merge(aToUpdate);
entityManager.flush();

When this happens, the following error occurs.

ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (Sync Thread)
Duplicate entry 'myAId-almostMyBId' for key 'uk_a_b'

One interesting thing is that the almostMyBId is 1 character short of the actual B ID. But only the full ID appears in the a_b table.

When I look through the code base, there is a uk_a_b constraint on an index on the a_b table. This is from liquibase.

<createIndex indexName="uk_a_b" tableName="a_b" unique="true">
    <column name="a_id"/>
    <column name="b_id"/>
</createIndex>

If I remove the aToUpdate.setBSet(bSet); line, the error disappears.

I added logging and confirmed that the IDs of the new bSet are the same as the old ones on aToUpdate.

Somehow, Hibernate seems to be trying to re-add the association, even though we're doing a merge and the associations haven't really changed.

I've tried changing a few CascadeType and FetchType things here and there, but the error doesn't seem to go away. Anyone have an idea of what's going on?

score 0 · Accepted Answer · answered Feb 17 '20 at 21:50

This is very specific to my situation, but figured I'd post the answer anyways in case anyone read my question and has been working on it.

Another developer on my team was tasked with speeding up a report based on the a and a_b tables. To avoid needing to do a JOIN and a WHERE clause, the developer copied the data over from a_b into a new table (using the WHERE clause) and added triggers so that whenever things were updated, it would insert into the new table. This new table had a constraint named the same as the a_b table (namely, uk_a_b). The duplicate case was not properly handled, so an error was being thrown. Due to the name similarities, it seemed to be the a_b table causing the issue when it was really the new table. Fun times.

Unique Constraint Violation on Hibernate @ManyToMany Relationship

1 Answers1