How to swap value pairs restricted by unique index

Question

We have a Hibernate-based (33.) web application and are finding an issue related to swapping datavalue pairs restricted by a unique index. In our flightticket HBM we have

<many-to-one name="participants" class="net.umbrella.entity.ParticipantModel" fetch="select">
    <column name="PARTICIPANTID" not-null="false" />
</many-to-one>
<many-to-one name="flights" class="net.umbrella.entity.FlightModel" fetch="select">
    <column name="FLIGHTID" not-null="true" />
</many-to-one>

It is functionally possible to take a data pair with ticket A assigned to participant P1 plus ticket B assigned to participant P2 and swap the two participants. Ticket A would end up assigned to participant P2, ticket B would be assigned to participant P1. However, FLIGHTID + PARTICIPANTID have a unique constraint.

When Hibernate issues the first update to change participant P1 to P2 on ticket A, a GenericJDBCException is thrown

Attempt to insert duplicate key row in object 'FLIGHTTICKET' with unique index 'IDX_FLIGHTTICKET_UQ'

Does anyone have a common solution to this?

Thanks Simon

Answer 1

So, you have something like this:

Participant temp = pair1.getParticipant();
pair1.setParticipant(pair2.getParticipant());
pair2.setParticipant(temp);

Instead, just do the following:

Participant temp1 = pair1.getParticipant();
Participant temp2 = pair2.getParticipant();
pair2.setParticipant(null);
session.flush();
pair1.setParticipant(temp2);
pair2.setParticipant(temp1);

The intermediate flush will make sure only one pair at a time references a participant.