title: "integrity constraint violation: unique constraint or index violation" in Spring HSQLDB
body: <p>I have a RESTful web service in Springboot with an end-point defined as: </p>
  
  <pre><code>@RequestMapping(
          value="/api/users",
          method=RequestMethod.POST,
          consumes=MediaType.APPLICATION_JSON_VALUE,
          produces=MediaType.APPLICATION_JSON_VALUE)
  public ResponseEntity&lt;User&gt; createUser(
          @Valid @RequestBody User user, BindingResult result) {
      if (result.hasErrors()) {
          return new ResponseEntity&lt;User&gt;(user, HttpStatus.BAD_REQUEST);
      }
      User savedUser = userService.create(user);
      return new ResponseEntity&lt;User&gt;(savedUser, HttpStatus.CREATED);
  
  }
  </code></pre>
  
  <p>and the User schema defined as:</p>
  
  <pre><code>    CREATE TABLE User (
      userId BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
      username VARCHAR(100) NOT NULL UNIQUE,
      password VARCHAR(100) NOT NULL,
      email VARCHAR(100) NOT NULL UNIQUE,
      mobile BIGINT NOT NULL UNIQUE,
      PRIMARY KEY (userId)
  );
  </code></pre>
  
  <p>I want to ensure that users with the same username/email or mobile phone do not get registered. In my Postman client, when I make a POST request with duplicate values, I get a 500 Internal Server Error and the following string of errors appears on my server-side:</p>
  
  <pre><code>    2015-08-11 18:27:10.454  WARN 4644 --- [nio-8080-exec-3] o.h.engine.jdbc.spi.Sql
  ExceptionHelper   : SQL Error: -104, SQLState: 23505
  2015-08-11 18:27:10.454 ERROR 4644 --- [nio-8080-exec-3] o.h.engine.jdbc.spi.Sql
  ExceptionHelper   : integrity constraint violation: unique constraint or index v
  iolation; SYS_CT_10094 table: USER
  2015-08-11 18:27:10.494 ERROR 4644 --- [nio-8080-exec-3] o.a.c.c.C.[.[.[/].[disp
  atcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context
   with path [] threw exception [Request processing failed; nested exception is or
  g.springframework.dao.DataIntegrityViolationException: could not execute stateme
  nt; SQL [n/a]; constraint [SYS_CT_10094]; nested exception is org.hibernate.exce
  ption.ConstraintViolationException: could not execute statement] with root cause
  
  
  org.hsqldb.HsqlException: integrity constraint violation: unique constraint or i
  ndex violation; SYS_CT_10094 table: USER
          at org.hsqldb.error.Error.error(Unknown Source)
          at org.hsqldb.Constraint.getException(Unknown Source)
          at org.hsqldb.index.IndexAVLMemory.insert(Unknown Source)
          at org.hsqldb.persist.RowStoreAVL.indexRow(Unknown Source)
          at org.hsqldb.TransactionManager2PL.addInsertAction(Unknown Source)
          at org.hsqldb.Session.addInsertAction(Unknown Source)
          at org.hsqldb.Table.insertSingleRow(Unknown Source)
          at org.hsqldb.StatementDML.insertRowSet(Unknown Source)
          at org.hsqldb.StatementInsert.getResult(Unknown Source)
          at org.hsqldb.StatementDMQL.execute(Unknown Source)
          at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
          at org.hsqldb.Session.execute(Unknown Source)
          at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
          at org.hsqldb.jdbc.JDBCPreparedStatement.executeUpdate(Unknown Source)
          at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(
  ResultSetReturnImpl.java:208)
          at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAn
  dExtract(IdentityGenerator.java:96)
          at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(Abstr
  actReturningDelegate.java:58)
          at org.hibernate.persister.entity.AbstractEntityPersister.insert(Abstrac
  tEntityPersister.java:3032)
          at org.hibernate.persister.entity.AbstractEntityPersister.insert(Abstrac
  tEntityPersister.java:3558)
          at org.hibernate.action.internal.EntityIdentityInsertAction.execute(Enti
  tyIdentityInsertAction.java:98)
          at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:492)
          at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(Ac
  tionQueue.java:197)
          at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java
  :181)
          at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:216)
          at org.hibernate.event.internal.AbstractSaveEventListener.addInsertActio
  n(AbstractSaveEventListener.java:334)
          at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrR
  eplicate(AbstractSaveEventListener.java:289)
          at org.hibernate.event.internal.AbstractSaveEventListener.performSave(Ab
  stractSaveEventListener.java:195)
          at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGenera
  tedId(AbstractSaveEventListener.java:126)
          at org.hibernate.jpa.event.internal.core.JpaPersistEventListener.saveWit
  hGeneratedId(JpaPersistEventListener.java:84)
          at org.hibernate.event.internal.DefaultPersistEventListener.entityIsTran
  sient(DefaultPersistEventListener.java:206)
          at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(De
  faultPersistEventListener.java:149)
          at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(De
  faultPersistEventListener.java:75)
          at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:811)
          at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:784)
          at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:789)
          at org.hibernate.jpa.spi.AbstractEntityManagerImpl.persist(AbstractEntit
  yManagerImpl.java:1181)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
  java:62)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
  sorImpl.java:43)
          at java.lang.reflect.Method.invoke(Method.java:483)
          at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEnti
  tyManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:344)
          at com.sun.proxy.$Proxy76.persist(Unknown Source)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
  java:62)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
  sorImpl.java:43)
          at java.lang.reflect.Method.invoke(Method.java:483)
          at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityMa
  nagerInvocationHandler.invoke(SharedEntityManagerCreator.java:291)
          at com.sun.proxy.$Proxy76.persist(Unknown Source)
          at org.springframework.data.jpa.repository.support.SimpleJpaRepository.s
  ave(SimpleJpaRepository.java:433)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
  java:62)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
  sorImpl.java:43)
          at java.lang.reflect.Method.invoke(Method.java:483)
          at org.springframework.data.repository.core.support.RepositoryFactorySup
  port$QueryExecutorMethodInterceptor.executeMethodOn(RepositoryFactorySupport.jav
  a:436)
          at org.springframework.data.repository.core.support.RepositoryFactorySup
  port$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:421)
          at org.springframework.data.repository.core.support.RepositoryFactorySup
  port$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:393)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(
  ReflectiveMethodInvocation.java:179)
          at org.springframework.data.repository.core.support.RepositoryFactorySup
  port$DefaultMethodInvokingMethodInterceptor.invoke(RepositoryFactorySupport.java
  :506)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(
  ReflectiveMethodInvocation.java:179)
          at org.springframework.transaction.interceptor.TransactionInterceptor$1.
  proceedWithInvocation(TransactionInterceptor.java:99)
          at org.springframework.transaction.interceptor.TransactionAspectSupport.
  invokeWithinTransaction(TransactionAspectSupport.java:281)
          at org.springframework.transaction.interceptor.TransactionInterceptor.in
  voke(TransactionInterceptor.java:96)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(
  ReflectiveMethodInvocation.java:179)
          at org.springframework.dao.support.PersistenceExceptionTranslationInterc
  eptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(
  ReflectiveMethodInvocation.java:179)
          at org.springframework.data.jpa.repository.support.CrudMethodMetadataPos
  tProcessor$CrudMethodMetadataPopulatingMethodIntercceptor.invoke(CrudMethodMetad
  ataPostProcessor.java:122)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(
  ReflectiveMethodInvocation.java:179)
          at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invok
  e(ExposeInvocationInterceptor.java:92)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(
  ReflectiveMethodInvocation.java:179)
          at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynami
  cAopProxy.java:207)
          at com.sun.proxy.$Proxy81.save(Unknown Source)
          at favorite.ws.service.UserServiceBean.create(UserServiceBean.java:28)
          at favorite.ws.web.api.UserController.createUser(UserController.java:73)
  
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
  java:62)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
  sorImpl.java:43)
          at java.lang.reflect.Method.invoke(Method.java:483)
          at org.springframework.web.method.support.InvocableHandlerMethod.doInvok
  e(InvocableHandlerMethod.java:221)
          at org.springframework.web.method.support.InvocableHandlerMethod.invokeF
  orRequest(InvocableHandlerMethod.java:137)
          at org.springframework.web.servlet.mvc.method.annotation.ServletInvocabl
  eHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
          at org.springframework.web.servlet.mvc.method.annotation.RequestMappingH
  andlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:776)
          at org.springframework.web.servlet.mvc.method.annotation.RequestMappingH
  andlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:705)
          at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapt
  er.handle(AbstractHandlerMethodAdapter.java:85)
          at org.springframework.web.servlet.DispatcherServlet.doDispatch(Dispatch
  erServlet.java:959)
          at org.springframework.web.servlet.DispatcherServlet.doService(Dispatche
  rServlet.java:893)
          at org.springframework.web.servlet.FrameworkServlet.processRequest(Frame
  workServlet.java:967)
          at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServ
  let.java:869)
          at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
          at org.springframework.web.servlet.FrameworkServlet.service(FrameworkSer
  vlet.java:843)
          at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
          at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
  icationFilterChain.java:291)
          at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
  ilterChain.java:206)
          at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52
  )
          at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
  icationFilterChain.java:239)
          at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
  ilterChain.java:206)
          at favorite.ws.filters.SimpleCORSFilter.doFilter(SimpleCORSFilter.java:2
  2)
          at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
  icationFilterChain.java:239)
          at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
  ilterChain.java:206)
          at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInterna
  l(HiddenHttpMethodFilter.java:77)
          at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerR
  equestFilter.java:107)
          at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
  icationFilterChain.java:239)
          at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
  ilterChain.java:206)
          at org.springframework.web.filter.CharacterEncodingFilter.doFilterIntern
  al(CharacterEncodingFilter.java:85)
          at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerR
  equestFilter.java:107)
          at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
  icationFilterChain.java:239)
          at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
  ilterChain.java:206)
          at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperV
  alve.java:219)
          at org.apache.catalina.core.StandardContextValve.invoke(StandardContextV
  alve.java:106)
          at org.apache.catalina.authenticator.AuthenticatorBase.invoke(Authentica
  torBase.java:502)
          at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.j
  ava:142)
          at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.j
  ava:79)
          at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineVal
  ve.java:88)
          at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.jav
  a:518)
          at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp
  11Processor.java:1091)
          at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(
  AbstractProtocol.java:668)
          at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpo
  int.java:1521)
          at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoin
  t.java:1478)
          at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.
  java:1142)
          at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor
  .java:617)
          at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskTh
  read.java:61)
          at java.lang.Thread.run(Thread.java:745)
  </code></pre>
  
  <p>Can someone tell me what's wrong? Why am I getting a 500 Internal Server Error and not a 400 Bad Request Error? How do I fix this?</p>
  
  <p>If a user selects a username (for example) which is already in use, I want the server to send a response telling him that. How do I achieve this? Tell him that the username isn't unique or the email is already registered (i.e. different error messages for username/email/mobile)?</p>
  
  <p>Thanks!</p>