Best April deals - hours only!Up to 80% off on all courses and bundles.-Close
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)
);