无法在MySQL错误1451中删除行。需要实现DELETE ON CASCADE

wnatus 发布于 5 天前 mysql 最后更新 5 天前 17 浏览

我想删除一个基于ID的记录。但我不能,因为外键约束失败......这意味着我可能需要在Cascade上实现DELETE ......但我不知道这是如何工作的。底线我需要修复错误,并且/或者我需要在Cascade上实现Delete,因为我通过网站进行搜索,这是最可行的解决方案。 这是查询:

SET SQL_SAFE_UPDATES = 0;
DELETE `rentals`, `rental_movie`
FROM `rentals`
LEFT JOIN `rental_movie`
    ON `rentals`.`idRentals` = `rental_movie`.`Rentals_idRentals`
WHERE `Movie_idMovie` = 1;
我在MySQL中有错误1451:
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`project`.`rental_movie`, CONSTRAINT `fk_Rental_Movie_Rentals1` FOREIGN KEY (`Rentals_idRentals`) REFERENCES `rentals` (`idRentals`) ON DELETE NO ACTION ON UPDATE NO ACTION)
这是我的数据库:
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='TRADITIONAL,ALLOW_INVALID_DATES';
CREATE SCHEMA IF NOT EXISTS `project` DEFAULT CHARACTER SET utf8 ;
USE `project` ;
-- -----------------------------------------------------
-- Table `project`.`customer`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `project`.`customer` ;
CREATE TABLE IF NOT EXISTS `project`.`customer` (
  `idCustomer` INT(11) NOT NULL AUTO_INCREMENT,
  `CustomerName` VARCHAR(20) NOT NULL,
  `CustomerLastName` VARCHAR(20) NOT NULL,
  `CustomerAddressl` VARCHAR(45) NOT NULL,
  `ZipCode` INT(11) NOT NULL,
  `CustomerPueblo` VARCHAR(20) NOT NULL,
  `CustomerTel` DECIMAL(10,0) NOT NULL,
  PRIMARY KEY (`idCustomer`))
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `project`.`movie`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `project`.`movie` ;
CREATE TABLE IF NOT EXISTS `project`.`movie` (
  `idMovie` INT(11) NOT NULL AUTO_INCREMENT,
  `TitleMovie` VARCHAR(45) NOT NULL,
  `Genre` VARCHAR(45) NOT NULL,
  `ReleaseDate` VARCHAR(45) NOT NULL,
  `RunTime` TIME NOT NULL,
  `Rated` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`idMovie`))
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `project`.`rentals`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `project`.`rentals` ;
CREATE TABLE IF NOT EXISTS `project`.`rentals` (
  `idRentals` INT(11) NOT NULL AUTO_INCREMENT,
  `Customer_idCustomer` INT(11) NOT NULL,
  `RentedDate` DATE NOT NULL,
  `ReturnDate` DATE NULL DEFAULT NULL,
  PRIMARY KEY (`idRentals`),
  INDEX `fk_Rentals_Customer_idx` (`Customer_idCustomer` ASC),
  CONSTRAINT `fk_Rentals_Customer`
    FOREIGN KEY (`Customer_idCustomer`)
    REFERENCES `project`.`customer` (`idCustomer`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `project`.`rental_movie`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `project`.`rental_movie` ;
CREATE TABLE IF NOT EXISTS `project`.`rental_movie` (
  `Movie_idMovie` INT(11) NOT NULL,
  `Rentals_idRentals` INT(11) NOT NULL,
  PRIMARY KEY (`Movie_idMovie`, `Rentals_idRentals`),
  INDEX `fk_Rental_Movie_Rentals1_idx` (`Rentals_idRentals` ASC),
  CONSTRAINT `fk_Rental_Movie_Movie1`
    FOREIGN KEY (`Movie_idMovie`)
    REFERENCES `project`.`movie` (`idMovie`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Rental_Movie_Rentals1`
    FOREIGN KEY (`Rentals_idRentals`)
    REFERENCES `project`.`rentals` (`idRentals`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `project`.`transaction`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `project`.`transaction` ;
CREATE TABLE IF NOT EXISTS `project`.`transaction` (
  `idTransaction` INT(11) NOT NULL AUTO_INCREMENT,
  `idRentals` INT(11) NOT NULL,
  `DaysRented` INT(11) NULL DEFAULT NULL,
  `Cost` DECIMAL(10,0) NULL DEFAULT NULL,
  `TotalCost` DECIMAL(10,0) NULL DEFAULT NULL,
  PRIMARY KEY (`idTransaction`),
  INDEX `idRentals_idx` (`idRentals` ASC),
  CONSTRAINT `idRentals`
    FOREIGN KEY (`idRentals`)
    REFERENCES `project`.`rentals` (`idRentals`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = utf8;
USE `project` ;
-- -----------------------------------------------------
-- Placeholder table for view `project`.`new_view`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `project`.`new_view` (`idRentals` INT, `Customer_idCustomer` INT);
-- -----------------------------------------------------
-- Placeholder table for view `project`.`rentals_view`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `project`.`rentals_view` (`idRentals` INT, `idCustomer` INT, `CustomerName` INT, `idMovie` INT, `TitleMovie` INT, `RentedDate` INT);
-- -----------------------------------------------------
-- procedure PName
-- -----------------------------------------------------
USE `project`;
DROP procedure IF EXISTS `project`.`PName`;
DELIMITER $$
USE `project`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `PName`(cid INT)
BEGIN
SELECT * FROM rentals;
END$$
DELIMITER ;
-- -----------------------------------------------------
-- View `project`.`new_view`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `project`.`new_view` ;
DROP TABLE IF EXISTS `project`.`new_view`;
USE `project`;
CREATE  OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `project`.`new_view` AS select `project`.`rentals`.`idRentals` AS `idRentals`,`project`.`rentals`.`Customer_idCustomer` AS `Customer_idCustomer` from `project`.`rentals`;
-- -----------------------------------------------------
-- View `project`.`rentals_view`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `project`.`rentals_view` ;
DROP TABLE IF EXISTS `project`.`rentals_view`;
USE `project`;
CREATE  OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `project`.`rentals_view` AS select `r`.`idRentals` AS `idRentals`,`c`.`idCustomer` AS `idCustomer`,`c`.`CustomerName` AS `CustomerName`,`m`.`idMovie` AS `idMovie`,`m`.`TitleMovie` AS `TitleMovie`,`r`.`RentedDate` AS `RentedDate` from (((`project`.`rentals` `r` join `project`.`customer` `c` on((`r`.`Customer_idCustomer` = `c`.`idCustomer`))) join `project`.`rental_movie` `rm` on((`rm`.`Rentals_idRentals` = `r`.`idRentals`))) join `project`.`movie` `m` on((`rm`.`Movie_idMovie` = `m`.`idMovie`)));
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
已邀请:

id_sed

赞同来自:

也许您应该考虑删除rental_movie表,并将Movie_idMovie列移动到租借表上,因为它是一对一的关系,您创建的这个表会让您感到悲伤,就像您已经经历过的一样。

DROP TABLE `project`.`rental_movie`;
ALTER TABLE `project`.`rentals` 
ADD COLUMN `Movie_idMovie` INT(11) NOT NULL AFTER `Customer_idCustomer`,
ADD INDEX `fk_Rentals_Movie_idx` (`Movie_idMovie` ASC);
ALTER TABLE `project`.`rentals` 
ADD CONSTRAINT `fk_Rentals_Movie`
        FOREIGN KEY (`Movie_idMovie`)
        REFERENCES `project`.`movie` (`idMovie`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION;

xesse

赞同来自:

您需要使用ON DELETE CASCADE选项声明的外键。

-- -----------------------------------------------------
-- Table `project`.`rental_movie`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `project`.`rental_movie` ;
CREATE TABLE IF NOT EXISTS `project`.`rental_movie` (
  `Movie_idMovie` INT(11) NOT NULL,
  `Rentals_idRentals` INT(11) NOT NULL,
  PRIMARY KEY (`Movie_idMovie`, `Rentals_idRentals`),
  INDEX `fk_Rental_Movie_Rentals1_idx` (`Rentals_idRentals` ASC),
  CONSTRAINT `fk_Rental_Movie_Movie1`
    FOREIGN KEY (`Movie_idMovie`)
    REFERENCES `project`.`movie` (`idMovie`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Rental_Movie_Rentals1`
    FOREIGN KEY (`Rentals_idRentals`)
    REFERENCES `project`.`rentals` (`idRentals`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
然后,如果您删除rental_movie.Rentals_idRentals中的值,它将自动删除rentals.idRentals中的值。 这种删除将是同时的和原子的 - 也就是说,没有其他同时运行的查询将能够看到在一个表中删除的数据而在另一个表中没有删除。