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

SQL Constraints

Introduction
Primary keys
Foreign keys
7. Creating tables with foreign keys
Updating and deleting with foreign keys
Summary

Instruction

Now that we remember what primary keys are, let's do a quick recap of foreign keys.

A foreign key is a field or a group of fields that refers to the primary key of another table. Foreign keys are used to link two tables together.

Let's say we have a table with tennis coaches:

CREATE TABLE coach (
  id integer PRIMARY KEY,
  first_name varchar(32),
  last_name varchar(32)
);

Now, we would like to create a table with tennis players. One of the fields will be named coach_id and will refer to the coach table:

CREATE TABLE player (
  id integer PRIMARY KEY,
  first_name varchar(32),
  last_name varchar(32),
  country varchar(32),
  coach_id integer,
  FOREIGN KEY (coach_id) REFERENCES coach (id)
);

As you can see, we've added 

FOREIGN KEY (coach_id) REFERENCES coach (id)
at the end. This indicates that the coach_id column is a foreign key which references the id column in the coach table.

Exercise

The company already has an the employee table, which was created in the following way:

CREATE TABLE employee (
  id integer PRIMARY KEY,
  first_name varchar(32),
  last_name varchar(32),
  department varchar(32)
);

They now want to recreate the project table so that it uses a foreign key to link to the employee table.

Create the project table with the following columns:

  1. id – An integer and the primary key.
  2. name – Up to 64 characters.
  3. description – A longer text.
  4. manager_id – An integer.

The manager_id column is a foreign key, referencing the id column in the employee table.