MYSQL - Cannot add foreign key constraint, but why?

Question

I don't understand I get cannot add foreign key constraint when trying to run this code. I narrowed it down to the last line:

FOREIGN KEY (room_no) REFERENCES Classroom(room_no)

giving me this error, as when I delete it the code runs fine. Could someone explain the issue to me? This code is part of a much bigger code block, and I seem to run into the same problem later on. Thank you!

CREATE TABLE Classroom (
    building VARCHAR(100),
    room_no SMALLINT UNSIGNED,
    capacity SMALLINT UNSIGNED,
    CONSTRAINT PK_Prereq PRIMARY KEY (building, room_no)
);

CREATE TABLE Section (
    course_id INT UNSIGNED,
    sec_id INT UNSIGNED,
    semester SMALLINT UNSIGNED,
    year SMALLINT UNSIGNED,
    building VARCHAR(100),
    room_no SMALLINT UNSIGNED,
    time_slot_id INT UNSIGNED,
    CONSTRAINT PK_Prereq PRIMARY KEY (course_id, sec_id, semester, year),
    FOREIGN KEY (building) REFERENCES Classroom(building),
    FOREIGN KEY (room_no) REFERENCES Classroom(room_no)
); 

Answer 1

You want just one foreign key instead of two, that references the set of columns that is the primary key in the parent table.

So change this:

FOREIGN KEY (building) REFERENCES Classroom(building),
FOREIGN KEY (room_no) REFERENCES Classroom(room_no)

To:

FOREIGN KEY (building, roo_no) REFERENCES Classroom(building, roo_no)

It is important to note that:

• the datatypes (and length, or precision) of the columns must be identical

• the columns in the foreign key must appear in the same order as in the primary key

Unrelated: year is a language keyword in MySQL, hence not a good choice for a column name.