Background: http://jeffkemponoracle.com/2011/03/11/handling-unique-constraint-violations-by-hibernate
Our table is:
BOND_PAYMENTS (BOND_PAYMENT_ID, BOND_NUMBER, PAYMENT_ID)
There is a Primary key constraint on BOND_PAYMENT_ID, and a Unique constraint on (BOND_NUMBER, PAYMENT_ID).
The application uses Hibernate, and allows a user to view all the Payments linked to a particular Bond; and it allows them to create new links, and delete existing links. Once they’ve made all their desired changes on the page, they hit “Save”, and Hibernate does its magic to run the required SQL on the database. Apparently, Hibernate works out which records need to be deleted, which need to be inserted, and leaves the rest untouched. Unfortunately, it does the INSERTs first, then it does the DELETEs.
If the user deletes a link to a payment, then changes their mind and re-inserts a link to the same payment, Hibernate quite happily tries to insert it then delete it. Since these inserts/deletes are running as separate SQL statements, Oracle validates the constraint immediately on the first insert and issues ORA-00001 unique constraint violated.
We know of only two options:
Option 2 is not very palatable, because the constraint provides excellent protection from nasty application bugs that might allow inconsistent data to be saved. We went with option 1.
ALTER TABLE bond_payments ADD
CONSTRAINT bond_payment_uk UNIQUE (bond_number, payment_id)
DEFERRABLE INITIALLY DEFERRED;
The downside is that the index created to police this constraint is now a non-unique index, so may be somewhat less efficient for queries. We have decided this is not as great a detriment for this particular case. Another downside (advised by Gary) is that it may suffer from a particular Oracle bug - although I believe we will be immune (at least, mostly) due to the way the application works.
Are there any other options we should consider?
From the problem you described, it's not clear if you have an entity BondPayment or if you have a Bond linked directly to a Payment. For now, I suppose you have the link between Payment and Bond through BondPayment. In this case, Hibernate is doing the right thing, and you'll need to add some logic in your app to retrieve the link and remove it (or change it). Something like this:
bond.getBondPayment().setPayment(newPayment);
You are probably doing something like this:
BondPayment bondPayment = new BondPayment();
bondPayment.setPayment(newPayment);
bondPayment.setBond(bond);
bond.setBondPayment(bondPayment);
In the first case, the BondPayment.id is kept, and you are just changing the payment for it. In the second case, it's a brand new BondPayment, and it will conflict with an existing record in the database.
I said that Hibernate is doing the right thing because it threats BondPayment as a "regular" entity, whose lifecycle is defined by your app. It's the same as having a User with a unique constraint on login, and you are trying to insert a second record with a duplicate login. Hibernate will accept (it doesn't knows if the login exists in the database) and your database will refuse.