0

I'm creating a Spring MVC web app (using Hibernate) and am trying to delete from a parent table which throws the java exceptions as seen in the stacktrace below. I receive a similar error within MySQL workbench after attempting to perform the same delete operation, so could just be bad setup of my schema on my part.

I've setup a Uni-directional One-to-Many mapping, one 'User' to many 'Comments'. From my understanding the foreign key is described in the child table. I've replicated this within MySQL and the Java code.

HTTP Status 500 – Internal Server Error Type Exception Report Message Request processing failed; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement Description The server encountered an unexpected condition that prevented it from fulfilling the request. Exception org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) org.springframework.web.servlet.FrameworkServlet.doDelete(FrameworkServlet.java:923) javax.servlet.http.HttpServlet.service(HttpServlet.java:666) org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:875) javax.servlet.http.HttpServlet.service(HttpServlet.java:741) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) Root Cause org.hibernate.exception.ConstraintViolationException: could not execute statement org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:59) org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99) org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:178) org.hibernate.hql.internal.ast.exec.BasicExecutor.doExecute(BasicExecutor.java:100) org.hibernate.hql.internal.ast.exec.BasicExecutor.execute(BasicExecutor.java:59) org.hibernate.hql.internal.ast.exec.DeleteExecutor.execute(DeleteExecutor.java:109) org.hibernate.hql.internal.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:453) org.hibernate.engine.query.spi.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:378) org.hibernate.internal.SessionImpl.executeUpdate(SessionImpl.java:1532) org.hibernate.query.internal.AbstractProducedQuery.doExecuteUpdate(AbstractProducedQuery.java:1617) org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1594) co.uk.nightmarengine.remixapp.dao.UserDAOImpl.deleteUser(UserDAOImpl.java:53) co.uk.nightmarengine.remixapp.service.UserServiceImpl.deleteUser(UserServiceImpl.java:39) java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.base/java.lang.reflect.Method.invoke(Method.java:566) org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343) org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294) org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) com.sun.proxy.$Proxy51.deleteUser(Unknown Source) co.uk.nightmarengine.remixapp.rest.UserRestController.deleteUser(UserRestController.java:43) java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.base/java.lang.reflect.Method.invoke(Method.java:566) org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:215) org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:142) org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102) org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895) org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:800) org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1038) org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:942) org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:998) org.springframework.web.servlet.FrameworkServlet.doDelete(FrameworkServlet.java:923) javax.servlet.http.HttpServlet.service(HttpServlet.java:666) org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:875) javax.servlet.http.HttpServlet.service(HttpServlet.java:741) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) Root Cause java.sql.SQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`remix_webapp`.`Comment`, CONSTRAINT `FK_Comment_User` FOREIGN KEY (`user_id`) REFERENCES `User` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117) com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:974) com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1113) com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061) com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1381) com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1046) com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:384) org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175) org.hibernate.hql.internal.ast.exec.BasicExecutor.doExecute(BasicExecutor.java:100) org.hibernate.hql.internal.ast.exec.BasicExecutor.execute(BasicExecutor.java:59) org.hibernate.hql.internal.ast.exec.DeleteExecutor.execute(DeleteExecutor.java:109) org.hibernate.hql.internal.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:453) org.hibernate.engine.query.spi.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:378) org.hibernate.internal.SessionImpl.executeUpdate(SessionImpl.java:1532) org.hibernate.query.internal.AbstractProducedQuery.doExecuteUpdate(AbstractProducedQuery.java:1617) org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1594) co.uk.nightmarengine.remixapp.dao.UserDAOImpl.deleteUser(UserDAOImpl.java:53) co.uk.nightmarengine.remixapp.service.UserServiceImpl.deleteUser(UserServiceImpl.java:39) java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.base/java.lang.reflect.Method.invoke(Method.java:566) org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343) org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294) org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) com.sun.proxy.$Proxy51.deleteUser(Unknown Source) co.uk.nightmarengine.remixapp.rest.UserRestController.deleteUser(UserRestController.java:43) java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.base/java.lang.reflect.Method.invoke(Method.java:566) org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:215) org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:142) org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102) org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895) org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:800) org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1038) org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:942) org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:998) org.springframework.web.servlet.FrameworkServlet.doDelete(FrameworkServlet.java:923) javax.servlet.http.HttpServlet.service(HttpServlet.java:666) org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:875) javax.servlet.http.HttpServlet.service(HttpServlet.java:741) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) Note The full stack trace of the root cause is available in the server logs. Apache Tomcat/9.0.12

The SQL tables:

