0

Executing SQL script in server

ERROR: Error 1215: Cannot add foreign key constraint

SQL Code:

CREATE TABLE IF NOT EXISTS `CAREIS101`.`Lecture` (
    `idLecture` INT NOT NULL AUTO_INCREMENT COMMENT '',
    `Lecture_name` VARCHAR(45) NULL COMMENT '',
    `Time` TIME NULL COMMENT '',
    `Date` DATE NULL COMMENT '',
    `Subject` VARCHAR(45) NULL COMMENT '',
    `Block` VARCHAR(45) NULL COMMENT '',
    `Semester` VARCHAR(45) NULL COMMENT '',
    `Room` VARCHAR(45) NULL COMMENT '',
    `Program_idProgram` INT NOT NULL COMMENT '',
    `Program_Stud_Info_Stud_Num` INT NOT NULL COMMENT '',
    `Lecturer_idLecturer` INT NOT NULL COMMENT '',

    PRIMARY KEY (`idLecture`)  COMMENT '',

    INDEX `fk_Lecture_Program1_idx` (`Program_idProgram` ASC, `Program_Stud_Info_Stud_Num` ASC)  COMMENT '',
    INDEX `fk_Lecture_Lecturer1_idx` (`Lecturer_idLecturer` ASC)  COMMENT '',

    UNIQUE INDEX `Program_idProgram_UNIQUE` (`Program_idProgram` ASC)  COMMENT '',
    UNIQUE INDEX `Program_Stud_Info_Stud_Num_UNIQUE` (`Program_Stud_Info_Stud_Num` ASC)  COMMENT '',
    UNIQUE INDEX `Lecturer_idLecturer_UNIQUE` (`Lecturer_idLecturer` ASC)  COMMENT '',

    CONSTRAINT `fk_Lecture_Program1`
        FOREIGN KEY (`Program_idProgram` , `Program_Stud_Info_Stud_Num`)
        REFERENCES `CAREIS101`.`Program` (`idProgram` , `Stud_Info_Stud_Num`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
    CONSTRAINT `fk_Lecture_Lecturer1`
        FOREIGN KEY (`Lecturer_idLecturer`)
        REFERENCES `CAREIS101`.`Lecturer` (`idLecturer`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)

    ENGINE = InnoDB

SQL script execution finished: statements: 8 succeeded, 1 failed

Fetching back view definitions in final form. Nothing to fetch

Mike Lischke
  • 48,925
  • 16
  • 119
  • 181
johnleo tatel
  • 1
  • You should be able to run: "show engine innodb status" in mysql workbench and copy the status row and search for the phrase latest foreign key error (may be upper case). That should provide more details on why the constraint creation failed. – Prudhvi Konda Aug 30 '15 at 05:25
  • possible duplicate of [MySQL Error 1215: Cannot add foreign key constraint](http://stackoverflow.com/questions/16969060/mysql-error-1215-cannot-add-foreign-key-constraint) – Mike Lischke Sep 01 '15 at 07:44
  • This question has been asked multiple times here. Please search SO first before you ask. – Mike Lischke Sep 01 '15 at 07:44

0 Answers0