MySQL 得到 errno: 150 從 Workbench 創建表

我對 MySQL 沒有經驗，我正在嘗試建構一些表並獲得 errno 150，我認為這可能與外鍵有關。這是 Workbench 生成的我的 SQL。提前感謝您的幫助。

   -- MySQL Script generated by MySQL Workbench
   -- Fri 11 Mar 2016 14:41:32 GMT
   -- Model: Fleet Test 1    Version: 1.0
   -- 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 biz_fleet_test1
   -- -----------------------------------------------------

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

   -- -----------------------------------------------------
   -- Table `biz_fleet_test1`.`manufacturer`
   -- -----------------------------------------------------
   CREATE TABLE IF NOT EXISTS `biz_fleet_test1`.`manufacturer` (
     `manufacturer_id` SMALLINT(6) NOT NULL,
     `manufacturer_name` VARCHAR(255) NULL,
     PRIMARY KEY (`manufacturer_id`))
   ENGINE = InnoDB;


   -- -----------------------------------------------------
   -- Table `biz_fleet_test1`.`owner`
   -- -----------------------------------------------------
   CREATE TABLE IF NOT EXISTS `biz_fleet_test1`.`owner` (
     `owner_id` SMALLINT(6) NOT NULL,
     `owner_name` VARCHAR(255) NULL,
     PRIMARY KEY (`owner_id`))
   ENGINE = InnoDB;


   -- -----------------------------------------------------
   -- Table `biz_fleet_test1`.`operator_type`
   -- -----------------------------------------------------
   CREATE TABLE IF NOT EXISTS `biz_fleet_test1`.`operator_type` (
     `operator_type_id` SMALLINT(6) NOT NULL,
     `operator_type` VARCHAR(255) NOT NULL,
     PRIMARY KEY (`operator_type_id`))
   ENGINE = InnoDB;


   -- -----------------------------------------------------
   -- Table `biz_fleet_test1`.`group`
   -- -----------------------------------------------------
   CREATE TABLE IF NOT EXISTS `biz_fleet_test1`.`group` (
     `group_id` SMALLINT(6) NOT NULL,
     `group_name` VARCHAR(255) NULL,
     PRIMARY KEY (`group_id`))
   ENGINE = InnoDB;


   -- -----------------------------------------------------
   -- Table `biz_fleet_test1`.`operator`
   -- -----------------------------------------------------
   CREATE TABLE IF NOT EXISTS `biz_fleet_test1`.`operator` (
     `operator_id` SMALLINT(6) NOT NULL,
     `operator_name` VARCHAR(255) NOT NULL,
     `operator_type` VARCHAR(255) NOT NULL,
     `operator_group` VARCHAR(255) NULL,
     PRIMARY KEY (`operator_id`),
     INDEX `fk_operator_operator_type_idx` (`operator_type` ASC),
     INDEX `fk_operator_group1_idx` (`operator_group` ASC),
     CONSTRAINT `fk_operator_operator_type`
       FOREIGN KEY (`operator_type`)
       REFERENCES `biz_fleet_test1`.`operator_type` (`operator_type`)
       ON DELETE NO ACTION
       ON UPDATE NO ACTION,
     CONSTRAINT `fk_operator_group1`
       FOREIGN KEY (`operator_group`)
       REFERENCES `biz_fleet_test1`.`group` (`group_name`)
       ON DELETE NO ACTION
       ON UPDATE NO ACTION)
   ENGINE = InnoDB;


   -- -----------------------------------------------------
   -- Table `biz_fleet_test1`.`franchise`
   -- -----------------------------------------------------
   CREATE TABLE IF NOT EXISTS `biz_fleet_test1`.`franchise` (
     `franchise_id` SMALLINT(6) NOT NULL,
     `franchise_name` VARCHAR(255) NOT NULL,
     `franchise_operator` VARCHAR(255) NOT NULL,
     PRIMARY KEY (`franchise_id`),
     INDEX `fk_franchise_operator1_idx` (`franchise_operator` ASC),
     CONSTRAINT `fk_franchise_operator1`
       FOREIGN KEY (`franchise_operator`)
       REFERENCES `biz_fleet_test1`.`operator` (`operator_name`)
       ON DELETE NO ACTION
       ON UPDATE NO ACTION)
   ENGINE = InnoDB;


   -- -----------------------------------------------------
   -- Table `biz_fleet_test1`.`fleet`
   -- -----------------------------------------------------
   CREATE TABLE IF NOT EXISTS `biz_fleet_test1`.`fleet` (
     `fleet_id` SMALLINT(6) NOT NULL,
     `fleet_main_number` SMALLINT(6) NOT NULL,
     `fleet_sub_number` TINYINT(4) NULL,
     `fleet_split_letter` CHAR(1) NULL,
     `manufacturer` VARCHAR(255) NOT NULL,
     `owner` VARCHAR(255) NOT NULL,
     `franchise` VARCHAR(255) NOT NULL,
     PRIMARY KEY (`fleet_id`),
     INDEX `fk_fleet_manufacturer1_idx` (`manufacturer` ASC),
     INDEX `fk_fleet_owner1_idx` (`owner` ASC),
     INDEX `fk_fleet_franchise1_idx` (`franchise` ASC),
     CONSTRAINT `fk_fleet_manufacturer1`
       FOREIGN KEY (`manufacturer`)
       REFERENCES `biz_fleet_test1`.`manufacturer` (`manufacturer_name`)
       ON DELETE NO ACTION
       ON UPDATE NO ACTION,
     CONSTRAINT `fk_fleet_owner1`
       FOREIGN KEY (`owner`)
       REFERENCES `biz_fleet_test1`.`owner` (`owner_name`)
       ON DELETE NO ACTION
       ON UPDATE NO ACTION,
     CONSTRAINT `fk_fleet_franchise1`
       FOREIGN KEY (`franchise`)
       REFERENCES `biz_fleet_test1`.`franchise` (`franchise_name`)
       ON DELETE NO ACTION
       ON UPDATE NO ACTION)
   ENGINE = InnoDB;

   CREATE USER 'biz' IDENTIFIED BY 'password';

   GRANT ALL ON `biz_fleet_test1`.* TO 'biz';
   GRANT SELECT, INSERT, TRIGGER ON TABLE `biz_fleet_test1`.* TO 'biz';
   GRANT SELECT, INSERT, TRIGGER, UPDATE, DELETE ON TABLE `biz_fleet_test1`.* TO 'biz';
   GRANT EXECUTE ON ROUTINE `biz_fleet_test1`.* TO 'biz';
   GRANT SELECT ON TABLE `biz_fleet_test1`.* TO 'biz';

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

這可能是因為您的fleet表具有以下 FK：

CONSTRAINT `fk_fleet_manufacturer1`
   FOREIGN KEY (`manufacturer`)
   REFERENCES `biz_fleet_test1`.`manufacturer` (`manufacturer_name`)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION

並且這兩個表定義是不同的，一個 hasNOT NULL和另一個NULL：

fleet:

`manufacturer` VARCHAR(255) NOT NULL,

manufacturer:

`manufacturer_name` VARCHAR(255) NULL,

fleet.owner供參考似乎也是如此owner.owner_name。

FK 引用的列定義必須相同。

引用自：https://dba.stackexchange.com/questions/131981