错误1215：正向工程时无法添加外键约束

when I try to forward engineer my new schema, I am getting this error. Anyone can offer your assistance to ?

当我尝试转发我的新架构时，我收到了这个错误。任何人都可以提供帮助吗？

-- -----------------------------------------------------
    -- Table `SLIOP`.`schedule`
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `SLIOP`.`schedule` (
      `scheduleID` INT NOT NULL AUTO_INCREMENT,
      `lecturerID` INT NOT NULL,
      `courseID` INT NOT NULL,
      `type` VARCHAR(30) NOT NULL,
      PRIMARY KEY (`scheduleID`),
      INDEX `fk_schedule_academic_staff1_idx` (`lecturerID` ASC),
      INDEX `fk_schedule_course1_idx` (`courseID` ASC),
      CONSTRAINT `lecturerID`
        FOREIGN KEY (`lecturerID`)
        REFERENCES `SLIOP`.`academic_staff` (`lecturerID`)
        CONSTRAINT `courseID`
        FOREIGN KEY (`courseID`)
        REFERENCES `SLIOP`.`course` (`courseID`)
        ENGINE = InnoDB

Parent tables

父表

     -- -----------------------------------------------------
     -- Table `SLIOP`.`course`
     -- -----------------------------------------------------
     CREATE TABLE IF NOT EXISTS `SLIOP`.`course` (
     `courseID` INT NOT NULL AUTO_INCREMENT,
     `course_code` VARCHAR(10) NOT NULL,
     `course_name` VARCHAR(40) NOT NULL,
     `lecturer_name` VARCHAR(40) NOT NULL,
     `time` TIMESTAMP NOT NULL,
     `fee` DECIMAL(10,2) NOT NULL,
     `requirement` MEDIUMTEXT NOT NULL,
     `lecturerID` INT NOT NULL,
     PRIMARY KEY (`courseID`),
     ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `SLIOP`.`academic_staff`
-- -----------------------------------------------------
 CREATE TABLE IF NOT EXISTS `SLIOP`.`academic_staff` (
 `lecturerID` INT NOT NULL AUTO_INCREMENT,
 `first_name` VARCHAR(60) NOT NULL,
 `last_name` VARCHAR(60) NOT NULL,
 `profile_image` BLOB NULL,
 PRIMARY KEY (`lecturerID`))
 ENGINE = InnoDB;

I have searched related posts here, but i couldn't find where is my error.

我在这里搜索了相关的帖子，但我找不到我的错误在哪里。

2 个解决方案

#1

Main issue I believe is a missing comma before CONSTRAINT courseID

我认为主要问题是在CONSTRAINT courseID之前缺少逗号

But you missed several closing braces as well.

但你也错过了几个关闭括号。

So here are working statements:

所以这是工作声明：

http://sqlfiddle.com/#!9/2bb11

     CREATE TABLE IF NOT EXISTS `course` (
     `courseID` INT NOT NULL AUTO_INCREMENT,
     `course_code` VARCHAR(10) NOT NULL,
     `course_name` VARCHAR(40) NOT NULL,
     `lecturer_name` VARCHAR(40) NOT NULL,
     `time` TIMESTAMP NOT NULL,
     `fee` DECIMAL(10,2) NOT NULL,
     `requirement` MEDIUMTEXT NOT NULL,
     `lecturerID` INT NOT NULL,
     PRIMARY KEY (`courseID`)
     )
     ENGINE = InnoDB;

 CREATE TABLE IF NOT EXISTS `academic_staff` (
 `lecturerID` INT NOT NULL AUTO_INCREMENT,
 `first_name` VARCHAR(60) NOT NULL,
 `last_name` VARCHAR(60) NOT NULL,
 `profile_image` BLOB NULL,
 PRIMARY KEY (`lecturerID`))
 ENGINE = InnoDB;

    CREATE TABLE IF NOT EXISTS `schedule` (
      `scheduleID` INT NOT NULL AUTO_INCREMENT,
      `lecturerID` INT NOT NULL,
      `courseID` INT NOT NULL,
      `type` VARCHAR(30) NOT NULL,
      PRIMARY KEY (`scheduleID`),
      INDEX `fk_schedule_academic_staff1_idx` (`lecturerID` ASC),
      INDEX `fk_schedule_course1_idx` (`courseID` ASC),
      CONSTRAINT `lecturerID`
        FOREIGN KEY (`lecturerID`)
        REFERENCES `academic_staff` (`lecturerID`),
        CONSTRAINT `courseID`
        FOREIGN KEY (`courseID`)
        REFERENCES `course` (`courseID`))
        ENGINE = InnoDB

#2

I have been able to create the tables without any issue, after correcting the syntax errors. Just make sure you are running the scripts in correct order. i.e. Table SLIOP.schedule should be created last since it is referencing SLIOP.academic_staff and SLIOP.course

在纠正语法错误后，我能够毫无问题地创建表。只需确保以正确的顺序运行脚本。即表SLIOP.schedule应该是最后创建的，因为它引用了SLIOP.academic_staff和SLIOP.course

#1