1

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;
  • Are there no details in the error message other than the error number? Can you edit in the complete message? – Andriy M Mar 11 '16 at 15:39
  • Don't your normalization tables need a UNIQUE on the "name"? And it seems like the "name" should never be NULL. – Rick James Mar 12 '16 at 4:47
  • This seemed to help too, I set all the normalization table main columns to UNIQUE which seemed to fix a subsequent errno 150. – Adam G. Mar 14 '16 at 16:22
2

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.

  • Thank you. Yes, that solved the problem for the owner and manufacturer tables. I then had another 150 error on creating the operator table. Setting all the normalization table main columns to UNIQUE seemed to fix this. – Adam G. Mar 14 '16 at 16:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.