ERROR 1005 (HY000): Can't create table?

Question

I have read through this about a billion times and I cannot figure it out... I generated this in workbench so it should work but it creates all of the tables except for SECTIONS... it will create it if i remove the relationship between SECTION and INSTRUCTOR but I need that relationship to work.... Any help would be greatly appreciated! code is below... Hopefully someone can help me! Thanks!

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

CREATE SCHEMA IF NOT EXISTS `385_s11_turpinvp` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `385_s11_turpinvp` ;

-- -----------------------------------------------------
-- Table `385_s11_turpinvp`.`TUTORS`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `385_s11_turpinvp`.`TUTORS` (
  `Name` VARCHAR(45) NOT NULL ,
  `Banner_Id` CHAR(8) NOT NULL ,
  `Email` VARCHAR(45) NOT NULL ,
  `Ssn` CHAR(9) NOT NULL ,
  `Address` VARCHAR(45) NOT NULL ,
  `Phone` INT NOT NULL ,
  PRIMARY KEY (`Banner_Id`) )
ENGINE = InnoDB;

CREATE UNIQUE INDEX `Ssn_UNIQUE` ON `385_s11_turpinvp`.`TUTORS` (`Ssn` ASC) ;

CREATE UNIQUE INDEX `Banner_Id_UNIQUE` ON `385_s11_turpinvp`.`TUTORS` (`Banner_Id` ASC) ;


-- -----------------------------------------------------
-- Table `385_s11_turpinvp`.`STUDENTS`
-    - -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `385_s11_turpinvp`.`STUDENTS` (
  `Name` VARCHAR(45) NOT NULL ,
  `Banner_Id` CHAR(8) NOT NULL ,
  `Email` VARCHAR(45) NOT NULL ,
  `Phone` INT NOT NULL ,
  PRIMARY KEY (`Banner_Id`) )
ENGINE = InnoDB;

CREATE UNIQUE INDEX `Banner_Id_UNIQUE` ON `385_s11_turpinvp`.`STUDENTS` (`Banner_Id` ASC) ;


-- -----------------------------------------------------
-- Table `385_s11_turpinvp`.`INSTRUCTORS`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `385_s11_turpinvp`.`INSTRUCTORS` (
  `Name` VARCHAR(45) NOT NULL ,
  `Banner_Id` CHAR(8) NOT NULL ,
  `Email` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`Banner_Id`, `Name`) )
ENGINE = InnoDB;

CREATE UNIQUE INDEX `Banner_Id_UNIQUE` ON `385_s11_turpinvp`.`INSTRUCTORS` (`Banner_Id` ASC) ;