DROP TABLE IF EXISTS `User`; CREATE TABLE `User`( id int(11) NOT NULL auto_increment, username varchar(50) NOT NULL, `password` varchar(68) NOT NULL, enabled tinyint (1) NOT NULL, first_name varchar(48) DEFAULT NULL, last_name varchar(48) DEFAULT NULL, email varchar(128) DEFAULT NULL, created_at timestamp NOT NULL, location varchar(48) DEFAULT NULL, description varchar(512) DEFAULT NULL, picture_file varchar(256) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY(username) )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS `Comment`; CREATE TABLE Comment( id int(11) NOT NULL auto_increment, track_id int(11) NOT NULL, user_id int(11) NOT NULL, content varchar(512) NOT NULL, time_posted timestamp NOT NULL, PRIMARY KEY (id), CONSTRAINT FK_Comment_Track FOREIGN KEY (track_id) REFERENCES Track(id) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT FK_Comment_User FOREIGN KEY (user_id) REFERENCES `User`(id) ON DELETE NO ACTION ON UPDATE NO ACTION )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

If I attempt the following command within MySQL:

delete from remix_webapp.User where id=1;

I get this error:

delete from remix_webapp.User where id=1 Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`remix_webapp`.`Comment`, CONSTRAINT `FK_Comment_User` FOREIGN KEY (`user_id`) REFERENCES `User` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) 0.0072 sec

Here are the two entities within Java,however as mentioned before I've only added the mapping onto Comment.

Comment.java

@Entity @Table(name="Comment") public class Comment { @Id @GeneratedValue(strategy=GenerationType.IDENTITY) @Column private int id; @ManyToOne(fetch=FetchType.LAZY, cascade= { CascadeType.PERSIST, CascadeType.MERGE, CascadeType.DETACH, CascadeType.REFRESH }) @JoinColumn(name="track_id") private Track track; @ManyToOne(fetch=FetchType.LAZY, cascade= { CascadeType.PERSIST, CascadeType.MERGE, CascadeType.DETACH, CascadeType.REFRESH, CascadeType.REMOVE }) @JoinColumn(name="user_id") private User user; @Column(name="content") private String content; @Column(name="time_posted") private Timestamp timePosted;

User.java:

@Entity @Table(name="User") public class User { @Id @GeneratedValue(strategy=GenerationType.IDENTITY) @Column(name="id") private int id; @Column(name="username") private String username; @Column(name="password") private String password; @Column(name="enabled") private int enabled; @Column(name="first_name") private String firstName; @Column(name="last_name") private String lastName; @Column(name="email") private String email; @Column(name="created_at") private String createdAt; @Column(name="location") private String location; @Column(name="description") private String description; @Column(name="picture_file") private String pictureFile;

My UserDAO class eventually runs this code when performing the delete:

@Override public void deleteUser(int id) { Session session = sessionFactory.getCurrentSession(); Query query = session.createQuery("delete from User where id=:userId"); query.setParameter("userId", id); query.executeUpdate(); }

As I mentioned, this might just be because I've written my SQL tables incorrectly, however I can't see what I'm doing wrong here.

Any help is much appreciated!

Improve this question

1 Answer 1

Reset to default
1

This is exclusively MySQL problem.

Your problem is the FK_Comment_User constraint on delete has no action, while comment.user_id must be not null.

So depending on your needs you must change the constraint to cascade or set to null (but in that case you need to change the comment.user_id to accept null).

Also, I assume you actually have in your code a track table right? Because I see a reference in the other constraint.

Cheers

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks for the suggestion.
No worries, just let us know if it solves your problem!
Although I think changing these two things brings me some new problems. Setting the constraint to 'ON DELETE SET NULL' means if I want to cascade from the java-side it still sets the user_id column to NULL, keeping the rest of the data in the row. If I set to 'ON DELETE CASCADE', I get my desired outcome, however my application is no longer database-agnostic. MySQL seems to override my java code if I've defined Cascade functions in both places. There must be a way exclusively use Java for these operations? Yes Track does exist but I left it out for simplicity :)
Unfortunately I didn't touch Java in years so I can't help you there, maybe somebody else can jump in, but another way I can think of would be to use a soft-delete, meaning adding a timestamp field in your schema like deleted_at which allows null values and has null as default; when you want to delete something you just set the field with the current timestamp. The complication is that you need to change all methods to filter those records whose deleted_at is not null (because deleted).
Ah I see that's definitely an option. I could probably even use the comment.user_id as the 'soft-delete' in that case, so comments with a null user_id aren't shown. The database can then be purged of these comments every once in a while with a script. I'd probably still prefer if anyone knew of an all-java solution to this for simplicity's sake, but thank you for your suggestions - I can at least have it mostly working now :)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.