时间戳

首页 > 解决方案 > 无法从与另一个表具有多对多关系的表中删除行

java - 无法从与另一个表具有多对多关系的表中删除行

问题描述

我有两个具有多对多关系的实体。**

public class Appointment {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private LocalDateTime actionDateTime;

    @ManyToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @OnDelete(action = OnDeleteAction.NO_ACTION)
    @JoinTable(name = "users_appointments",
    joinColumns = {@JoinColumn(name="appointment_id")},
    inverseJoinColumns = {@JoinColumn(name="user_id")})
    private Set<AppUser> participants = new HashSet<>();
}

public class AppUser implements UserDetails {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(unique=true)
    private String username;
    private String password;
    private String role;
    private int rating;

    @ManyToMany(fetch = FetchType.LAZY, mappedBy = "participants", cascade = CascadeType.ALL)
    @OnDelete(action = OnDeleteAction.CASCADE)
    private Set<Appointment> appointments = new HashSet<>();
}

如何从约会中删除？每次我尝试它时，我都会看到以下错误：

18:43:23 delete from appointments where appointments.id = 1
错误代码：1451。无法删除或更新父行：外键约束失败(`beauty_salon`.`users_appointments`, CONSTRAINT `FKe4fn9rrveg7s7ff17fxnwt51d` FOREIGN KEY (`appointment_id`) REFERENCES `appointments` (`id`)) 0.000 秒

如果我对我创建的数据库进行反向工程，然后对其进行正向工程以查看代码，我们将看到以下内容：

-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema beauty_salon
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema beauty_salon
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `beauty_salon` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
USE `beauty_salon` ;

-- -----------------------------------------------------
-- Table `beauty_salon`.`appointments`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `beauty_salon`.`appointments` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `action_date_time` DATETIME(6) NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `beauty_salon`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `beauty_salon`.`users` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `password` VARCHAR(255) NULL DEFAULT NULL,
  `rating` INT NOT NULL,
  `role` VARCHAR(255) NULL DEFAULT NULL,
  `username` VARCHAR(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `UK_r43af9ap4edm43mmtq01oddj6` (`username` ASC) VISIBLE)
ENGINE = InnoDB
AUTO_INCREMENT = 5
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `beauty_salon`.`users_appointments`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `beauty_salon`.`users_appointments` (
  `appointment_id` BIGINT NOT NULL,
  `user_id` BIGINT NOT NULL,
  PRIMARY KEY (`appointment_id`, `user_id`),
  INDEX `FK1mwwyjbcdbqsvock8n7quafhv` (`user_id` ASC) VISIBLE,
  CONSTRAINT `FK1mwwyjbcdbqsvock8n7quafhv`
    FOREIGN KEY (`user_id`)
    REFERENCES `beauty_salon`.`users` (`id`)
    ON DELETE CASCADE,
  CONSTRAINT `FKe4fn9rrveg7s7ff17fxnwt51d`
    FOREIGN KEY (`appointment_id`)
    REFERENCES `beauty_salon`.`appointments` (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

标签： javamysqlspringjpaspring-data-jpa

解决方案

一般来说 - 如果您需要删除 M:N 关系中的某个实体，那么您只需从其表中删除该实体的行。外键的级联操作（必须正确定义）执行所有需要的附加操作。

Example

请记住 - 不会从另一个实体表中删除任何行 - 即使在删除后相邻表中不存在相关记录。如果需要，您必须在任何情况下明确删除实体行（例如，使用日常服务事件过程）。

The fiddle提供了 DDL。

推荐阅读