Table in mySQL that has several foreign keys that are primary key, getting an error ERROR 1215: Cannot add foreign key constraint

Question

I had created those tables:

CREATE TABLE `course` (
  `idcourse` varchar(2) NOT NULL,
  `courseName` varchar(45) NOT NULL,
  `subjectID` varchar(2) NOT NULL,
  PRIMARY KEY (`idcourse`),
  KEY `subjectID_idx` (`subjectID`),
  CONSTRAINT `subjectID` FOREIGN KEY (`subjectID`) REFERENCES `subject` (`idsubject`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `exam` (
  `subjectID` varchar(2) NOT NULL,
  `courseID` varchar(2) NOT NULL,
  `examNumber` varchar(2) NOT NULL,
  `duration` int(11) DEFAULT NULL,
  PRIMARY KEY (`subjectID`,`courseID`,`examNumber`),
  KEY `idCourse_idx` (`courseID`),
  CONSTRAINT `idCo` FOREIGN KEY (`courseID`) REFERENCES `course` (`idcourse`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `idSu` FOREIGN KEY (`subjectID`) REFERENCES `subject` (`idsubject`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `question` (
  `questionText` varchar(100) DEFAULT NULL,
  `answer1` varchar(100) DEFAULT NULL,
  `answer2` varchar(100) DEFAULT NULL,
  `answer3` varchar(100) DEFAULT NULL,
  `answer4` varchar(100) DEFAULT NULL,
  `subjetID` varchar(2) NOT NULL,
  `questionNumber` varchar(3) NOT NULL,
  `rightAnswer` int(4) DEFAULT NULL,
  PRIMARY KEY (`subjetID`,`questionNumber`),
  CONSTRAINT `idsubject` FOREIGN KEY (`subjetID`) REFERENCES `subject` (`idsubject`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `subject` (
  `idsubject` varchar(2) NOT NULL,
  `subjectName` varchar(45) NOT NULL,
  PRIMARY KEY (`idsubject`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

To subject table I add a record:

idsubject = 02, subjectName = Mathematica

To course table I add a record:

idcourse = 03, courseName = Algebra 1, subjectID = 02

To exam table I add a record:

subjectID = 02, courseID = 03, examNumber = 01, duration = 180

Now, I want to create a table: questionsinexam

CREATE TABLE `test`.`questionsinexam` (
  `idExamSubject` VARCHAR(2) NOT NULL,
  `idExamCourse` VARCHAR(2) NOT NULL,
  `idExamNumber` VARCHAR(2) NOT NULL,
  `idQuestionNumber` VARCHAR(3) NOT NULL,
  `pointsPerQuestion` INT NULL,
  PRIMARY KEY (`idExamSubject`, `idExamCourse`, `idExamNumber`, `idQuestionNumber`),
  INDEX `idExamCourse_idx` (`idExamCourse` ASC),
  INDEX `idExamNumber_idx` (`idExamNumber` ASC),
  INDEX `idQuestionNumber_idx` (`idQuestionNumber` ASC),
  CONSTRAINT `idExamSubject`
    FOREIGN KEY (`idExamSubject`)
    REFERENCES `test`.`exam` (`subjectID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `idExamCourse`
    FOREIGN KEY (`idExamCourse`)
    REFERENCES `test`.`exam` (`courseID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `idExamNumber`
    FOREIGN KEY (`idExamNumber`)
    REFERENCES `test`.`exam` (`examNumber`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `idQuestionNumber`
    FOREIGN KEY (`idQuestionNumber`)
    REFERENCES `test`.`question` (`questionNumber`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

Why I'm getting this error?

Thanks.

A foreign key points to a primary key, it cannot point to a *part of* a primary key. The PK of `exam` is `subjectID,courseID,examNumber`, so a foreign key **must** point to all 3 attributes included in the PK. — HoneyBadger, May 30 '18 at 13:06
I'm doing that: CONSTRAINT `idExamSubject` FOREIGN KEY (`idExamSubject`) REFERENCES `test`.`exam` (`subjectID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `idExamCourse` FOREIGN KEY (`idExamCourse`) REFERENCES `test`.`exam` (`courseID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `idExamNumber` FOREIGN KEY (`idExamNumber`) REFERENCES `test`.`exam` (`examNumber`) ON DELETE NO ACTION ON UPDATE NO ACTION, — Asaf, May 30 '18 at 14:11
No you do not: `REFERENCES test.exam (subjectID)` <-- you are referencing one of the 3 attributes that make up the PK of `exam`. — HoneyBadger, May 30 '18 at 14:19
I had finally created the table: `PRIMARY KEY (`subjectID`,`courseID`,`examNumber`,`questionNumber`), KEY `questionsOfTable_idx` (`subjectID`,`questionNumber`), CONSTRAINT `examOfTable` FOREIGN KEY (`subjectID`, `courseID`, `examNumber`) REFERENCES `exam` (`subjectID`, `courseID`, `examNumber`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `questionsOfTable` FOREIGN KEY (`subjectID`, `questionNumber`) REFERENCES `question` (`subjetID`, `questionNumber`) ON DELETE NO ACTION ON UPDATE NO ACTION` Now I'm getting an error by add ERROR 1452: 1452: Cannot add or update a child row: — Asaf, May 30 '18 at 15:42
@HoneyBadger Thanks for your help, I understood and fixed it with your help. — Asaf, May 30 '18 at 19:14

Gordon Linoff · Answer 1 · 2018-07-14T12:29:36.480

0

This constraint is definitely wrong:

CONSTRAINT `idExamSubject`
    FOREIGN KEY (`idExamSubject`)
    REFERENCES `test`.`exam` (`subjectID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,

Perhaps you intend:

CONSTRAINT `idExamSubject`
    FOREIGN KEY (`idExamSubject`)
    REFERENCES `test`.`subject` (`subjectID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,

Be sure that you create the target tables before you create the foreign key constraint. The referred to table needs to exist first (so the engine can validate types on the columns used for the foreign key).

edited Jul 14 '18 at 12:29

answered May 30 '18 at 13:06

Gordon Linoff

1,242,037
58
646
786

I'm getting the same error: > ERROR 1215: Cannot add foreign key constraint – Asaf May 30 '18 at 14:09
@Asaf - Do things in a different order. – Rick James Jul 14 '18 at 05:01

Table in mySQL that has several foreign keys that are primary key, getting an error ERROR 1215: Cannot add foreign key constraint

1 Answers1