Introduction
Foreign keys
Multicolumn foreign keys
Updates and deletes
Revision

Instruction

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 multiple column foreign keys too.

Let's say we have a new table called office which describes a physical location of an office in a given building on a given floor:

Office table

Let's say the primary key will consist of two columns: floor and building_name. There may be many offices on the third floor in various building and there may be many offices on different floors in one building, but together, these values form a primary key. We'll now learn how to create the proper multicolumn foreign key for this situation. Take a look, you just need to provide all the columns in the parentheses:

FOREIGN KEY (office_floor,office_building_name)
REFERENCES office (floor,building_name)

The columns office_floor and office_building_name will form a multicolumn foreign key. Excellent!

Exercise

Each department will be located in a specific office, so we need to change our table department so that it contains a multicolumn foreign key pointing to the table office. Take a look at the new ERD for the table department:

Office and department tables

Now take a look at the template code which will create this table with a multicolumn foreign key.

As you may remember, the column manager_id used to be a foreign key, and now it's not. This is because we don't know how to create more than one foreign key in a table yet. Don't worry, we'll get to that in a second. For now, focus on creating a multicolumn foreign key.

Console

Code editor

Result

TableConsole