错误:错误#1215:无法添加外键约束

Question

这是当我试图将我的模式转发到我的数据库时遇到的错误。

错误:错误1215:无法添加外键约束。

我已经列出了这份清单，我相信这些都没有。

Db是InnoDB吗？是

所有的桌子都是InnoDB吗？是

引用表上是否存在唯一索引？是

引用和引用列是否完全相同？是

我也尝试过：

ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1; 

在保修表的末尾。还是不起作用。

这是完整的脚本，因为我确实认为它位于保证表的某个地方，但是我尝试的所有东西都不起作用。

---------------------------------------------------------------------------

-- 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 mydb
-- -----------------------------------------------------

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

-- -----------------------------------------------------
-- Table `mydb`.`warranty`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`warranty` ;

CREATE TABLE IF NOT EXISTS `mydb`.`warranty` (
  `warranty_id` VARCHAR(255) NOT NULL,
  `warranty_number` VARCHAR(25) NOT NULL COMMENT 'Warranty Number',
  `warranty_effective_date` DATETIME NOT NULL COMMENT 'Warranty Effective Date',
  `payment_option` VARCHAR(100) NOT NULL COMMENT 'Pay monthly or annunally, or all at once',
  `total_amount` DOUBLE NOT NULL COMMENT 'Total Warranty Amount\n',
  `active` TINYINT(1) NOT NULL,
  `date_created` DATETIME NOT NULL,
  `additional_information` LONGTEXT NULL COMMENT 'Additional information for finance, and car dealership.\nWill not be seen by customer',
  PRIMARY KEY (`warranty_id`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`warranty_edit`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`warranty_edit` ;

CREATE TABLE IF NOT EXISTS `mydb`.`warranty_edit` (
  `warranty_edit_id` VARCHAR(255) NOT NULL,
  `fk_warranty_id_3` VARCHAR(255) NOT NULL COMMENT 'Foreign Key to warranty table',
  `edited_table_name` VARCHAR(45) NOT NULL COMMENT 'Name of the table that was updated',
  `edited_date` DATETIME NOT NULL COMMENT 'When record is inserted into DB',
  `edited_by` VARCHAR(45) NOT NULL COMMENT 'Who updated the records',
  `additional_information` LONGTEXT NULL COMMENT 'Extra information if needed',
  PRIMARY KEY (`warranty_edit_id`),
  INDEX `policy_id_idx` (`fk_warranty_id_3` ASC),
  CONSTRAINT `warranty_id`
    FOREIGN KEY (`fk_warranty_id_3`)
    REFERENCES `mydb`.`warranty` (`warranty_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`bill`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`bill` ;

CREATE TABLE IF NOT EXISTS `mydb`.`bill` (
  `bill_id` VARCHAR(255) NOT NULL,
  `fk_warranty_id_1` VARCHAR(255) NOT NULL COMMENT 'Foreign Key of bill table',
  `due_date` DATETIME NOT NULL COMMENT 'Payment due date',
  `minimum_payment` DOUBLE NOT NULL COMMENT 'Minimum payment or per bill payment',
  `created_date` DATETIME NOT NULL COMMENT 'Bill generated date',
  `balance` DOUBLE NOT NULL COMMENT 'Customer makes partial payment, this has remaining balance',
  `status` VARCHAR(45) NOT NULL COMMENT 'Paid, or Unpaid',
  PRIMARY KEY (`bill_id`),
  INDEX `warranty_id_idx` (`fk_warranty_id_1` ASC),
  CONSTRAINT `warranty_id`
    FOREIGN KEY (`fk_warranty_id_1`)
    REFERENCES `mydb`.`warranty` (`warranty_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`vehicle`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`vehicle` ;

CREATE TABLE IF NOT EXISTS `mydb`.`vehicle` (
  `vehicle_id` VARCHAR(255) NOT NULL COMMENT 'Foreign Key to Warranty Table',
  `fk_warranty_id_2` VARCHAR(255) NOT NULL,
  `year` CHAR(4) NOT NULL COMMENT 'Year of Car',
  `make` VARCHAR(45) NOT NULL,
  `model` VARCHAR(45) NOT NULL COMMENT 'Model',
  `color` VARCHAR(45) NULL COMMENT 'Color',
  `trim` VARCHAR(45) NULL COMMENT 'Trim',
  `milage` INT NOT NULL COMMENT 'Mileage',
  `vin_number` VARCHAR(20) NOT NULL COMMENT 'VIN Number',
  `vehicle_plate_number` VARCHAR(20) NOT NULL COMMENT 'Registered Plate',
  `vehicle_registered_state` VARCHAR(45) NOT NULL COMMENT 'Registered State',
  `aftermarket_parts` VARCHAR(45) NULL COMMENT 'After Market Parts Not Included (Information)',
  `created_date` DATETIME NOT NULL COMMENT 'Created Date',
  PRIMARY KEY (`vehicle_id`),
  INDEX `warranty_id_idx` (`fk_warranty_id_2` ASC),
  CONSTRAINT `warranty_id`
    FOREIGN KEY (`fk_warranty_id_2`)
    REFERENCES `mydb`.`warranty` (`warranty_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`coverage`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`coverage` ;

CREATE TABLE IF NOT EXISTS `mydb`.`coverage` (
  `coverage_id` VARCHAR(255) NOT NULL,
  `coverage_package` VARCHAR(45) NOT NULL COMMENT 'Name of the package selected',
  `fk_vehicle_id` VARCHAR(255) NOT NULL,
  `detuctible` FLOAT NOT NULL,
  `terms` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`coverage_id`),
  INDEX `vehicle_id_idx` (`fk_vehicle_id` ASC),
  CONSTRAINT `vehicle_id`
    FOREIGN KEY (`fk_vehicle_id`)
    REFERENCES `mydb`.`vehicle` (`vehicle_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`warranty_coverage`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`warranty_coverage` ;

CREATE TABLE IF NOT EXISTS `mydb`.`warranty_coverage` (
  `warranty_coverage_id` VARCHAR(255) NOT NULL,
  `fk_warranty_id_4` VARCHAR(255) NOT NULL,
  `fk_coverage_id` VARCHAR(255) NOT NULL,
  `active` TINYINT(1) NOT NULL,
  PRIMARY KEY (`warranty_coverage_id`),
  INDEX `warranty_id_idx` (`fk_warranty_id_4` ASC),
  INDEX `coverage_id_idx` (`fk_coverage_id` ASC),
  CONSTRAINT `warranty_id`
    FOREIGN KEY (`fk_warranty_id_4`)
    REFERENCES `mydb`.`warranty` (`warranty_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `coverage_id`
    FOREIGN KEY (`fk_coverage_id`)
    REFERENCES `mydb`.`coverage` (`coverage_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`payment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`payment` ;

CREATE TABLE IF NOT EXISTS `mydb`.`payment` (
  `payment_id` VARCHAR(255) NOT NULL,
  `fk_bill_id` VARCHAR(255) NOT NULL COMMENT 'Foreign Key to bill table',
  `paid_date` DATETIME NOT NULL COMMENT 'Payment made date',
  `amount` DOUBLE NOT NULL COMMENT 'How much is paid',
  `payment_method` VARCHAR(100) NOT NULL COMMENT 'Credit / Debit / Check / E-Withdrawl',
  `payer_first_name` VARCHAR(45) NULL,
  `payer_last_name` VARCHAR(45) NULL COMMENT 'Payer Last Name',
  `card_number` VARCHAR(45) NULL COMMENT 'Credit or Debit Card Number',
  `zip_code` VARCHAR(10) NULL,
  `card_expiration_date` VARCHAR(10) NULL COMMENT 'Expiration Date of Credit Card',
  `card_type` VARCHAR(25) NULL COMMENT 'Visa, Mastercard, American Express, Discover, other',
  `debit_or_credit` VARCHAR(45) NULL COMMENT 'Is the card debit or credit',
  `bank_name` VARCHAR(100) NULL COMMENT 'Bank Name for E-Withdrawal',
  `account_number` VARCHAR(20) NULL COMMENT 'Account Number of Payer',
  `routing_number` VARCHAR(20) NULL COMMENT 'Bank Routing Number',
  `check_number` VARCHAR(20) NULL,
  `addition_information` LONGTEXT NULL,
  `created_date` DATETIME NOT NULL COMMENT 'When the payment is created',
  PRIMARY KEY (`payment_id`),
  INDEX `bill_id_idx` (`fk_bill_id` ASC),
  CONSTRAINT `bill_id`
    FOREIGN KEY (`fk_bill_id`)
    REFERENCES `mydb`.`bill` (`bill_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = ' ';


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

Answer 1

通过在MySQL CLI中运行此命令并滚动到LATEST FOREIGN KEY ERROR部分，您可以找到特定的错误：

SHOW ENGINE INNODB STATUS\G

每个外键约束都需要有一个唯一的名称。在您的示例中，有4个外键约束具有完全相同的名称(warranty_id)，因此除第一个外，所有CREATE TABLE语句都将失败。