ERROR: Error 1215: Cannot add foreign key constraint (have tried everything)

Question

Having an impossible time finding what this error is.

I use

SHOW ENGINE INNODB STATUS

to get this error:

2015-12-05 03:20:44 16c8 Error in foreign key constraint of table mydb/enroll:

    FOREIGN KEY (`sectno`)
    REFERENCES `mydb`.`section` (`sectno`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.

So I think it's safe to assume that it's the sectno key and the table it references that is causing me trouble.

So here are my "enroll" tables (the table that it's not letting me create) and the "section" tables

    -- -----------------------------------------------------
-- Table `mydb`.`section`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`section` (
  `dname` VARCHAR(45) NOT NULL COMMENT '',
  `cno` INT NOT NULL COMMENT '',
  `sectno` INT NOT NULL COMMENT '',
  `pname` VARCHAR(45) NOT NULL COMMENT '',
  PRIMARY KEY (`sectno`, `dname`, `cno`)  COMMENT '',
  INDEX `fk_cno_section_idx` (`cno` ASC)  COMMENT '',
  INDEX `fk_pname_section_idx` (`pname` ASC)  COMMENT '',
  CONSTRAINT `fk_dname_section`
    FOREIGN KEY (`dname`)
    REFERENCES `mydb`.`dept` (`dname`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_cno_section`
    FOREIGN KEY (`cno`)
    REFERENCES `mydb`.`course` (`cno`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_pname_section`
    FOREIGN KEY (`pname`)
    REFERENCES `mydb`.`prof` (`pname`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`enroll`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`enroll` (
  `sid` INT NOT NULL COMMENT '',
  `grade` DOUBLE NULL COMMENT '',
  `dname` VARCHAR(45) NOT NULL COMMENT '',
  `cno` INT NOT NULL COMMENT '',
  `sectno` INT NOT NULL COMMENT '',
  PRIMARY KEY (`sid`, `dname`, `cno`, `sectno`)  COMMENT '',
  INDEX `fk_dname_enroll_idx` (`dname` ASC)  COMMENT '',
  INDEX `fk_cno_enroll_idx` (`cno` ASC)  COMMENT '',
  INDEX `fk_sectno_enroll_idx` (`sectno` ASC)  COMMENT '',
  CONSTRAINT `fk_sid_enroll`
    FOREIGN KEY (`sid`)
    REFERENCES `mydb`.`student` (`sid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_dname_enroll`
    FOREIGN KEY (`dname`)
    REFERENCES `mydb`.`dept` (`dname`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_cno_enroll`
    FOREIGN KEY (`cno`)
    REFERENCES `mydb`.`course` (`cno`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_sectno_enroll`
    FOREIGN KEY (`sectno`)
    REFERENCES `mydb`.`section` (`sectno`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

I'm using MySQL workbench forward engineering so I didn't actually type out the code myself. I've checked and double checked and triple checked and all the data types seem to match, so it must be this error that is the problem: "Cannot find an index in the referenced table where the referenced columns appear as the first columns." I'm not entirely sure I understood it 100%, but I thought it meant that they FK being referenced must be in the first column of the referenced table. So in the section table, I like..declared

 `sectno` INT NOT NULL COMMENT '',`

before all the other columns so that it was "first," and still nothing. I've spent hour after hour after hour and I am just...mentally exhausted. Somebody please help

Edit: here are the rest of the tables:

-- 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 mydb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`student`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`student` (
  `sid` INT NOT NULL COMMENT '',
  `sname` VARCHAR(45) NULL COMMENT '',
  `sex` VARCHAR(10) NULL COMMENT '',
  `age` INT NULL COMMENT '',
  `year` INT NULL COMMENT '',
  `gpa` DOUBLE NULL COMMENT '',
  PRIMARY KEY (`sid`)  COMMENT '')
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`dept`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`dept` (
  `dname` VARCHAR(45) NOT NULL COMMENT '',
  `numphds` INT NULL COMMENT '',
  PRIMARY KEY (`dname`)  COMMENT '')
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`prof`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`prof` (
  `pname` VARCHAR(45) NOT NULL COMMENT '',
  `dname` VARCHAR(45) NOT NULL COMMENT '',
  PRIMARY KEY (`pname`)  COMMENT '',
  INDEX `fk_dname_prof_idx` (`dname` ASC)  COMMENT '',
  CONSTRAINT `fk_dname_prof`
    FOREIGN KEY (`dname`)
    REFERENCES `mydb`.`dept` (`dname`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`course`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`course` (
  `cno` INT NOT NULL COMMENT '',
  `cname` VARCHAR(45) NULL COMMENT '',
  `dname` VARCHAR(45) NOT NULL COMMENT '',
  PRIMARY KEY (`cno`, `dname`)  COMMENT '',
  INDEX `pk_dname_course_idx` (`dname` ASC)  COMMENT '',
  CONSTRAINT `pk_dname_course`
    FOREIGN KEY (`dname`)
    REFERENCES `mydb`.`dept` (`dname`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`major`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`major` (
  `dname` VARCHAR(45) NOT NULL COMMENT '',
  `sid` INT NOT NULL COMMENT '',
  PRIMARY KEY (`dname`, `sid`)  COMMENT '',
  INDEX `fk_sid_major_idx` (`sid` ASC)  COMMENT '',
  CONSTRAINT `fk_dname_major`
    FOREIGN KEY (`dname`)
    REFERENCES `mydb`.`dept` (`dname`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_sid_major`
    FOREIGN KEY (`sid`)
    REFERENCES `mydb`.`student` (`sid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Answer 1

This script runs all the way thru. If it does not, change the 2 lines at the top to a different schema name, and run it without any comment sections active.

I cannot find a reference at the moment for that reason, but I stumbled into it once here.

To be clear on that, in some interfaces, a comment section would throw an error.

Needless to say, the order in which they run is important.

All of your FK conditions look good. The datatypes and signs are good and match from referencing to referenced tables. And in the referenced tables, they all have left-most indexes on those columns.

create schema asdf789;

use asdf789;


CREATE TABLE IF NOT EXISTS `section` (
  `dname` VARCHAR(45) NOT NULL COMMENT '',
  `cno` INT NOT NULL COMMENT '',
  `sectno` INT NOT NULL COMMENT '',
  `pname` VARCHAR(45) NOT NULL COMMENT '',
  PRIMARY KEY (`sectno`, `dname`, `cno`)  COMMENT '',
  INDEX `fk_cno_section_idx` (`cno` ASC)  COMMENT '',
  INDEX `fk_pname_section_idx` (`pname` ASC)  COMMENT ''
)ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `student` (
  `sid` INT NOT NULL COMMENT '',
  `sname` VARCHAR(45) NULL COMMENT '',
  `sex` VARCHAR(10) NULL COMMENT '',
  `age` INT NULL COMMENT '',
  `year` INT NULL COMMENT '',
  `gpa` DOUBLE NULL COMMENT '',
  PRIMARY KEY (`sid`)  COMMENT '')
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`dept`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `dept` (
  `dname` VARCHAR(45) NOT NULL COMMENT '',
  `numphds` INT NULL COMMENT '',
  PRIMARY KEY (`dname`)  COMMENT '')
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`prof`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `prof` (
  `pname` VARCHAR(45) NOT NULL COMMENT '',
  `dname` VARCHAR(45) NOT NULL COMMENT '',
  PRIMARY KEY (`pname`)  COMMENT '',
  INDEX `fk_dname_prof_idx` (`dname` ASC)  COMMENT '',
  CONSTRAINT `fk_dname_prof`
    FOREIGN KEY (`dname`)
    REFERENCES `dept` (`dname`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`course`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `course` (
  `cno` INT NOT NULL COMMENT '',
  `cname` VARCHAR(45) NULL COMMENT '',
  `dname` VARCHAR(45) NOT NULL COMMENT '',
  PRIMARY KEY (`cno`, `dname`)  COMMENT '',
  INDEX `pk_dname_course_idx` (`dname` ASC)  COMMENT '',
  CONSTRAINT `pk_dname_course`
    FOREIGN KEY (`dname`)
    REFERENCES `dept` (`dname`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`major`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `major` (
  `dname` VARCHAR(45) NOT NULL COMMENT '',
  `sid` INT NOT NULL COMMENT '',
  PRIMARY KEY (`dname`, `sid`)  COMMENT '',
  INDEX `fk_sid_major_idx` (`sid` ASC)  COMMENT '',
  CONSTRAINT `fk_dname_major`
    FOREIGN KEY (`dname`)
    REFERENCES `dept` (`dname`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_sid_major`
    FOREIGN KEY (`sid`)
    REFERENCES `student` (`sid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `enroll` (
  `sid` INT NOT NULL COMMENT '',
  `grade` DOUBLE NULL COMMENT '',
  `dname` VARCHAR(45) NOT NULL COMMENT '',
  `cno` INT NOT NULL COMMENT '',
  `sectno` INT NOT NULL COMMENT '',
  PRIMARY KEY (`sid`, `dname`, `cno`, `sectno`)  COMMENT '',
  INDEX `fk_dname_enroll_idx` (`dname` ASC)  COMMENT '',
  INDEX `fk_cno_enroll_idx` (`cno` ASC)  COMMENT '',
  INDEX `fk_sectno_enroll_idx` (`sectno` ASC)  COMMENT '',
  CONSTRAINT `fk_sid_enroll`
    FOREIGN KEY (`sid`)
    REFERENCES `student` (`sid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_dname_enroll`
    FOREIGN KEY (`dname`)
    REFERENCES `dept` (`dname`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_cno_enroll`
    FOREIGN KEY (`cno`)
    REFERENCES `course` (`cno`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_sectno_enroll`
    FOREIGN KEY (`sectno`)
    REFERENCES `section` (`sectno`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;