MySQL Cannot add Foreign Key Constraint MySQL-CodePudding

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).

CodePudding user response：

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