Hibernate's "DELETE_ORPHAN" and a DataIntegrityViolationException

Suppose you have DELETE_ORPHAN set on a parent child relationship like this:

class Parent {
    private List<Child> children = new ArrayList<Child>();

    @OneToMany(cascade = CascadeType.ALL)
    @Cascade({org.hibernate.annotations.CascadeType.DELETE_ORPHAN})
    @JoinColumn(name = "ID", nullable = false)
    @IndexColumn(name = "INDEX")
    public List<BulkAmendmentCashflow> getBulkAmendmentCashflows() {
        return bulkAmendmentCashflows;
    }
    ...
}

If your friendly Database Administrator (DBA) has set a unique database constraint on the "ID" and "INDEX" columns of your Oracle database, then under certain conditions you will get this sort of error:

org.springframework.dao.DataIntegrityViolationException:
...
Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint (DB_NAME.UNIQUE_CONSTRAINT_NAME) violated

It usually occurs when you are both adding a new child and deleting an old child in the same transaction.

I haven't fully investigated the cause of this in detail, but it seems likely to me that Hibernate is adding the new row before it deletes the old row. The new row gets the same INDEX as the row being deleted, so that the INDEX is kept in numeric order. Even though the transaction hasn't been completed, Oracle registers this as a violation of the constraint.

There is an easy fix which keeps everyone happy (and the DBA can still keep their precious constraint!).

The DBA needs to enable deferred constraints in Oracle on the constraint like this:

alter table TABLE_NAME drop constraint CONSTRAINT_NAME;
alter table TABLE_NAME add constraint CONSTRAINT_NAME UNIQUE (ID, INDEX) DEFERRABLE INITIALLY DEFERRED;