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:And the two table definitions are different, one has
NOT NULL
and the otherNULL
:fleet
:manufacturer
:The same appears to be true for
fleet.owner
toowner.owner_name
reference.The column definitions of FK references must be the same.