Can't add my second Foreign Key Constraint

Question

CREATE TABLE Transcripts (sID VARCHAR(7), cNo VARCHAR(10),
semester VARCHAR(20), grade CHAR(1), PRIMARY KEY (sID)
);
CREATE TABLE Students (sID CHAR(7), sName VARCHAR(20),
bDate DATE, phone VARCHAR(12), major VARCHAR(30), avgGPA VARCHAR(4), PRIMARY KEY (sID),
FOREIGN KEY (sID)
    REFERENCES Transcripts(sID)
);
CREATE TABLE Courses (cNo VARCHAR(10), cTitle VARCHAR(30),
creditHours VARCHAR(2), deptName VARCHAR(30), PRIMARY KEY (cNo),
FOREIGN KEY (cNo)
    REFERENCES Transcripts(cNo)
);

So whenever I run this, the first foreign key works just fine, whenever I run the next table it gives me back this error "ERROR 1215 (HY000): Cannot add foreign key constraint" What did I do wrong?

score 1 · Answer 1 · answered Jan 29 '18 at 19:23

This is the foreign key that produces the error:

FOREIGN KEY (cNo)
    REFERENCES Transcripts(cNo)

But the Transcripts(cNo) is not part of any KEY in that table.

A foreign key must reference column(s) of a UNIQUE or PRIMARY KEY of the parent table.

See MySQL Creating tables with Foreign Keys giving errno: 150 for a good checklist required for a foreign key.

score 1 · Answer 2 · answered Jan 29 '18 at 19:23

Index the foreign key column before adding the foreign key.

CREATE TABLE Courses (cNo VARCHAR(10), cTitle VARCHAR(30),
    creditHours VARCHAR(2), deptName VARCHAR(30), PRIMARY KEY (cNo),
    INDEX (cNo),
    FOREIGN KEY (cNo)
        REFERENCES Transcripts(cNo)
    );

score 0 · Accepted Answer · answered Jan 29 '18 at 19:22

0

to define cNo as a foreign key on Courses table, it must be primary key or a unique key of other table.

CREATE TABLE Transcripts(
  sID VARCHAR(7), 
  cNo VARCHAR(10) NOT NULL UNIQUE,
  semester VARCHAR(20), 
  grade CHAR(1), 
  PRIMARY KEY (sID)
);

http://sqlfiddle.com/#!9/fddf8

changing Transcripts, as I've written above wil solve your problem.

answered Jan 29 '18 at 19:22

marmeladze

6,468
3
24
45

But, if you can make `cNo` `UNIQUE`, then it may as well be the `PRIMARY KEY`, thereby making `sID` useless? Once you have done that, you may as well merge the table with the other table having `cNo` as the PK?? – Rick James Feb 14 '18 at 02:53
Allen -- did you verify that this fix works for your data before 'approving' the Answer? – Rick James Feb 14 '18 at 02:55
Rick, making `cNo` unique just makes `cNo` to be a candidate for a foreign key to be used on other table. I do not advice to use that way, and completely agree with your rationale, but Allen asked how to make it possible to add `cNo` as a FK and I've verily answered this. – marmeladze Feb 14 '18 at 05:20

score 0 · Answer 4 · answered Feb 14 '18 at 02:54

0

Isn't the logic backwards? Shouldn't `Transcripts have two FKs referencing the other two tables? And, as already pointed out, declare the other two tables first.

answered Feb 14 '18 at 02:54

Rick James

135,179
13
127
222

Can't add my second Foreign Key Constraint

4 Answers4