mysql - 删除父级时 Hibernate/MySQL 级联错误

Question

我正在开发一个 java web 项目，该项目使用:

• Spring 4.3.2

• Hibernate 5.2.2/JPA 2.0+ MySQL5InnoDBDialect

• MySQL 5.6.15-innoDB(在 EasyPHP/PHPMyAdmin 上)+ JDBC 连接器6.0.4

当我想删除一些数据时遇到错误。但是，只有当我想删除一个有子级的父行并且他的一个子级也有<时，这个问题才存在。/strong> 至少一个 child 。

<小时/>

目标:我希望操作/查询删除所选行及其子行和孙行。

<小时/>

错误代码:在服务器输出中

28-Sep-2016 11:51:30.345 ERROR [http-nio-80-exec-42] org.hibernate.internal.ExceptionMapperStandardImpl.mapManagedFlushFailure HHH000346: Error during managed flush [org.hibernate.exception.ConstraintViolationException: could not execute statement] org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

父类: Pavillon

//...
private Set<Table> pavTables;
//...
@JsonIgnore
@Cascade(CascadeType.ALL)
@OneToMany(orphanRemoval = true, mappedBy = "pavillon", fetch = FetchType.EAGER)
public Set<com.optimal.waiter.component.model.Table> getPavTables() {
    return pavTables;
}
//...setters & others

子类: 表格

//...
private Pavillon pavillon;
//...
@NotNull
@Basic(optional = false)
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "PAV_ID")
public Pavillon getPavillon() {
    return pavillon;
}
@JsonIgnore
@Cascade(CascadeType.ALL)
@OneToMany(orphanRemoval = true, mappedBy = "table", fetch = FetchType.EAGER)
public Set<Reservation> getTableReservations() {
    return tableReservations;
}
//...setters & others

孙子类: 预订

//...
private Table table;
//...
@NotNull
@Basic(optional = false)
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "TAB_ID", nullable = false)
public Table getTable() {
    return table;
}
//...setters & others

SQL:

CREATE TABLE PAVILLONS
(
   PAV_ID               INT(10) NOT NULL AUTO_INCREMENT,
   PAV_NOM              VARCHAR(25) NOT NULL,
   PAV_DES              TEXT,
   PAV_TYPE             INT(1) NOT NULL DEFAULT 0,
   PRIMARY KEY (PAV_ID)
);
CREATE TABLE TABLES
(
   TAB_ID               INT(10) NOT NULL AUTO_INCREMENT,
   PAV_ID               INT(10) NOT NULL DEFAULT 1,
   TAB_DISPO            TINYINT(1) NOT NULL DEFAULT 1,
   TAB_TYPE             INT(1) NOT NULL DEFAULT 0,
   PRIMARY KEY (TAB_ID)
);
CREATE TABLE RESERVATIONS
(
   RES_ID               INT(10) NOT NULL AUTO_INCREMENT,
   TAB_ID               INT(10) NOT NULL,
   PRIMARY KEY (RES_ID)
);
ALTER TABLE TABLES ADD CONSTRAINT FK_PAV_TAB FOREIGN KEY (PAV_ID)
      REFERENCES PAVILLONS (PAV_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE RESERVATIONS ADD CONSTRAINT FK_TABLE_RESERVATION FOREIGN KEY (TAB_ID)
  REFERENCES TABLES (TAB_ID) ON DELETE CASCADE ON UPDATE CASCADE;

PS:即使我尝试删除然后直接在 PHPMyAdmin 中使用 SQL 查询，错误仍然存​​在。

错误代码:在服务器输出中

ERROR 1451: 1451: Cannot delete or update a parent row: a foreign key constraint fails (commandes, CONSTRAINT FK_TAB_COMM FOREIGN KEY (TAB_ID) REFERENCES tables (TAB_ID)) SQL Statement: DELETE FROM pavillons WHERE PAV_ID='2'

这就是为什么我认为问题出在数据库端。

等待您的帮助，任何建议都会有所帮助。

Answer 1

根据@Shadow i的评论，我认为与孙子相关的另一个表/实体导致了这个问题。

以后任何人遇到这个问题，应该重新检查每个相关表的所有关系。

例如:Table A ---> Table B ---> Table C ---> Table D

删除一行Table A(我想做的方式 - 级联方式)，它需要与Table有所有的关系。 D 正确级联 ON DELETE CASCADE 。特别是如果全部FK被迫NOT NULL .