-- -----------------------------------------------------
-- Table `385_s11_turpinvp`.`COURSES`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `385_s11_turpinvp`.`COURSES` (
  `Course_Id` VARCHAR(45) NOT NULL ,
  `Course_Name` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`Course_Id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `385_s11_turpinvp`.`APPOINTMENTS`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `385_s11_turpinvp`.`APPOINTMENTS` (
  `Courses_Course_Id` VARCHAR(45) NOT NULL ,
  `TUTORS_Banner_Id` CHAR(8) NOT NULL ,
  `STUDENTS_Banner_Id` CHAR(8) NOT NULL ,
      `Start_Time` DATETIME NOT NULL ,
  `End_Time` DATETIME NOT NULL ,
  PRIMARY KEY (`Courses_Course_Id`, `TUTORS_Banner_Id`, `STUDENTS_Banner_Id`, `Start_Time`, `End_Time`) ,
  CONSTRAINT `fk_COURSES_has_TUTORS_Courses1`
    FOREIGN KEY (`Courses_Course_Id` )
    REFERENCES `385_s11_turpinvp`.`COURSES` (`Course_Id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_COURSES_has_TUTORS_TUTORS1`
    FOREIGN KEY (`TUTORS_Banner_Id` )
    REFERENCES `385_s11_turpinvp`.`TUTORS` (`Banner_Id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_APPOINTMENTS_STUDENTS1`
    FOREIGN KEY (`STUDENTS_Banner_Id` )
    REFERENCES `385_s11_turpinvp`.`STUDENTS` (`Banner_Id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `fk_COURSES_has_TUTORS_TUTORS1` ON `385_s11_turpinvp`.`APPOINTMENTS` (`TUTORS_Banner_Id` ASC) ;

CREATE INDEX `fk_COURSES_has_TUTORS_Courses1` ON `385_s11_turpinvp`.`APPOINTMENTS` (`Courses_Course_Id` ASC) ;

CREATE INDEX `fk_APPOINTMENTS_STUDENTS1` ON `385_s11_turpinvp`.`APPOINTMENTS` (`STUDENTS_Banner_Id` ASC) ;


-- -----------------------------------------------------
-- Table `385_s11_turpinvp`.`QUALIFIED`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `385_s11_turpinvp`.`QUALIFIED` (
  `Courses_Course_Id` VARCHAR(45) NOT NULL ,
  `TUTORS_Banner_Id` CHAR(8) NOT NULL ,
  PRIMARY KEY (`Courses_Course_Id`, `TUTORS_Banner_Id`) ,
  CONSTRAINT `fk_Courses_has_TUTORS_Courses2`
    FOREIGN KEY (`Courses_Course_Id` )
    REFERENCES `385_s11_turpinvp`.`COURSES` (`Course_Id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Courses_has_TUTORS_TUTORS2`
    FOREIGN KEY (`TUTORS_Banner_Id` )
    REFERENCES `385_s11_turpinvp`.`TUTORS` (`Banner_Id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `fk_Courses_has_TUTORS_TUTORS2` ON `385_s11_turpinvp`.`QUALIFIED` (`TUTORS_Banner_Id` ASC) ;

CREATE INDEX `fk_Courses_has_TUTORS_Courses2` ON `385_s11_turpinvp`.`QUALIFIED` (`Courses_Course_Id` ASC) ;


-- -----------------------------------------------------
-- Table `385_s11_turpinvp`.`Availability`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `385_s11_turpinvp`.`Availability` (
  `Start_Time` DATETIME NOT NULL ,
  `End_time` DATETIME NOT NULL ,
  `TUTORS_Banner_Id` CHAR(8) NOT NULL ,
  PRIMARY KEY (`TUTORS_Banner_Id`, `End_time`, `Start_Time`) ,
  CONSTRAINT `fk_Availability_TUTORS1`
    FOREIGN KEY (`TUTORS_Banner_Id` )
    REFERENCES `385_s11_turpinvp`.`TUTORS` (`Banner_Id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `385_s11_turpinvp`.`SECTIONS`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `385_s11_turpinvp`.`SECTIONS` (
  `COURSES_Course_Id` VARCHAR(45) NOT NULL ,
  `INSTRUCTORS_Name` VARCHAR(45) NOT NULL ,
  `Section_Id` CHAR(1) NOT NULL ,
  PRIMARY KEY (`COURSES_Course_Id`, `INSTRUCTORS_Name`, `Section_Id`) ,
  CONSTRAINT `fk_COURSES_has_ENROLLED_COURSES1`
    FOREIGN KEY (`COURSES_Course_Id` )
    REFERENCES `385_s11_turpinvp`.`COURSES` (`Course_Id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_SECTIONS_INSTRUCTORS1`
    FOREIGN KEY (`INSTRUCTORS_Name` )
    REFERENCES `385_s11_turpinvp`.`INSTRUCTORS` (`Name` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `fk_COURSES_has_ENROLLED_COURSES1` ON `385_s11_turpinvp`.`SECTIONS` (`COURSES_Course_Id` ASC) ;

CREATE INDEX `fk_SECTIONS_INSTRUCTORS1` ON `385_s11_turpinvp`.`SECTIONS` (`INSTRUCTORS_Name` ASC) ;


-- -----------------------------------------------------
-- Table `385_s11_turpinvp`.`ENROLLED`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `385_s11_turpinvp`.`ENROLLED` (
  `STUDENTS_Banner_Id` CHAR(8) NOT NULL ,
  `COURSES_Course_Id` VARCHAR(45) NOT NULL ,
  `SECTIONS_Section_Id` CHAR(1) NOT NULL ,
  PRIMARY KEY (`STUDENTS_Banner_Id`, `COURSES_Course_Id`, `SECTIONS_Section_Id`) ,
  CONSTRAINT `fk_COURSES_has_STUDENTS_STUDENTS1`
    FOREIGN KEY (`STUDENTS_Banner_Id` )
    REFERENCES `385_s11_turpinvp`.`STUDENTS` (`Banner_Id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ENROLLED_COURSES1`
    FOREIGN KEY (`COURSES_Course_Id` )
    REFERENCES `385_s11_turpinvp`.`COURSES` (`Course_Id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ENROLLED_SECTIONS1`
    FOREIGN KEY (`SECTIONS_Section_Id` )
    REFERENCES `385_s11_turpinvp`.`SECTIONS` (`Section_Id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `fk_COURSES_has_STUDENTS_STUDENTS1` ON `385_s11_turpinvp`.`ENROLLED` (`STUDENTS_Banner_Id` ASC) ;

CREATE INDEX `fk_ENROLLED_COURSES1` ON `385_s11_turpinvp`.`ENROLLED` (`COURSES_Course_Id` ASC) ;

C    REATE INDEX `fk_ENROLLED_SECTIONS1` ON `385_s11_turpinvp`.`ENROLLED` (`SECTIONS_Section_Id` ASC) ;



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

Answer 1

To get details on your foreign key error, run SHOW ENGINE INNODB STATUS\G and look at the "LATEST FOREIGN KEY ERROR" section.

It should tell you that the foreign key is invalid because there is no unique index or primary key index on INSTRUCTORS.Name. The primary key is INSTRUCTORS.(Banner_Id, Name) and you have a unique index on INSTRUCTORS.Banner_Id. You either need to use one of those column combinations for the foreign key, or add a unique key on INSTRUCTORS.Name