can't create table in mysql because of foriegn key constraint

Question

i am trying to create the codedata table in mysql but get error #1215 - Cannot add foreign key constraint. can someone please help me figure out what is wrong? thanks in advance for your help!

here is the code that doesn't work:

DROP TABLE IF EXISTS `interviewcodes`.`codedata` ;

CREATE TABLE IF NOT EXISTS `interviewcodes`.`codedata` (
  `StudyID` INT(11) NOT NULL,
  `ParticipantID` INT(11) NOT NULL,
  `CoderID` INT(11) NOT NULL,
  `CodingMonth` INT(11) NOT NULL,
  `CodingDay` INT(11) NOT NULL,
  `CodingYear` INT(11) NOT NULL,
  `StudyQuestionLabel` VARCHAR(45) NOT NULL,
  `StudyQuestionResponse` VARCHAR(245) NULL,
  `IWAcode` INT(11) NULL DEFAULT 0,
  `CQcode` INT(11) NULL DEFAULT 0,
  `CRcode` INT(11) NULL DEFAULT 0,
  `PMinusCode` INT(11) NULL DEFAULT 0,
  `PPlusCode` INT(11) NULL DEFAULT 0,
  `PROcode` INT(11) NULL DEFAULT 0,
  `CONcode` INT(11) NULL DEFAULT 0,
  `RELcode` INT(11) NULL DEFAULT 0,
  `NOAcode` INT(11) NULL DEFAULT 0,
  `OTHcode` INT(11) NULL DEFAULT 0,
  `TotalScore` INT(11) NULL DEFAULT 0,
  `Remark` VARCHAR(5000) NULL DEFAULT NULL,
  INDEX `fk_CodeData_Participant1_idx` (`ParticipantID` ASC),
  INDEX `fk_CodeData_StudyCoders1_idx` (`CoderID` ASC),
  INDEX `fk_codedata_studyquestion1_idx` (`StudyQuestionLabel` ASC),
  PRIMARY KEY (`StudyID`, `ParticipantID`, `CoderID`, `StudyQuestionLabel`),
  CONSTRAINT `fk_CodeData_Participant1`
    FOREIGN KEY (`ParticipantID`)
    REFERENCES `interviewcodes`.`participant` (`ParticipantID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_CodeData_StudyCoders1`
    FOREIGN KEY (`CoderID`)
    REFERENCES `interviewcodes`.`studycoders` (`CoderID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_codedata_studylkup1`
    FOREIGN KEY (`StudyID`)
    REFERENCES `interviewcodes`.`studylkup` (`StudyID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_codedata_studyquestion1`
    FOREIGN KEY (`StudyQuestionLabel`)
    REFERENCES `interviewcodes`.`studyquestion` (`StudyQuestionLabel`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

here is the code i used to create the other tables, which ran correctly:

DROP TABLE IF EXISTS `interviewcodes`.`interviewerlkup` ;

CREATE TABLE IF NOT EXISTS `interviewcodes`.`interviewerlkup` (
  `InterviewerID` INT(11) NOT NULL AUTO_INCREMENT,
  `InterviewerFirstName` VARCHAR(45) NOT NULL,
  `InterviewerLastName` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`InterviewerID`))
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = utf8;


DROP TABLE IF EXISTS `interviewcodes`.`studylkup` ;

CREATE TABLE IF NOT EXISTS `interviewcodes`.`studylkup` (
  `StudyID` INT(11) NOT NULL AUTO_INCREMENT,
  `StudyName` VARCHAR(45) NOT NULL,
  `StudyPIFirstName` VARCHAR(45) NULL,
  `StudyPILastName` VARCHAR(45) NULL,
  `StudyStartMonth` INT(11) NOT NULL,
  `StudyStartDay` INT(11) NOT NULL,
  `StudyStartYear` INT(11) NOT NULL,
  PRIMARY KEY (`StudyID`))
ENGINE = InnoDB
AUTO_INCREMENT = 4
DEFAULT CHARACTER SET = utf8;


DROP TABLE IF EXISTS `interviewcodes`.`studyinterviewers` ;

CREATE TABLE IF NOT EXISTS `interviewcodes`.`studyinterviewers` (
  `StudyID` INT(11) NOT NULL,
  `InterviewerID` INT(11) NOT NULL,
  PRIMARY KEY (`StudyID`, `InterviewerID`),
  INDEX `fk_StudyInterviewers_InterviewerLkup1_idx` (`InterviewerID` ASC),
  CONSTRAINT `fk_StudyInterviewers_InterviewerLkup1`
    FOREIGN KEY (`InterviewerID`)
    REFERENCES `interviewcodes`.`interviewerlkup` (`InterviewerID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_StudyInterviewers_StudyLkup1`
    FOREIGN KEY (`StudyID`)
    REFERENCES `interviewcodes`.`studylkup` (`StudyID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


DROP TABLE IF EXISTS `interviewcodes`.`participant` ;

CREATE TABLE IF NOT EXISTS `interviewcodes`.`participant` (
  `ParticipantID` INT(11) NOT NULL AUTO_INCREMENT,
  `ParticipantCaseID` VARCHAR(45) NOT NULL,
  `StudyID` INT(11) NOT NULL,
  `InterviewerID` INT(11) NOT NULL,
  `InterviewMonth` INT(11) NULL DEFAULT NULL,
  `InterviewDay` INT(11) NULL DEFAULT NULL,
  `InterviewYear` INT(11) NULL DEFAULT NULL,
  PRIMARY KEY (`ParticipantID`),
  INDEX `fk_participant_studyinterviewers1_idx` (`InterviewerID` ASC),
  CONSTRAINT `fk_participant_studyinterviewers1`
    FOREIGN KEY (`InterviewerID`)
    REFERENCES `interviewcodes`.`studyinterviewers` (`InterviewerID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_participant_studylkup1`
    FOREIGN KEY (`StudyID`)
    REFERENCES `interviewcodes`.`studylkup` (`StudyID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 6
DEFAULT CHARACTER SET = utf8;


DROP TABLE IF EXISTS `interviewcodes`.`coderlkup` ;

CREATE TABLE IF NOT EXISTS `interviewcodes`.`coderlkup` (
  `CoderID` INT(11) NOT NULL AUTO_INCREMENT,
  `CoderFirstName` VARCHAR(45) NOT NULL,
  `CoderLastName` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`CoderID`))
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = utf8;


DROP TABLE IF EXISTS `interviewcodes`.`studycoders` ;

CREATE TABLE IF NOT EXISTS `interviewcodes`.`studycoders` (
  `StudyID` INT(11) NOT NULL,
  `CoderID` INT(11) NOT NULL,
  PRIMARY KEY (`StudyID`, `CoderID`),
  INDEX `fk_StudyCoders_CoderLkup1_idx` (`CoderID` ASC),
  CONSTRAINT `fk_StudyCoders_CoderLkup1`
    FOREIGN KEY (`CoderID`)
    REFERENCES `interviewcodes`.`coderlkup` (`CoderID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_StudyCoders_StudyLkup1`
    FOREIGN KEY (`StudyID`)
    REFERENCES `interviewcodes`.`studylkup` (`StudyID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


DROP TABLE IF EXISTS `interviewcodes`.`studyquestion` ;

CREATE TABLE IF NOT EXISTS `interviewcodes`.`studyquestion` (
  `StudyID` INT(11) NOT NULL,
  `StudyQuestionLabel` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`StudyID`, `StudyQuestionLabel`),
  CONSTRAINT `fk_StudyQuestion_StudyLkup`
    FOREIGN KEY (`StudyID`)
    REFERENCES `interviewcodes`.`studylkup` (`StudyID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

here is the last part of my code, after i try to create the codedata table:

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


ALTER IGNORE TABLE `StudyLkup` ADD UNIQUE INDEX(`StudyName`);
ALTER IGNORE TABLE `InterviewerLkup` ADD UNIQUE INDEX(`InterviewerFirstName`, `InterviewerLastName`);
ALTER IGNORE TABLE `CoderLkup` ADD UNIQUE INDEX(`CoderFirstName`, `CoderLastName`);
ALTER IGNORE TABLE `Participant` ADD UNIQUE INDEX(`ParticipantCaseID`, `StudyID`);
ALTER IGNORE TABLE `StudyCoders` ADD UNIQUE INDEX(`StudyID`, `CoderID`);
ALTER IGNORE TABLE `StudyInterviewers` ADD UNIQUE INDEX(`StudyID`, `InterviewerID`);
ALTER IGNORE TABLE `StudyQuestion` ADD UNIQUE INDEX(`StudyID`, `StudyQuestionLabel`);
ALTER IGNORE TABLE `CodeData` ADD UNIQUE INDEX(`StudyID`, `ParticipantID`, `CoderID`, `StudyQuestionLabel`);

Answer 1

It will work if you perform this:

CREATE TABLE IF NOT EXISTS `interviewcodes`.`studyquestion` (
  `StudyID` INT(11) NOT NULL,
  `StudyQuestionLabel` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`StudyID`, `StudyQuestionLabel`),
  key(`StudyQuestionLabel`), -- <-------- I added this
  CONSTRAINT `fk_StudyQuestion_StudyLkup`
    FOREIGN KEY (`StudyID`)
    REFERENCES `interviewcodes`.`studylkup` (`StudyID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

Reason: Left-most issue on StudyQuestionLabel is lacking.

Though StudyQuestionLabel is in a composite key it your code, it is not left-most.

Note that I created an interviewcodes schema and tested it.

From the Manual Page Using FOREIGN KEY Constraints a quote:

... In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order.