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`);

It would be quicker and helpful if you could provide us which foreign key is being blocked so we can directly look at them to try and help you. — Marco Aurélio Deleu, Oct 18 '15 at 15:24
it doesn't tell me. all i know is that the codedata table is the one containing the error. sorry, i can't help identify which one. — ajwong4, Oct 18 '15 at 15:26
i noticed that there is no index statement for fk_codedata_studylkup1. could this be it? — ajwong4, Oct 18 '15 at 15:27
Possible duplicate of http://stackoverflow.com/questions/16969060/mysql-error-1215-cannot-add-foreign-key-constraint — Marco Aurélio Deleu, Oct 18 '15 at 15:28
i looked at that one and made sure the foriegn keys are primary keys in their parent tables and also made sure the data type and character sets are the same in all tables — ajwong4, Oct 18 '15 at 15:42
As a suggestion, try to erase all "Index" definitions and keep just the constraint definitions. — Marco Aurélio Deleu, Oct 18 '15 at 15:46

Drew · Accepted Answer · 2015-10-18T16:03:44.343

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.

edited Oct 18 '15 at 16:03

answered Oct 18 '15 at 15:51

Drew

24,851
10
43
78

i will paste the rest of my code above. now i am getting the error: SQL query: SET SQL_MODE=@OLD_SQL_MODE; MySQL said: Documentation #1231 - Variable 'sql_mode' can't be set to the value of 'NULL' – ajwong4 Oct 18 '15 at 16:03
what is the rest of your code? I just showed you in the pastie how it works all the way thru, with the change I made adding a left-most key – Drew Oct 18 '15 at 16:04
ok, you just performed an edit to your comment. Play with what I gave, good luck – Drew Oct 18 '15 at 16:06
i deleted the db and ran my code again. i didn't get any errors this time. thanks! that work! – ajwong4 Oct 18 '15 at 16:15

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

1 Answers1