title: SqlExceptionHelper - Duplicate entry for Unique key Using Spring Data JPA
body: <p>Here is the situation:</p>
  
  <ol>
  <li>I have 3 tables - Dummy, A and B - where A and B have One-To-Many
  relationship, and Dummy is stand alone </li>
  <li>These tables have corresponding JPA entities in the data layer. I am
  using Repository design pattern, so accessing these entities via their corresponding
  service implementations.</li>
  <li>I am making exact sequence of calls to these entities:</li>
  <li>Get an entity for ID = xxx</li>
  <li>Display the entity ID and name (or whatever)</li>
  <li>Update a field using entity.setField(YYY)</li>
  <li>push it back to DB using: entityService.updateEntity(entity)</li>
  </ol>
  
  <p>The above sequence from #4 to #7 works like a charm for Dummy table. But fails to execute for the A and B. The exception is:</p>
  
  <pre><code>ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Duplicate entry &lt;The name here&gt; for key 'name_UNIQUE'
  Exception in thread "main" org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.ConstraintViolationException: could not execute statement; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement
  at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:321)
  at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:403)
  at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:58)
  at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
  </code></pre>
  
  <p></p>
  
  <p>The field that I am updating is not unique. The entities have exactly same structure with some additional columns here and there. </p>
  
  <p>Here is the code for Dummy Entity:</p>
  
  <pre><code>    DummyEntity dummyEntity = dummyService.findDummyEntity(16L);
      System.out.println("&gt;&gt;&gt; Name is: " + dummyEntity.getName() + " with ID: " + dummyEntity.getId());
      dummyEntity.setName("New Name");
      dummyEntity.setRank(3333333);
      dummyService.updateDummyEntity(dummyEntity);
  </code></pre>
  
  <p>Repeating the exact same steps for the remaining entities A and B. </p>
  
  <p>So what am I doing wrong? Any pointers will be greatly appreciated. </p>
  
  <h1>UPDATE:</h1>
  
  <p>@erencan - yes, I double checked that. Here is what I observed after posing the question here. The Table A and B (the troublesome ones) has this issue: </p>
  
  <ul>
  <li>When I ask repository service to return me an instance for a given ID for Table A and B, it returns them alright </li>
  <li>when a change is made to that instance using the setXXX(), and updateEntity() or saveEntity() is called (as shown using the demo entity code above), the save/update inserts a new entity in the table with exact attribute values as the old one, but with the new changes incorporated (this was observed by removing the unique key constraint on the Table A and B). </li>
  <li>Later, when I query these newly created entities using their ID in the JPA/Java code, and perform the exact same steps (change some attribute and call save/update on the repository), these newly created entities (rows in the db table) get updated in exactly the manner expected. </li>
  <li>So it seems that the original entity (row) is somehow 'locked' and updates are prevented. Therefore, the JPA save/update calls simply tries to create a fresh one instead; and since the fresh entity still has the same set of attribute values, any UNIQUE key constraint will start complaining (of course) </li>
  <li>I did some tests on the existing tables (ETL'd into the DB), and find this behavior consistent: If the entity is NOT created by JPA, then JPA can read the data alright, but cannot update them; if the entity IS created by JPA, then JPA can read AND update them alright.</li>
  </ul>
  
  <p>Not sure how this happens though (yet). Here is the schema of Table A and B:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `mydbschema`.`table-B` (
    `id` INT NOT NULL AUTO_INCREMENT COMMENT 'This is PK',
    `name` VARCHAR(100) NULL,
    `city` VARCHAR(50) NOT NULL,
    `state` VARCHAR(30) NOT NULL,
    `zip` VARCHAR(5) NOT NULL,
    `country` VARCHAR(50) NOT NULL,
    `overall_rank` INT NULL,
    `inserted` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `insert_src_ver_id` INT NULL,
    `updated` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
    `update_src_ver_id` INT NULL,
    `version` INT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `name_UNIQUE` (`name` ASC))
  ENGINE = InnoDB;
  </code></pre>
  
  <p>Another one:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `mydbschema`.`table-A` (
    `id` INT NOT NULL AUTO_INCREMENT COMMENT 'This is PK',
    `full_name` VARCHAR(200) NULL,
    `gender` VARCHAR(1) NULL,
    `year_of_birth` VARCHAR(4) NULL,
    `title_code` VARCHAR(6) NULL,
    `business_role` VARCHAR(30) NULL,
    `graduation_year` VARCHAR(4) NULL,
    `residency` VARCHAR(500) NULL,
    `table-B_id` INT NULL,
    `npi_num` VARCHAR(10) NULL,
    `upin` VARCHAR(20) NULL,
    `dea_num` VARCHAR(20) NULL,
    `dea_expire_date` VARCHAR(10) NULL,
    `year_started_practicing` VARCHAR(4) NULL,
    `high_prescriber` VARCHAR(1) NULL,
    `board_action` VARCHAR(1) NULL,
    `mdi_qscore` INT NOT NULL DEFAULT 0,
    `mdi_cscore` INT NOT NULL DEFAULT 0,
    `aco_id` INT NULL,
    `npp` INT NULL,
    `medicaid_id` VARCHAR(50) NULL,
    `medicaid_state` VARCHAR(2) NULL,
    `medicare_id` VARCHAR(50) NULL,
    `medicare_state` VARCHAR(2) NULL,
    `medicare_provider_flag` VARCHAR(1) NULL,
    `inserted` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `insert_src_ver_id` INT NULL,
    `updated` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
    `update_src_ver_id` INT NULL,
    `version` INT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `hdsphy_id_UNIQUE` (`id` ASC),
    UNIQUE INDEX `npi_num_UNIQUE` (`npi_num` ASC),
    UNIQUE INDEX `dea_num_UNIQUE` (`dea_num` ASC),
    CONSTRAINT `fk_table-A_table-B`
      FOREIGN KEY (`table-B_id`)
      REFERENCES `mydbschema`.`table-B` (`id`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  ENGINE = InnoDB;
  </code></pre>
  
  <p>Here is the FULL Stack trace:</p>
  
  <pre><code>2013-09-30 10:20:49,705 [main] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Duplicate entry '1568673648' for key 'npi_num_UNIQUE'
  Exception in thread "main" org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.ConstraintViolationException: could not execute statement; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement
  at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:321)
  at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:403)
  at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:58)
  at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
  at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:163)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
  at org.springframework.data.jpa.repository.support.LockModeRepositoryPostProcessor$LockModePopulatingMethodIntercceptor.invoke(LockModeRepositoryPostProcessor.java:84)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
  at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:91)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
  at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
  at com.sun.proxy.$Proxy60.save(Unknown Source)
  at com.mdinsider.platform.domain.PhysicianServiceImpl_Roo_Service.ajc$interMethod$com_mdinsider_platform_domain_PhysicianServiceImpl_Roo_Service$com_mdinsider_platform_domain_PhysicianServiceImpl$updatePhysician(PhysicianServiceImpl_Roo_Service.aj:48)
  at com.mdinsider.platform.domain.PhysicianServiceImpl.updatePhysician(PhysicianServiceImpl.java:1)
  at com.mdinsider.platform.domain.PhysicianService_Roo_Service.ajc$interMethodDispatch1$com_mdinsider_platform_domain_PhysicianService_Roo_Service$com_mdinsider_platform_domain_PhysicianService$updatePhysician(PhysicianService_Roo_Service.aj)
  at com.mdinsider.platform.mediblip.engine.TestDBSave.saveMDIQualityScore(TestDBSave.java:94)
  at com.mdinsider.platform.mediblip.engine.TestDBSave.main(TestDBSave.java:142)
  Caused by: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement
  at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1387)
  at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1310)
  at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1316)
  at org.hibernate.ejb.AbstractEntityManagerImpl.merge(AbstractEntityManagerImpl.java:898)
  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  at java.lang.reflect.Method.invoke(Method.java:606)
  at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:241)
  at com.sun.proxy.$Proxy31.merge(Unknown Source)
  at org.springframework.data.jpa.repository.support.SimpleJpaRepository.save(SimpleJpaRepository.java:345)
  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  at java.lang.reflect.Method.invoke(Method.java:606)
  at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.executeMethodOn(RepositoryFactorySupport.java:334)
  at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:319)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
  at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
  at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
  at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
  at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:155)
  ... 12 more
  Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
  at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:74)
  at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
  at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
  at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
  at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:136)
  at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:96)
  at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:58)
  at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2975)
  at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3487)
  at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:81)
  at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:377)
  at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:214)
  at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:194)
  at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:178)
  at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:321)
  at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:286)
  at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:192)
  at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:125)
  at org.hibernate.ejb.event.EJB3MergeEventListener.saveWithGeneratedId(EJB3MergeEventListener.java:71)
  at org.hibernate.event.internal.DefaultMergeEventListener.saveTransientEntity(DefaultMergeEventListener.java:236)
  at org.hibernate.event.internal.DefaultMergeEventListener.entityIsTransient(DefaultMergeEventListener.java:216)
  at org.hibernate.event.internal.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:154)
  at org.hibernate.event.internal.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:76)
  at org.hibernate.internal.SessionImpl.fireMerge(SessionImpl.java:914)
  at org.hibernate.internal.SessionImpl.merge(SessionImpl.java:898)
  at org.hibernate.internal.SessionImpl.merge(SessionImpl.java:902)
  at org.hibernate.ejb.AbstractEntityManagerImpl.merge(AbstractEntityManagerImpl.java:889)
  ... 31 more
  Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1568673648' for key 'npi_num_UNIQUE'
  at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
  at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
  at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
  at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
  at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
  at com.mysql.jdbc.Util.getInstance(Util.java:386)
  at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1039)
  at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
  at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
  at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
  at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
  at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
  at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
  at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2427)
  at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2345)
  at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2330)
  at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
  at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
  at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:133)
  </code></pre>