Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Creating Tables in SQL

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)
);