title: Failed to add the foreign key constraint. Missing column 'gym_id' for constraint 'fk_gyms_instructors_gyms' in the referenced table 'gyms'
body: <p>I'm forward engineering a database from an ERD that I created in MySqlWorkbench. I have a very basic many to many relationship between the "gyms" table and the "instructors" table, but I'm getting the error above. To me, the error reads that MySql can't find the "gym_id" column in the "gyms" table when it tries to create the many-to-many table. 
  I don't know why that would be the case.</p>
  
  <p>I've went through the tables to make sure there were no typos and that the data types matched. I also went through the execution script to make sure the "gyms" table was being created before the many-to-many. It was.  I can't sniff out what is causing the error. So any help or suggestion is appreciated </p>
  
  <p>This is the error: </p>
  
  <pre><code>Executing SQL script in server
  ERROR: Error 3734: Failed to add the foreign key constraint. Missing column 'gym_id' for constraint 'fk_gyms_instructors_gyms' in the referenced table 'gyms'
  SQL Code:
          -- -----------------------------------------------------
          -- Table `fitness`.`gyms_instructors`
          -- -----------------------------------------------------
          CREATE TABLE IF NOT EXISTS `fitness`.`gyms_instructors` (
            `id` INT NOT NULL AUTO_INCREMENT,
            `gym_id` SMALLINT NOT NULL,
            `instructor_id` SMALLINT NOT NULL,
            PRIMARY KEY (`id`),
            INDEX `fk_gyms_instructors_gyms_idx` (`gym_id` ASC) VISIBLE,
            INDEX `fk_gyms_instructors_instructors_idx` (`instructor_id` ASC) VISIBLE,
            CONSTRAINT `fk_gyms_instructors_gyms`
              FOREIGN KEY (`gym_id`)
              REFERENCES `fitness`.`gyms` (`gym_id`)
              ON DELETE NO ACTION
              ON UPDATE CASCADE,
            CONSTRAINT `fk_gyms_instructors_instructors`
              FOREIGN KEY (`instructor_id`)
              REFERENCES `fitness`.`instructors` (`instructor_id`)
              ON DELETE NO ACTION
              ON UPDATE CASCADE)
          ENGINE = InnoDB
  
  SQL script execution finished: statements: 10 succeeded, 1 failed
  
  Fetching back view definitions in final form.
  Nothing to fetch
  
  </code></pre>
  
  <p>The other potentially useful information is:
  1. On the many to many table, in the foreign keys section both gym_id and instructor_id are SMALLINTS and NOT NULL, 
  2. On their respective table the gym_id and instructor_id are autoincrementing primary keys, NOT NULL
  3. I pasted the full script below if you want to look.</p>
  
  <pre><code>
  -- 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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
  
  -- -----------------------------------------------------
  -- Schema fitness
  -- -----------------------------------------------------
  
  -- -----------------------------------------------------
  -- Schema fitness
  -- -----------------------------------------------------
  CREATE SCHEMA IF NOT EXISTS `fitness` DEFAULT CHARACTER SET utf8 ;
  USE `fitness` ;
  
  -- -----------------------------------------------------
  -- Table `fitness`.`fitness_classes`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `fitness`.`fitness_classes` (
    `fitness_class_id` INT NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(45) NOT NULL,
    `price` DECIMAL(2,2) NOT NULL,
    `description` VARCHAR(500) NOT NULL,
    `vacancies` INT NOT NULL,
    `start_time` DATETIME NOT NULL,
    PRIMARY KEY (`fitness_class_id`))
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `fitness`.`categories`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `fitness`.`categories` (
    `category_id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(45) NOT NULL,
    `description` TEXT(1200) NULL,
    PRIMARY KEY (`category_id`))
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `fitness`.`tags`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `fitness`.`tags` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `fitness_class_id` INT NOT NULL,
    `category_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_tags_fitness_classes_idx` (`fitness_class_id` ASC) VISIBLE,
    INDEX `fk_tags_categories_idx` (`category_id` ASC) VISIBLE,
    CONSTRAINT `fk_tags_fitness_classes`
      FOREIGN KEY (`fitness_class_id`)
      REFERENCES `fitness`.`fitness_classes` (`fitness_class_id`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
    CONSTRAINT `fk_tags_categories`
      FOREIGN KEY (`category_id`)
      REFERENCES `fitness`.`categories` (`category_id`)
      ON DELETE NO ACTION
      ON UPDATE CASCADE)
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `fitness`.`gyms`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `fitness`.`gyms` (
    `gym_id` SMALLINT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(55) NOT NULL,
    `address` VARCHAR(55) NOT NULL,
    `city` VARCHAR(25) NOT NULL,
    `state` VARCHAR(25) NOT NULL,
    `phone` VARCHAR(10) NOT NULL,
    `latitude` DECIMAL(10,8) NULL,
    `longitude` DECIMAL(10,8) NULL,
    `neighborhood` VARCHAR(45) NOT NULL,
    `create_time` TIMESTAMP NOT NULL,
    PRIMARY KEY (`gym_id`))
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `fitness`.`instructors`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `fitness`.`instructors` (
    `instructor_id` SMALLINT NOT NULL AUTO_INCREMENT,
    `first_name` VARCHAR(55) NOT NULL,
    `last_name` VARCHAR(55) NOT NULL,
    `create_time` TIMESTAMP NOT NULL,
    PRIMARY KEY (`instructor_id`))
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `fitness`.`gyms_instructors`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `fitness`.`gyms_instructors` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `gym_id` SMALLINT NOT NULL,
    `instructor_id` SMALLINT NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_gyms_instructors_gyms_idx` (`gym_id` ASC) VISIBLE,
    INDEX `fk_gyms_instructors_instructors_idx` (`instructor_id` ASC) VISIBLE,
    CONSTRAINT `fk_gyms_instructors_gyms`
      FOREIGN KEY (`gym_id`)
      REFERENCES `fitness`.`gyms` (`gym_id`)
      ON DELETE NO ACTION
      ON UPDATE CASCADE,
    CONSTRAINT `fk_gyms_instructors_instructors`
      FOREIGN KEY (`instructor_id`)
      REFERENCES `fitness`.`instructors` (`instructor_id`)
      ON DELETE NO ACTION
      ON UPDATE CASCADE)
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `fitness`.`listings`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `fitness`.`listings` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `gym_id` SMALLINT NOT NULL,
    `fitness_class_id` INT NOT NULL,
    `instructor_id` SMALLINT NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_listings_gym_idx` (`gym_id` ASC) VISIBLE,
    INDEX `fk_listings_fitness_class_idx` (`fitness_class_id` ASC) VISIBLE,
    INDEX `fk_listings_instructor_idx` (`instructor_id` ASC) VISIBLE,
    CONSTRAINT `fk_listings_gym`
      FOREIGN KEY (`gym_id`)
      REFERENCES `fitness`.`gyms` (`gym_id`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
    CONSTRAINT `fk_listings_fitness_class`
      FOREIGN KEY (`fitness_class_id`)
      REFERENCES `fitness`.`fitness_classes` (`fitness_class_id`)
      ON DELETE NO ACTION
      ON UPDATE CASCADE,
    CONSTRAINT `fk_listings_instructor`
      FOREIGN KEY (`instructor_id`)
      REFERENCES `fitness`.`instructors` (`instructor_id`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `fitness`.`users`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `fitness`.`users` (
    `user_id` INT NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(255) NOT NULL,
    `password` VARCHAR(32) NOT NULL,
    `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `username` VARCHAR(16) NOT NULL,
    PRIMARY KEY (`user_id`));
  
  
  -- -----------------------------------------------------
  -- Table `fitness`.`reviews`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `fitness`.`reviews` (
    `review_id` INT NOT NULL AUTO_INCREMENT,
    `fitness_class_id` INT NOT NULL,
    `rating` TINYINT(1) NOT NULL,
    `review_text` TEXT(1200) NULL,
    `create_time` TIMESTAMP NOT NULL,
    PRIMARY KEY (`review_id`),
    INDEX `fk_reviews_classes_idx` (`fitness_class_id` ASC) VISIBLE,
    CONSTRAINT `fk_reviews_classes`
      FOREIGN KEY (`fitness_class_id`)
      REFERENCES `fitness`.`fitness_classes` (`fitness_class_id`)
      ON DELETE NO ACTION
      ON UPDATE CASCADE)
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `fitness`.`bookings`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `fitness`.`bookings` (
    `booking_id` INT NOT NULL AUTO_INCREMENT,
    `fitness_class_id` INT NOT NULL,
    `user_id` INT NOT NULL,
    `price` DECIMAL(2,2) NOT NULL,
    `class_start_time` DATETIME NOT NULL,
    `purchase_date` DATETIME NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`booking_id`),
    CONSTRAINT `user_id`
      FOREIGN KEY ()
      REFERENCES `fitness`.`users` ()
      ON DELETE NO ACTION
      ON UPDATE CASCADE,
    CONSTRAINT `fitness_class_id`
      FOREIGN KEY ()
      REFERENCES `fitness`.`fitness_classes` ()
      ON DELETE NO ACTION
      ON UPDATE CASCADE)
  ENGINE = InnoDB;
  
  
  SET SQL_MODE=@OLD_SQL_MODE;
  SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
  SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
  
  
  </code></pre>