Search code examples
mysqldatabasemysql-error-150

Error: 150 / MySQL 'Can't create table'

So I made a database model through the model creator in MySQL Workbench. However, I'm running into an error code when I try to convert it to an SQL file. The error says

Can't create table project2i2w.customer (error: 150 "Foreign key constraint is incorrectly formed") 0.032 sec.

Here is my script:

-- 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 project2i2w
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `project2i2w` ;

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

-- -----------------------------------------------------
-- Table `project2i2w`.`services`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `project2i2w`.`services` (
  `service_id` INT NOT NULL AUTO_INCREMENT,
  `service_name` VARCHAR(50) NOT NULL,
  `service_desc` VARCHAR(250) NOT NULL,
  `os_id` INT NOT NULL,
  `customer_id` INT NOT NULL,
  PRIMARY KEY (`service_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `project2i2w`.`customer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `project2i2w`.`customer` (
  `customer_id` INT NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(45) NOT NULL,
  `last_name` VARCHAR(45) NOT NULL,
  `city` VARCHAR(45) NOT NULL,
  `street` VARCHAR(20) NOT NULL,
  `province` VARCHAR(30) NOT NULL,
  `postal` CHAR(7) NOT NULL,
  PRIMARY KEY (`customer_id`),
  CONSTRAINT `fk_customer_services`
    FOREIGN KEY (`customer_id`)
    REFERENCES `project2i2w`.`services` (`customer_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `project2i2w`.`op_sys`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `project2i2w`.`op_sys` (
  `os_id` INT NOT NULL AUTO_INCREMENT,
  `os_name` VARCHAR(25) NOT NULL,
  `os_desc` VARCHAR(250) NULL,
  `os_ver` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`os_id`),
  CONSTRAINT `fk_op_sys_services1`
    FOREIGN KEY (`os_id`)
    REFERENCES `project2i2w`.`services` (`os_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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

-- -----------------------------------------------------
-- Data for table `project2i2w`.`services`
-- -----------------------------------------------------
START TRANSACTION;
USE `project2i2w`;
INSERT INTO `project2i2w`.`services` (`service_id`, `service_name`, `service_desc`, `os_id`, `customer_id`) VALUES (1, 'Virus Removal', 'Removing of viruses.', 1, 1);
INSERT INTO `project2i2w`.`services` (`service_id`, `service_name`, `service_desc`, `os_id`, `customer_id`) VALUES (2, 'Tune-up', 'Tuning system performance.', 2, 2);
INSERT INTO `project2i2w`.`services` (`service_id`, `service_name`, `service_desc`, `os_id`, `customer_id`) VALUES (3, 'Rooting', 'Accessing super-user status.', 3, 3);
INSERT INTO `project2i2w`.`services` (`service_id`, `service_name`, `service_desc`, `os_id`, `customer_id`) VALUES (4, 'Restore', 'Restoring to stock factory condition.', 4, 4);
INSERT INTO `project2i2w`.`services` (`service_id`, `service_name`, `service_desc`, `os_id`, `customer_id`) VALUES (5, 'Fix Permissions', 'Fixing file permissions.', 5, 5);

COMMIT;


-- -----------------------------------------------------
-- Data for table `project2i2w`.`customer`
-- -----------------------------------------------------
START TRANSACTION;
USE `project2i2w`;
INSERT INTO `project2i2w`.`customer` (`customer_id`, `first_name`, `last_name`, `city`, `street`, `province`, `postal`) VALUES (1, 'Kathryn', 'Trollinger', 'Kanata', '2337 Merivale Road', 'ON', 'K2K 1L9');
INSERT INTO `project2i2w`.`customer` (`customer_id`, `first_name`, `last_name`, `city`, `street`, `province`, `postal`) VALUES (2, 'Lori ', 'Frederick', 'Toronto', '799 Yonge Street', 'ON', 'M4W 1J7');
INSERT INTO `project2i2w`.`customer` (`customer_id`, `first_name`, `last_name`, `city`, `street`, `province`, `postal`) VALUES (3, 'Maxine ', 'Coley', 'Ingersoll', '1305 Albert Street', 'ON', 'N5C 1S2');
INSERT INTO `project2i2w`.`customer` (`customer_id`, `first_name`, `last_name`, `city`, `street`, `province`, `postal`) VALUES (4, 'June ', 'Harrison', 'Windsor', '1089 Goyeau Ave', 'ON', 'N9A 1H9');
INSERT INTO `project2i2w`.`customer` (`customer_id`, `first_name`, `last_name`, `city`, `street`, `province`, `postal`) VALUES (5, 'Joan ', 'Dunn', 'Oshawa', '615 Toy Avenue', 'ON', 'L1H 7M3');

COMMIT;


-- -----------------------------------------------------
-- Data for table `project2i2w`.`op_sys`
-- -----------------------------------------------------
START TRANSACTION;
USE `project2i2w`;
INSERT INTO `project2i2w`.`op_sys` (`os_id`, `os_name`, `os_desc`, `os_ver`) VALUES (1, 'Windows', 'OS by Microsoft', '10');
INSERT INTO `project2i2w`.`op_sys` (`os_id`, `os_name`, `os_desc`, `os_ver`) VALUES (2, 'Mac OSX', 'OS by Apple', 'El Capitan');
INSERT INTO `project2i2w`.`op_sys` (`os_id`, `os_name`, `os_desc`, `os_ver`) VALUES (3, 'Android', 'OS by Google', 'Marshmellow');
INSERT INTO `project2i2w`.`op_sys` (`os_id`, `os_name`, `os_desc`, `os_ver`) VALUES (4, 'iOS', 'OS by Apple', '10');
INSERT INTO `project2i2w`.`op_sys` (`os_id`, `os_name`, `os_desc`, `os_ver`) VALUES (5, 'Linux', 'Open-source OS', 'Ubuntu');

COMMIT;

If someone could fix this for me and tell me why it's not working, I would immensely apreciate it.

Nick

Solution

  • You need some kind of key for customer_id, that will do the trick.

    CREATE TABLE IF NOT EXISTS `project2i2w`.`services` (
`service_id` INT NOT NULL AUTO_INCREMENT,
`service_name` VARCHAR(50) NOT NULL,
`service_desc` VARCHAR(250) NOT NULL,
`os_id` INT NOT NULL,
`customer_id` INT NOT NULL,
 INDEX `customerid` (`customer_id`),
PRIMARY KEY (`service_id`))
ENGINE = InnoDB;

    -- Table project2i2w.customer

    CREATE TABLE IF NOT EXISTS `project2i2w`.`customer` (
`customer_id` INT NOT NULL AUTO_INCREMENT,
`first_name` VARCHAR(45) NOT NULL,
`last_name` VARCHAR(45) NOT NULL,
`city` VARCHAR(45) NOT NULL,
`street` VARCHAR(20) NOT NULL,
`province` VARCHAR(30) NOT NULL,
`postal` CHAR(7) NOT NULL,
PRIMARY KEY (`customer_id`),
CONSTRAINT `fk_customer_services`
 FOREIGN KEY (`customer_id`)
 REFERENCES `project2i2w`.`services` (`customer_id`)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION)
  ENGINE = InnoDB;

    Try this