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
8. Adding foreign keys to existing tables
Updating and deleting with foreign keys
Summary

Instruction

Great! We can also add foreign keys to existing tables. We use a very similar syntax to the one we use for primary keys. We have these two tables:

CREATE TABLE soccer_team (
  id integer PRIMARY KEY,
  name varchar(32),
  city varchar(32)
);

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

We now want the team_id column in soccer_player to reference soccer_team.id:

ALTER TABLE soccer_player
ADD FOREIGN KEY (team_id) REFERENCES soccer_team (id);

As you can see, the syntax is very similar. The column team_id in soccer_player table will become a foreign key referencing the id column (the primary key) in the soccer_team table.

Just as with primary keys, you can specify your own constraint name for foreign keys:

ALTER TABLE soccer_player
ADD CONSTRAINT soccer_player_fk FOREIGN KEY (team_id) REFERENCES soccer_team (id);

The code above will store the foreign key with the soccer_player_fk name.

Exercise

We have modified the employee and department tables:

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

CREATE TABLE department (
  id integer PRIMARY KEY,
  country varchar(32),
  name varchar(32),
  address varchar(64)
);

Your task is to add a foreign key constraint – the department_id column in the employee table should point to the id column in the department table.