在 phpMyAdmin 中实现时 MySQL Workbench 创建 SQL 语句错误

Question

我在 MySQL Workbench 中创建了整个数据库，现在我尝试通过 phpMyAdmin 将其导入到我的 Webhost 上的数据库中。工作台创建了以下代码:

CREATE TABLE IF NOT EXISTS `usr_web375_4`.`Sandwich` (
  `Sandwich_ID` INT NOT NULL,
  `Style_ID` INT NOT NULL,
  `Bread_ID` INT NOT NULL,
  `Cheese_ID` INT NULL,
  `Size_ID` INT NOT NULL,
  `Sandwich_Toasted` TINYINT(1) NOT NULL,
  `Sandwich_Prize` FLOAT NOT NULL,
  PRIMARY KEY (`Sandwich_ID`),
  INDEX `Style_ID_idx` (`Style_ID` ASC),
  INDEX `Bread_ID_idx` (`Bread_ID` ASC),
  INDEX `Cheese_ID_idx` (`Cheese_ID` ASC),
  INDEX `Size_ID_idx` (`Size_ID` ASC),
  CONSTRAINT `Style_ID`
    FOREIGN KEY (`Style_ID`)
    REFERENCES `usr_web375_4`.`Style` (`Style_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Bread_ID`
    FOREIGN KEY (`Bread_ID`)
    REFERENCES `usr_web375_4`.`Bread` (`Bread_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Cheese_ID`
    FOREIGN KEY (`Cheese_ID`)
    REFERENCES `usr_web375_4`.`Cheese` (`Cheese_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Size_ID`
    FOREIGN KEY (`Size_ID`)
    REFERENCES `usr_web375_4`.`Size` (`Size_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `usr_web375_4`.`Sauce` (
  `Sauce_ID` INT NOT NULL,
  `Sauce_Name` VARCHAR(45) NULL,
  `Sauce_IMG` VARCHAR(45) NULL,
  `Sauce_Nutrition` INT NULL,
  PRIMARY KEY (`Sauce_ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `usr_web375_4`.`SandwichSauce`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `usr_web375_4`.`SandwichSauce` (
  `SandwichSauce_ID` INT NOT NULL,
  `Sandwich_ID` INT NOT NULL,
  `Sauce_ID` INT NOT NULL,
  PRIMARY KEY (`SandwichSauce_ID`),
  INDEX `Sandwich_ID_idx` (`Sandwich_ID` ASC),
  INDEX `Sauce_ID_idx` (`Sauce_ID` ASC),
  CONSTRAINT `Sandwich_ID`
    FOREIGN KEY (`Sandwich_ID`)
    REFERENCES `usr_web375_4`.`Sandwich` (`Sandwich_ID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `Sauce_ID`
    FOREIGN KEY (`Sauce_ID`)
    REFERENCES `usr_web375_4`.`Sauce` (`Sauce_ID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

我尝试使用导入函数和代码部分。它给了我以下错误:

CREATE TABLE IF NOT EXISTS `usr_web375_4`.`SandwichSauce` (

SandwichSauce_ID INT NOT NULL， Sandwich_ID INT NOT NULL, Sauce_ID INT NOT NULL, 主键(SandwichSauce_ID)， INDEX Sandwich_ID_idx (Sandwich_ID ASC), INDEX Sauce_ID_idx (Sauce_ID ASC), 约束 Sandwich_ID 外键(Sandwich_ID)

#1005 - Can't create table 'usr_web375_4.SandwichSauce' (errno: 121) (Details…) 

我不明白。工作台中没有错误

我不确定它是否与我成功创建的这个表有关:

CREATE TABLE IF NOT EXISTS `usr_web375_4`.`SandwichVegtable` (
  `SandwichVegtable_ID` INT NOT NULL,
  `Sandwich_ID` INT NOT NULL,
  `Vegtable_ID` INT NOT NULL,
  PRIMARY KEY (`SandwichVegtable_ID`),
  INDEX `Sandwich_ID_idx` (`Sandwich_ID` ASC),
  INDEX `Vegtable_ID_idx` (`Vegtable_ID` ASC),
  CONSTRAINT `Sandwich_ID`
    FOREIGN KEY (`Sandwich_ID`)
    REFERENCES `usr_web375_4`.`Sandwich` (`Sandwich_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Vegtable_ID`
    FOREIGN KEY (`Vegtable_ID`)
    REFERENCES `usr_web375_4`.`Vegtable` (`Vegtable_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

Answer 1

您必须在 CREATE TABLE IF NOT EXISTSusr_web375_4.SandwichSauce ... 中添加 Sandwich_ID 和 Sauce_ID 的类型 ...

并且您必须为所有索引和约束使用唯一的名称。

你能试试这个代码吗:

CREATE TABLE IF NOT EXISTS `usr_web375_4`.`SandwichSauce` (
  `SandwichSauce_ID` INT NOT NULL,
  `Sandwich_ID` INT NOT NULL,
  `Sauce_ID` INT NOT NULL,
  PRIMARY KEY (`SandwichSauce_ID`),
  INDEX `Sandwich_ID_idx` (`Sandwich_ID` ASC),
  INDEX `Sauce_ID_idx` (`Sauce_ID` ASC),
  CONSTRAINT `Sandwich_ID_1`
    FOREIGN KEY (`Sandwich_ID`)
    REFERENCES `usr_web375_4`.`Sandwich` (`Sandwich_ID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `Sauce_ID`
    FOREIGN KEY (`Sauce_ID`)
    REFERENCES `usr_web375_4`.`Sauce` (`Sauce_ID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB

或者在工作台中，您可以运行此查询并再次导出数据库:

 ALTER TABLE `SandwichSauce` DROP FOREIGN KEY `Sandwich_ID`;
 ALTER TABLE `SandwichSauce` ADD CONSTRAINT `Sandwich_ID_1` FOREIGN KEY (`Sandwich_ID`) REFERENCES `usr_web375_4`.`Sandwich`(`Sandwich_ID`) ON DELETE CASCADE ON UPDATE CASCADE;