Cannot add foreign key constraint error 1215 HY000

Question

I keep getting this error for my ENROLLS table, TAKES table, CONDUCTED_IN table, and EXAM table. when I am uploading my created tables to the database. I am not sure what the problem is can anyone help out? The COURSE, SECTION, CLASS_ROOM, and STUDENT tables were added to the database just fine the others have problems.

create table COURSE(
CourseNo varchar(10),
Course_Name varchar(30),
Department varchar(25),
primary key(CourseNo)
);

create table SECTION(
CourseNo varchar(10),
SectionNo varchar(2),
primary key(CourseNo, SectionNo),
foreign key(CourseNo) references COURSE(CourseNo)
);

create table ENROLLS(
SSN char(9),
SectionNo varchar(2),
CourseNo varchar(10),
primary key(SSN, CourseNo, SectionNo),
foreign key(SSN) references STUDENT(SSN),
foreign key(SectionNo) references SECTION(SectionNo),
foreign key(CourseNo) references SECTION(CourseNo)
);

create table STUDENT(
SSN char(9),
First_Name varchar(20),
Last_Name varchar(20),
Street varchar(15),
City varchar(15),
Zip char(5),
State varchar(20),
primary key(SSN)
);

create table TAKES(
SSN char(9),
CourseNo varchar(10),
SectionNo varchar(2),
ExamNo char(3),
Result char(1),
primary key(SSN, CourseNo, SectionNo, ExamNo),
foreign key(SSN) references STUDENT(SSN),
foreign key(CourseNo) references EXAM(CourseNo),
foreign key(SectionNo) references EXAM(SectionNo),
foreign key(ExamNo) references EXAM(ExamNo)
);

create table EXAM(
CourseNo varchar(10),
SectionNo varchar(2),
ExamNo char(3),
primary key(CourseNo, SectionNo, ExamNo),
foreign key(CourseNo) references SECTION(CourseNo),
foreign key(SectionNo) references SECTION(SectionNo)
);

create table CONDUCTED_IN(
RoomNo int,
Building varchar(30),
CourseNo varchar(10),
SectionNo varchar(2),
ExamNo char(3),
primary key(RoomNo, Building, CourseNo, SectionNo, ExamNo),
foreign key(RoomNo) references CLASS_ROOM(RoomNo),
foreign key(Building) references CLASS_Room(Building),
foreign key(CourseNo) references EXAM(CourseNo),
foreign key(SectionNo) references EXAM(SectionNo),
foreign key(ExamNo) references EXAM(ExamNo)
);

create table CLASS_ROOM(
RoomNo int,
Building varchar(30),
Capacity int,
primary key(RoomNo, Building)
);

Answer 1

The issues in your code are related to the management of relationships

• tables must be created in sequence; when a table that refers to another table through a foreign key, it must be created after the parent table

• most of your table have compound primary keys (ie primary keys that use multiple columns) - so you need compound foreign keys as well: rather than creating multiple foreign keys over each related column of the relation, you want an single, multi-column key that references the tuple of columns at once

With these base principles in mind, here is an updated version of your code that runs fine in this DB Fiddle.

create table COURSE(
    CourseNo varchar(10),
    Course_Name varchar(30),
    Department varchar(25),
    primary key(CourseNo)
);

create table CLASS_ROOM(
    RoomNo int,
    Building varchar(30),
    Capacity int,
    primary key(RoomNo, Building)
);

create table STUDENT(
    SSN char(9),
    First_Name varchar(20),
    Last_Name varchar(20),
    Street varchar(15),
    City varchar(15),
    Zip char(5),
    State varchar(20),
    primary key(SSN)
);

create table SECTION(
    CourseNo varchar(10),
    SectionNo varchar(2),
    primary key(CourseNo, SectionNo),
    foreign key(CourseNo) references COURSE(CourseNo)
);

create table ENROLLS(
    SSN char(9),
    SectionNo varchar(2),
    CourseNo varchar(10),
    primary key(SSN, CourseNo, SectionNo),
    foreign key(SSN) references STUDENT(SSN),
    foreign key(CourseNo, SectionNo) references SECTION(CourseNo, SectionNo)
);

create table EXAM(
    CourseNo varchar(10),
    SectionNo varchar(2),
    ExamNo char(3),
    primary key(CourseNo, SectionNo, ExamNo),
    foreign key(CourseNo, SectionNo) references SECTION(CourseNo, SectionNo)
);

create table TAKES(
    SSN char(9),
    CourseNo varchar(10),
    SectionNo varchar(2),
    ExamNo char(3),
    Result char(1),
    primary key(SSN, CourseNo, SectionNo, ExamNo),
    foreign key(SSN) references STUDENT(SSN),
    foreign key(CourseNo, SectionNo, ExamNo) references EXAM(CourseNo, SectionNo, ExamNo)
);

create table CONDUCTED_IN(
    RoomNo int,
    Building varchar(30),
    CourseNo varchar(10),
    SectionNo varchar(2),
    ExamNo char(3),
    primary key(RoomNo, Building, CourseNo, SectionNo, ExamNo),
    foreign key(RoomNo, Building) references CLASS_ROOM(RoomNo, Building),
    foreign key(CourseNo, SectionNo, ExamNo) references EXAM(CourseNo, SectionNo, ExamNo)
);