Introduction
Foreign keys
11. Creating tables with foreign keys on your own
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:

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

Console

Code editor

Result

TableConsole