Introduction
Foreign keys
Multicolumn foreign keys
Updates and deletes
Revision

Instruction

Excellent! Okay, we've promised to teach you how to create multiple foreign keys in a single table and here we go:

CREATE TABLE table1 (
    column1 int NOT NULL,
    column2 int NOT NULL,
    FOREIGN KEY(column1) REFERENCES table2(column3),
    FOREIGN KEY(column2) REFERENCES table3(column4)
);

As you can see, if you want to create another foreign key, you just need to repeat the whole FOREIGN KEY construction after a comma. Do not provide all the column names inside one pair of parentheses - this won't create multiple foreign keys, but a single multicolumn foreign key instead!

Exercise

Let's create the table department once again - this time with two separate foreign keys:

  • one on column manager_id pointing to employee.id and
  • the other on two columns: office_floor (referring to office.floor) an office_building_name (referring to office.building_name).
Use the ERD below to help you:

Employee, office and department tables

Stuck? Here's a hint!

Type:

CREATE TABLE department (
    id int PRIMARY KEY,
    name varchar(1000)  NOT NULL,
    manager_id int  NOT NULL,
    office_floor int  NOT NULL,
    office_building_name varchar(255)  NOT NULL,
    FOREIGN KEY (office_floor,office_building_name) REFERENCES office (floor,building_name), FOREIGN KEY (manager_id) REFERENCES employee(id)
);

Console

Code editor

Result

TableConsole