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

The update failed again! The database keeps an eye on the employees too.

Of course, it's important to control the values inserted into column manager_id in the table project, but if you take a look from the other side – the column id in the table employee must be under supervision too. If you changed the id of an employee who's a manager on a project, the project would have an incorrect manager_id value. Quite understandable, right?

Exercise

It's your turn to try to create a table with a foreign key.

We want another table in our database. It's called department and contains the following columns (all with the non-NULL values):

  • id – integer and primary key,
  • name varchar of up to 100 characters,
  • manager_id – an integer. It is also a foreign key which points to the column id in the table employee.
Do you remember how to create a foreign key? If you want to create a foreign key constraint on column1 which points to column2 in table2, you can write: 

FOREIGN KEY(column1) REFERENCES table2(column2)

Stuck? Here's a hint!

Type:

CREATE TABLE department (
  id int PRIMARY KEY,
  name varchar(100)  NOT NULL,
  manager_id int  NOT NULL,
  FOREIGN KEY(manager_id) REFERENCES employee(id)
);