mysql - 尝试从父表中删除时出现 SQLIntegrityConstraintViolationException(使用 Java Spring 和 MySQL)

Question

我正在创建一个 Spring MVC Web 应用程序(使用 Hibernate)，并尝试从父表中删除，该表会引发 java 异常，如下面的堆栈跟踪所示。在尝试执行相同的删除操作后，我在 MySQL 工作台中收到类似的错误，因此可能是我的架构设置错误。

我已经设置了单向一对多映射，一个“用户”到多个“评论”。根据我的理解，外键在子表中描述。我已在 MySQL 和 Java 代码中复制了此内容。

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

SQL 表:

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;

如果我在 MySQL 中尝试以下命令:

delete from remix_webapp.User where id=1;

我收到此错误:

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

这是 Java 中的两个实体，但是正如之前提到的，我仅将映射添加到 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;

用户.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;

我的 UserDAO 类最终在执行删除时运行此代码:

@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();

}

正如我提到的，这可能只是因为我错误地编写了 SQL 表，但是我看不出我在这里做错了什么。

非常感谢任何帮助!

Answer 1

这完全是 MySQL 的问题。

您的问题是 FK_Comment_User 对删除的约束没有任何操作，而 comment.user_id 必须不为空。

因此，根据您的需要，您必须将约束更改为级联或设置为 null(但在这种情况下，您需要更改 comment.user_id 以接受 null)。

另外，我假设您的代码中实际上有一个轨道表，对吧？因为我在其他约束中看到了引用。

干杯