[spring, jpa] Cannot add or update a child row: a foreign key constraint fails

2021. 5. 14. 15:09

728x90

mysql의 auto_increment(자동증가)를 기본키로 사용하고 있다.

그런데 jpa에서는 save시 db에 바로 정보가 insert되지 않아

자동증가하는 기본키를 외래키로 바로 사용해야 하는 경우 문제가 된다.

그래서 이때 save를 대신해서 saveAndFlush를 사용할 수 있다.

1
2
3
4
5
6
7
8
9
10

// Member에 insert
// Member saveEntity = repositoryClass.save(entity);
Member saveEntity = repositoryClass.saveAndFlush(entity);
        
// 권한 저장
if (req.getAuthSeq() != null && req.getAuthSeq() > 0) {
    // Member의 Auth를 insert
    AuthOfMember authOfMemEntity = new AuthOfMember(req.getAuthSeq(), saveEntity.getMemSeq());
    authOfMemberRepositoryClass.save(authOfMemEntity);
}
Colored by Color Scripter

cs

에러는 2번 라인으로 인해서 발생되므로, 3번과 같이 saveAndFlush를 사용하면

기본키 값을 가져올 수 있다.

* 아래는 에러 내용

1. insert into auth_of_member (crt_by, crt_dt, del_by, del_dt, del_yn, upd_by, upd_dt, auth_seq, mem_seq) values (0, '05/13/2021 22:23:58.897', NULL, NULL, 'N', 0, '05/13/2021 22:23:58.897', 1, -1)

{FAILED after 0 msec}

java.sql.SQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`dbName`.`auth_of_member`, CONSTRAINT `fk_AUTH_OF_MEMBER_member1` FOREIGN KEY (`MEM_SEQ`) REFERENCES `member` (`MEM_SEQ`) ON DELETE NO ACTION ON UPDATE NO ACTION)

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117)

at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)

at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)

at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)

at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)

at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1347)

at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025)

at net.sf.log4jdbc.sql.jdbcapi.PreparedStatementSpy.executeUpdate(PreparedStatementSpy.java:1080)

at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)

at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)

at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)

at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3302)

at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3829)

at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:107)

at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)

at org.hibernate.engine.spi.ActionQueue.lambda$executeActions$1(ActionQueue.java:478)

at java.util.LinkedHashMap.forEach(Unknown Source)

at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:475)

at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:345)

at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:40)

at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:93)

at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1362)

at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:453)

at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3212)

at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2380)

at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:447)

at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:183)

at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoordinatorImpl.java:40)

at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:281)

at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101)

at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:562)

at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:743)

at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:711)

at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:654)

at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:407)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)

at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692)

at packageNameadmin.member.service.MemberService$$EnhancerBySpringCGLIB$$3becb09.insertItem()

at packageNameadmin.member.web.MemberController.insertItem(MemberController.java:94)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:197)

at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:141)

at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106)

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:894)

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)

at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1060)

at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:962)

at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)

at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:652)

at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)

at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)

at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)

at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)

at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)

at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:346)

at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374)

at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)

at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:887)

at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1684)

at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)

at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

at java.lang.Thread.run(Unknown Source)

20210513 22:23:58.897 [http-nio-8091-exec-9] WARN o.h.e.j.s.SqlExceptionHelper - SQL Error: 1452, SQLState: 23000

20210513 22:23:58.897 [http-nio-8091-exec-9] ERROR o.h.e.j.s.SqlExceptionHelper - Cannot add or update a child row: a foreign key constraint fails (`dbName`.`auth_of_member`, CONSTRAINT `fk_AUTH_OF_MEMBER_member1` FOREIGN KEY (`MEM_SEQ`) REFERENCES `member` (`MEM_SEQ`) ON DELETE NO ACTION ON UPDATE NO ACTION)

20210513 22:23:58.897 [http-nio-8091-exec-9] ERROR c.d.b.c.a.ExceptionControllerAdvice -

org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:276)

at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233)

at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:566)