4 Replies Latest reply: Mar 26, 2012 4:38 PM by lingling788 RSS

@OneToMany audit entry -- Integrity constraint violation: Duplicate entry for primarykey

lingling788 Newbie

I have two entities: @AccountHolder and @Additionalparty, it's set as below in AccountHolder class:

@OneToMany (orphanRemoval=true, cascade={CascadeType.ALL})

    @JoinColumn(name="account_holder_id", referencedColumnName="id")

    @OrderColumn (name="ordinal")

    @AuditJoinTable (name="accountholder_additionalparty_aud")

    private List<AdditionalParty> additionalParties = new ArrayList<AdditionalParty>();

 

In my code, I retrieved one accountholder from db, it had two additionalParties,

and I used a CollectionUtils.filter to remove the 1st entry in the list, then merged accountholder to db.

 

Here is the result I got from audit join table:

mysql> select * from accountholder_additionalparty_aud where account_holder_id=1088737;

+---------+-------------------+-------+---------+---------+

| REV     | account_holder_id | id    | ordinal | REVTYPE |

+---------+-------------------+-------+---------+---------+

|  338443 |           1088737 | 14199 |       0 |       0 |                    -- This is the 1st entry to be removed.

|  338443 |           1088737 | 14201 |       1 |       0 |                    -- This 2nd entry will become the 1st later and ordinal column will be updated to 0.

| 1309244 |           1088737 | 14201 |       0 |       0 |                   -- Here is my issue. It deleted the 2nd entry and generated a new one using a same REV #. It used to give me duplicate primary key error

| 1309244 |           1088737 | 14201 |       1 |       2 |                     because the table had a composite primary key: REV, account_holder_id,id. It only worked after I removed the primary key.

| 1309244 |           1088737 | 14199 |       0 |       2 |                   -- This one has been successfully removed.

+---------+-------------------+-------+---------+---------+

 

 

-------------------

Actually, I was expecting it update the 2nd entry with ordinal = 0, then it should only generate one record with REVTYPE=1 which will never cause this issue.

Any help on this would be much appreciated.