I have a Spring Boot backend that uses Spring Data JPA to communicate with the database. I am attempting to save a cart item into the database, but a foreign key constraint fails while saving a child row to a table that establishes a many-to-many relationship between cart items and excursions. Inspecting the logged SQL queries and their bound parameters does not reveal any issue that I can see.
It’s probably worth noting that this is a school project, not an actual back end that will be put into production. Both the database and front end have been provided to me and, as such, are certain to be in working order.
The relevant entity classes are Customer, Cart, CartItem, and Excursion. Excursions and CartItems have a many-to-many relationship, which is defined like so:
@Entity(name="cart_items")
public class CartItem {
@Id
@Column(name="cart_item_id")
@GeneratedValue(strategy=GenerationType.IDENTITY)
private long id;
// ...
@ManyToMany
@JoinTable(name="excursion_cartitem",
joinColumns = {@JoinColumn(name="cart_item_id")},
inverseJoinColumns = {@JoinColumn(name="excursion_id")}
)
@Getter
@Setter
private Set<Excursion> excursions = new HashSet<Excursion>();
}
@Entity(name="excursions")
public class Excursion {
@Id
@Column(name="excursion_id")
@GeneratedValue(strategy=GenerationType.IDENTITY)
private long id;
// ...
@ManyToMany(mappedBy="excursions")
private Set<CartItem> cartItems = new HashSet<CartItem>();
}
A RestController handles post requests. Each request contains a Customer, Cart, and a list of CartItems. All entities have corresponding DAO objects which extend JPARepository and are Autowired into the controller class.
@RestController
@CrossOrigin
public class CheckoutController {
// ...
@PostMapping("...")
@Transactional
public ResponseEntity<PurchaseResponse> checkout(@RequestBody PurchaseData purchaseData) {
String orderTrackingNumber = "#################";
purchaseData.setCartOrderTrackingNumber(orderTrackingNumber);
// Adds the Customer to the Cart, and the Cart to all the CartItems.
purchaseData.resolveConnections();
cartDao.save(purchaseData.getCart());
cartItemDao.saveAll(purchaseData.getCartItems());
PurchaseResponse response = new PurchaseResponse();
response.setOrderTrackingNumber(orderTrackingNumber);
return ResponseEntity.ok(response);
}
}
If I use the front end to send this post request and any excursions have been selected, the following appears in the output:
Hibernate: insert into cart_items (cart_id,create_date,last_update,vacation_id) values (?,?,?,?)
2024-07-14T20:21:07.470-04:00 TRACE 3740 --- [nio-8080-exec-2] org.hibernate.orm.jdbc.bind : binding parameter (1:BIGINT) <- [25]
2024-07-14T20:21:07.470-04:00 TRACE 3740 --- [nio-8080-exec-2] org.hibernate.orm.jdbc.bind : binding parameter (2:DATE) <- [null]
2024-07-14T20:21:07.470-04:00 TRACE 3740 --- [nio-8080-exec-2] org.hibernate.orm.jdbc.bind : binding parameter (3:DATE) <- [null]
2024-07-14T20:21:07.470-04:00 TRACE 3740 --- [nio-8080-exec-2] org.hibernate.orm.jdbc.bind : binding parameter (4:BIGINT) <- [2]
Hibernate: insert into excursion_cartitem (cart_item_id,excursion_id) values (?,?)
2024-07-14T20:21:07.481-04:00 TRACE 3740 --- [nio-8080-exec-2] org.hibernate.orm.jdbc.bind : binding parameter (1:BIGINT) <- [24]
2024-07-14T20:21:07.481-04:00 TRACE 3740 --- [nio-8080-exec-2] org.hibernate.orm.jdbc.bind : binding parameter (2:BIGINT) <- [6]
2024-07-14T20:21:07.503-04:00 WARN 3740 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 1216, SQLState: 23000
2024-07-14T20:21:07.503-04:00 ERROR 3740 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper : Cannot add or update a child row: a foreign key constraint fails
I can guarantee that the excursion_id is valid, so I can only assume that the cart_item_id is causing the violation. However, I can’t imagine how this could happen. The two queries are the result of the same function call to save the cart_item, I assume that JPA can be counted on to reliably retrieve the ID of the item it just inserted, so what’s going on here?
Here are the table create statements given by “show create table”:
CREATE TABLE `cart_items` (
`cart_item_id` bigint NOT NULL AUTO_INCREMENT,
`create_date` datetime(6) DEFAULT NULL,
`last_update` datetime(6) DEFAULT NULL,
`cart_id` bigint NOT NULL,
`vacation_id` bigint NOT NULL,
PRIMARY KEY (`cart_item_id`),
KEY `cart_id` (`cart_id`),
KEY `vacation_id` (`vacation_id`),
CONSTRAINT `cart_items_ibfk_1` FOREIGN KEY (`vacation_id`) REFERENCES `vacations` (`vacation_id`),
CONSTRAINT `cart_items_ibfk_2` FOREIGN KEY (`cart_id`) REFERENCES `carts` (`cart_id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
CREATE TABLE `excursions` (
`excursion_id` bigint NOT NULL AUTO_INCREMENT,
`create_date` datetime(6) DEFAULT NULL,
`excursion_price` decimal(19,2) DEFAULT NULL,
`excursion_title` varchar(255) DEFAULT NULL,
`image_url` varchar(255) DEFAULT NULL,
`last_update` datetime(6) DEFAULT NULL,
`vacation_id` bigint NOT NULL,
PRIMARY KEY (`excursion_id`),
KEY `vacation_id` (`vacation_id`),
CONSTRAINT `excursions_ibfk_1` FOREIGN KEY (`vacation_id`) REFERENCES `vacations` (`vacation_id`)
) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
CREATE TABLE `excursion_cartitem` (
`cart_item_id` bigint NOT NULL,
`excursion_id` bigint NOT NULL,
PRIMARY KEY (`cart_item_id`,`excursion_id`),
KEY `excursion_id` (`excursion_id`),
CONSTRAINT `excursion_cartitem_ibfk_1` FOREIGN KEY (`excursion_id`) REFERENCES `cart_items` (`cart_item_id`),
CONSTRAINT `excursion_cartitem_ibfk_2` FOREIGN KEY (`cart_item_id`) REFERENCES `excursions` (`excursion_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
Here is the tracestack:
java.sql.SQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:118) ~[mysql-connector-j-8.3.0.jar:8.3.0]
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-j-8.3.0.jar:8.3.0]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:912) ~[mysql-connector-j-8.3.0.jar:8.3.0]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1054) ~[mysql-connector-j-8.3.0.jar:8.3.0]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1003) ~[mysql-connector-j-8.3.0.jar:8.3.0]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1312) ~[mysql-connector-j-8.3.0.jar:8.3.0]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:988) ~[mysql-connector-j-8.3.0.jar:8.3.0]
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) ~[HikariCP-5.1.0.jar:na]
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) ~[HikariCP-5.1.0.jar:na]
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:194) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.engine.jdbc.mutation.internal.AbstractMutationExecutor.performNonBatchedMutation(AbstractMutationExecutor.java:134) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.engine.jdbc.mutation.internal.MutationExecutorSingleNonBatched.performNonBatchedOperations(MutationExecutorSingleNonBatched.java:55) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.engine.jdbc.mutation.internal.AbstractMutationExecutor.execute(AbstractMutationExecutor.java:55) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.persister.collection.mutation.InsertRowsCoordinatorStandard.insertRows(InsertRowsCoordinatorStandard.java:117) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.persister.collection.BasicCollectionPersister.recreate(BasicCollectionPersister.java:119) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.action.internal.CollectionRecreateAction.execute(CollectionRecreateAction.java:47) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:632) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:499) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:371) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:41) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:127) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1425) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:487) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:2324) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:1981) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:439) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:169) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:267) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:562) ~[spring-orm-6.1.8.jar:6.1.8]
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:795) ~[spring-tx-6.1.8.jar:6.1.8]
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:758) ~[spring-tx-6.1.8.jar:6.1.8]
at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:676) ~[spring-tx-6.1.8.jar:6.1.8]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:426) ~[spring-tx-6.1.8.jar:6.1.8]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-6.1.8.jar:6.1.8]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.8.jar:6.1.8]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:768) ~[spring-aop-6.1.8.jar:6.1.8]
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:720) ~[spring-aop-6.1.8.jar:6.1.8]
at com.example.demo.controllers.CheckoutController$$SpringCGLIB$$0.checkout(<generated>) ~[classes/:na]
at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:580) ~[na:na]
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:255) ~[spring-web-6.1.8.jar:6.1.8]
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:188) ~[spring-web-6.1.8.jar:6.1.8]
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:118) ~[spring-webmvc-6.1.8.jar:6.1.8]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:926) ~[spring-webmvc-6.1.8.jar:6.1.8]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:831) ~[spring-webmvc-6.1.8.jar:6.1.8]
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-6.1.8.jar:6.1.8]
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1089) ~[spring-webmvc-6.1.8.jar:6.1.8]
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:979) ~[spring-webmvc-6.1.8.jar:6.1.8]
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1014) ~[spring-webmvc-6.1.8.jar:6.1.8]
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:914) ~[spring-webmvc-6.1.8.jar:6.1.8]
at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:590) ~[tomcat-embed-core-10.1.24.jar:6.0]
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:885) ~[spring-webmvc-6.1.8.jar:6.1.8]
at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658) ~[tomcat-embed-core-10.1.24.jar:6.0]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:195) ~[tomcat-embed-core-10.1.24.jar:10.1.24]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) ~[tomcat-embed-core-10.1.24.jar:10.1.24]
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51) ~[tomcat-embed-websocket-10.1.24.jar:10.1.24]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) ~[tomcat-embed-core-10.1.24.jar:10.1.24]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) ~[tomcat-embed-core-10.1.24.jar:10.1.24]
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-6.1.8.jar:6.1.8]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.8.jar:6.1.8]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) ~[tomcat-embed-core-10.1.24.jar:10.1.24]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) ~[tomcat-embed-core-10.1.24.jar:10.1.24]
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-6.1.8.jar:6.1.8]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.8.jar:6.1.8]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) ~[tomcat-embed-core-10.1.24.jar:10.1.24]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) ~[tomcat-embed-core-10.1.24.jar:10.1.24]
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-6.1.8.jar:6.1.8]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.8.jar:6.1.8]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) ~[tomcat-embed-core-10.1.24.jar:10.1.24]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) ~[tomcat-embed-core-10.1.24.jar:10.1.24]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167) ~[tomcat-embed-core-10.1.24.jar:10.1.24]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90) ~[tomcat-embed-core-10.1.24.jar:10.1.24]
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:482) ~[tomcat-embed-core-10.1.24.jar:10.1.24]
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:115) ~[tomcat-embed-core-10.1.24.jar:10.1.24]
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93) ~[tomcat-embed-core-10.1.24.jar:10.1.24]
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) ~[tomcat-embed-core-10.1.24.jar:10.1.24]
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344) ~[tomcat-embed-core-10.1.24.jar:10.1.24]
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:389) ~[tomcat-embed-core-10.1.24.jar:10.1.24]
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63) ~[tomcat-embed-core-10.1.24.jar:10.1.24]
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:896) ~[tomcat-embed-core-10.1.24.jar:10.1.24]
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1741) ~[tomcat-embed-core-10.1.24.jar:10.1.24]
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52) ~[tomcat-embed-core-10.1.24.jar:10.1.24]
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1190) ~[tomcat-embed-core-10.1.24.jar:10.1.24]
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) ~[tomcat-embed-core-10.1.24.jar:10.1.24]
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:63) ~[tomcat-embed-core-10.1.24.jar:10.1.24]
at java.base/java.lang.Thread.run(Thread.java:1570) ~[na:na]
Thank you very much in advance!
New contributor
Ekobadd is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
5
The database contains the cart_items table with the cart_item_id column, the excursions table with the excursion_id column, and the excursion_cartitem table with two similarly named columns. Foreign key constraints on those columns restrict them to hold only values that are present in the primary keys of the first two tables.
However, the foreign key restrictions are backwards. The excursion_id column is bound to the cart_item_id column in the cart_items table and the cart_item_id column is bound to the excursion_id column in the excursions table.
As the database was provided to me and I cannot modify it, the solution is to modify the @JoinTable annotation to look like this:
@JoinTable(name="excursion_cartitem",
joinColumns = {@JoinColumn(name="excursion_id")},
inverseJoinColumns = {@JoinColumn(name="cart_item_id")}
)
New contributor
Ekobadd is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.