MySQL Workbench – Fixing errno: 150 on Table Creation

foreign keyMySQLmysql-workbench

I'm inexperienced with MySQL and I am trying to build a few tables and getting errno 150 which I believe could be to do with Foreign Keys. Here is my SQL as generated by Workbench. Thanks in advance for any help.

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

Best Answer

This is likely because your fleet table has the following FK:

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

And the two table definitions are different, one has NOT NULL and the other NULL:

fleet:

`manufacturer` VARCHAR(255) NOT NULL,

manufacturer:

`manufacturer_name` VARCHAR(255) NULL,

The same appears to be true for fleet.owner to owner.owner_name reference.

The column definitions of FK references must be the same.