That's right. Okay, time to move on. So far, we've created foreign keys on single columns. We know, however, that primary keys may comprise multiple columns, so there must be a way to create multi-column foreign keys too.
Let's say we have a new table called office that describes the physical location of an office in a given building on a given floor:
Next, let's say the primary key will consist of two columns: floor and building_name. There could be many offices on the third floor in various buildings or many offices on different floors in one building. Either way, these values together form a primary key. We'll now learn how to create the proper multi-column foreign key for this situation. Take a look. You just need to provide all the columns in parentheses:
FOREIGN KEY (office_floor, office_building_name) REFERENCES office (floor, building_name)
The columns office_floor and office_building_name will form a multi-column foreign key. Excellent!
Each department will be located in a specific office, so we need to change our department table for it to contain a multi-column foreign key pointing to the office table. Take a look at the new ERD for the department table:
Now, take a look at the template code which will create this table with a multi-column foreign key.
As you may remember, the column manager_id used to be a foreign key, but it is not anymore. This change is because we don't know how to create more than one foreign key in a table yet. But don't worry! We'll get to that part in a second. For now, focus on creating a multi-column foreign key.
