MySQL Cannot add Foreign Key Constraint MySQL

Question

I am having a hard time understanding why my code is giving me cannot add foreign key constraint error. I just started coding in MySQL so I am sorry for anything dumb.

drop table if exists COURSE_INSTRUCTORJG;
drop table if exists CLASSJG;
drop table if exists COURSEJG;
drop table if exists INSTRUCTORJG;

create table INSTRUCTORJG(
InstructorID int,
DepartHead int,
primary key(InstructorID)
);

create table COURSEJG(
CourseNo char(10),
ComputerNeeded smallint,
primary key(CourseNo)
);

create table CLASSJG(
Building     CHAR(20),
RoomNum     smallint,
Maxcap int,
primary key(Building, RoomNum)
);

create table COURSE_INSTRUCTORJG ( 
COURSEJG_CourseNo    CHAR(10), 
INSTRUCTORJG_InstructorID  INT, 
Semester    CHAR(20),  
Section       INT,   
CLASSJG_Building char(20),
CLASSJG_RoomNum smallint,
primary key(COURSEJG_CourseNo, INSTRUCTORJG_InstructorID, Semester, Section),
foreign key (COURSEJG_CourseNo) references COURSEJG(CourseNo),
foreign key (INSTRUCTORJG_InstructorID) references INSTRUCTORJG(InstructorID),
foreign key (CLASSJG_Building) references CLASSJG(Building),
foreign key (CLASSJG_RoomNum) references CLASSJG(RoomNum)
);

My error is coming from the line: foreign key (CLASSJG_RoomNum) references CLASSJG(RoomNum).

nbk · Accepted Answer · 2022-03-04 22:42:27Z

1

You can reference primary key, keys or unique comnstrqaint, but in table CLASSJG you have a double primary key, so reference both columns or define another key

create table CLASSJG(
Building     CHAR(20),
RoomNum     smallint,
Maxcap int,
primary key(Building, RoomNum),
KEY(RoomNum)
);

see sample fiddle

answered Mar 4, 2022 at 22:42

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Cannot add Foreign Key Constraint MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related