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.