Model 1:N relationships with foreign keys

Introduction to Relational Databases in SQL

Timo Grossenbacher

Data Journalist

The current database model

The next database model

Implementing relationships with foreign keys

A foreign key (FK) points to the primary key (PK) of another table
Domain of FK must be equal to domain of PK
Each value of FK must exist in PK of the other table (FK constraint or "referential integrity")
FKs are not actual keys

A query

SELECT * FROM professors LIMIT 8;

 id |    firstname    |  lastname   | university_s..
  --+-----------------+-------------+-------------
  1 | Karl            | Aberer      | EPF
  2 | Reza Shokrollah | Abhari      | ETH
  3 | Georges         | Abou Jaoudé | EPF
  4 | Hugues          | Abriel      | UBE
  5 | Daniel          | Aebersold   | UBE
  6 | Marcelo         | Aebi        | ULA
  7 | Christoph       | Aebi        | UBE
  8 | Patrick         | Aebischer   | EPF

SELECT * FROM universities;

 id  |   university   | university_city
<hr />--+----------------+-----------------
 EPF | ETH Lausanne   | Lausanne
 ETH | ETH Zürich     | Zurich
 UBA | Uni Basel      | Basel
 UBE | Uni Bern       | Bern
 UFR | Uni Freiburg   | Fribourg
 UGE | Uni Genf       | Geneva
 ULA | Uni Lausanne   | Lausanne
 UNE | Uni Neuenburg  | Neuchâtel
 USG | Uni St. Gallen | Saint Gallen
 USI | USI Lugano     | Lugano
 UZH | Uni Zürich     | Zurich

Specifying foreign keys

CREATE TABLE manufacturers (
 name varchar(255) PRIMARY KEY);

INSERT INTO manufacturers 
VALUES ('Ford'), ('VW'), ('GM');

CREATE TABLE cars (
 model varchar(255) PRIMARY KEY,
 manufacturer_name varchar(255) REFERENCES manufacturers (name));

INSERT INTO cars 
VALUES ('Ranger', 'Ford'), ('Beetle', 'VW');

-- Throws an error!
INSERT INTO cars 
VALUES ('Tundra', 'Toyota');

Specifying foreign keys to existing tables

ALTER TABLE a 
ADD CONSTRAINT a_fkey FOREIGN KEY (b_id) REFERENCES b (id);

Let's implement this!

Introduction to Relational Databases in SQL