MySQL InnoDB在DELETE CASCADE上更改参照操作

Is there a faster way to add a CASCADE rule to an existing foreign key than dropping and adding the constraint?

是否有更快的方法将CASCADE规则添加到现有外键而不是删除和添加约束?

SET foreign_key_checks = 0;
ALTER TABLE drop foreign key ...;
ALTER TABLE add foreign key ...;
SET foreign_key_checks = 1;

Manual says, you cannot add a foreign key and drop a foreign key in separate clauses of a single ALTER TABLE statement. You must use separate statements.

手动说,您不能添加外键并将外键放在单个ALTER TABLE语句的单独子句中。您必须使用单独的语句。

1 个解决方案

#1

I tried to use 'MySQL Workbench 5.2 CE->EER Diagram' to change only the foreign key option: from 'on delete restrict' to 'on delete cascade' in the table:

我尝试使用'MySQL Workbench 5.2 CE-> EER Diagram'来仅更改外键选项:从表中的'on delete restrict'到'on delete cascade':

CREATE  TABLE IF NOT EXISTS `mydb`.`Customers` (
  `CustomerID` INT NOT NULL AUTO_INCREMENT ,
  `FirstName` VARCHAR(45) NOT NULL ,
  `LastName` VARCHAR(45) NOT NULL ,
  `Email` VARCHAR(500) NOT NULL ,
  `Password` VARCHAR(500) NOT NULL ,
  `AddressID` INT NULL ,
  PRIMARY KEY (`CustomerID`) ,
  INDEX `AddressID_idx` (`AddressID` ASC) ,
  UNIQUE INDEX `Email_UNIQUE` (`Email` ASC) ,
  CONSTRAINT `CustomerAddressID`
    FOREIGN KEY (`AddressID` )
    REFERENCES `mydb`.`Address` (`AddressID` )
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;

So, that GUI tool generate the following code:

因此,该GUI工具生成以下代码:

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';

ALTER TABLE `mydb`.`Customers` DROP FOREIGN KEY `CustomerAddressID` ;

ALTER TABLE `mydb`.`Customers` 
  ADD CONSTRAINT `CustomerAddressID`
  FOREIGN KEY (`AddressID` )
  REFERENCES `mydb`.`Address` (`AddressID` )
  ON DELETE CASCADE
  ON UPDATE CASCADE;

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

I think it means that it's the natural way to change the foreign key option by two steps.

我认为这意味着通过两个步骤更改外键选项的自然方式。

#1