why you can’t add a foreign key constraint in mysql?

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.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top