MySQL 服务器错误 1005

Question

当我尝试运行 SQL 脚本时，我在 Workbench Forward Engineer 中收到日志错误:

Executing SQL script in server
ERROR: Error 1005: Can't create table 'regional_budget.budget' (errno: 121)
SQL Code:
        -- -----------------------------------------------------
        -- Table `regional_budget`.`budget`
        -- -----------------------------------------------------
        CREATE TABLE IF NOT EXISTS `regional_budget`.`budget` (
          `id` INT(3) NOT NULL AUTO_INCREMENT,
          `region_id` INT(3) NOT NULL,
          `balance` DECIMAL(11,2) NULL,
          `income_id` INT(3) NOT NULL,
          `income` DECIMAL(11,2) NULL,
          `outcome_id` INT(3) NOT NULL,
          `outcome` DECIMAL(11,2) NULL,
          PRIMARY KEY (`id`),
          INDEX `region_id_idx` (`region_id` ASC),
          INDEX `outcome_id_idx` (`outcome_id` ASC),
          INDEX `income_id_idx` (`income_id` ASC),
          CONSTRAINT `region_id`
            FOREIGN KEY (`region_id`)
            REFERENCES `regional_budget`.`regions` (`id`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `income_id`
            FOREIGN KEY (`income_id`)
            REFERENCES `regional_budget`.`income` (`id`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `outcome_id`
            FOREIGN KEY (`outcome_id`)
            REFERENCES `regional_budget`.`outcome` (`id`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)
        ENGINE = InnoDB

SQL script execution finished: statements: 13 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

SQL 脚本:

-- 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='TRADITIONAL,ALLOW_INVALID_DATES';

    -- -----------------------------------------------------
    -- Schema regional_budget
    -- -----------------------------------------------------

    -- -----------------------------------------------------
    -- Schema regional_budget
    -- -----------------------------------------------------
    CREATE SCHEMA IF NOT EXISTS `regional_budget` DEFAULT CHARACTER SET utf8 ;
    USE `regional_budget` ;

    -- -----------------------------------------------------
    -- Table `regional_budget`.`regions`
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `regional_budget`.`regions` (
      `id` INT NOT NULL AUTO_INCREMENT,
      `name` VARCHAR(20) NULL,
      PRIMARY KEY (`id`))
    ENGINE = InnoDB;


    -- -----------------------------------------------------
    -- Table `regional_budget`.`subjects`
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `regional_budget`.`subjects` (
      `id` INT NOT NULL,
      `name` VARCHAR(20) NULL,
      `region_id` INT NULL,
      PRIMARY KEY (`id`),
      INDEX `region_id_idx` (`region_id` ASC),
      CONSTRAINT `region_id`
        FOREIGN KEY (`region_id`)
        REFERENCES `regional_budget`.`regions` (`id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;


    -- -----------------------------------------------------
    -- Table `regional_budget`.`income`
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `regional_budget`.`income` (
      `id` INT NOT NULL AUTO_INCREMENT,
      `tax_and_non_tax` DECIMAL(11,2) NULL,
      `receipts` DECIMAL(11,2) NULL,
      PRIMARY KEY (`id`))
    ENGINE = InnoDB;


    -- -----------------------------------------------------
    -- Table `regional_budget`.`outcome`
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `regional_budget`.`outcome` (
      `id` INT NOT NULL AUTO_INCREMENT,
      `transfers` DECIMAL(11,2) NULL,
      `subventions` DECIMAL(11,2) NULL,
      `subsidies` DECIMAL(11,2) NULL,
      `dotations` DECIMAL(11,2) NULL,
      PRIMARY KEY (`id`))
    ENGINE = InnoDB;


    -- -----------------------------------------------------
    -- Table `regional_budget`.`population`
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `regional_budget`.`population` (
      `subject_id` INT NOT NULL,
      `count` DECIMAL(11,2) NULL,
      PRIMARY KEY (`subject_id`),
      CONSTRAINT `subject_id`
        FOREIGN KEY (`subject_id`)
        REFERENCES `regional_budget`.`subjects` (`id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;


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

    -- -----------------------------------------------------
    -- Table `regional_budget`.`budget`
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `regional_budget`.`budget` (
      `id` INT(3) NOT NULL AUTO_INCREMENT,
      `region_id` INT(3) NOT NULL,
      `balance` DECIMAL(11,2) NULL,
      `income_id` INT(3) NOT NULL,
      `income` DECIMAL(11,2) NULL,
      `outcome_id` INT(3) NOT NULL,
      `outcome` DECIMAL(11,2) NULL,
      PRIMARY KEY (`id`),
      INDEX `region_id_idx` (`region_id` ASC),
      INDEX `outcome_id_idx` (`outcome_id` ASC),
      INDEX `income_id_idx` (`income_id` ASC),
      CONSTRAINT `region_id`
        FOREIGN KEY (`region_id`)
        REFERENCES `regional_budget`.`regions` (`id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT `income_id`
        FOREIGN KEY (`income_id`)
        REFERENCES `regional_budget`.`income` (`id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT `outcome_id`
        FOREIGN KEY (`outcome_id`)
        REFERENCES `regional_budget`.`outcome` (`id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;

附注我尝试在其他引用表之后使用外键创建“regional_budget”。“budget”。我还尝试使用foreign_key_checks，但没有任何帮助。

Answer 1

我建议您尝试将外键约束的标识符更改为唯一，并且不要与已用于列(或其他约束)的标识符冲突。

例如:

  CONSTRAINT `FK_budget_regions` FOREIGN KEY ...

  CONSTRAINT `FK_budget_income`  FOREIGN KEY ...

  CONSTRAINT `FK_budget_outcome` FOREIGN KEY ...