Primary en foreign keys

Introductie

Een database bestaat meestal uit:

• Meerdere tabellen.
• Die onderling relaties hebben.

Deze relaties definieer je door sleutels (keys) aan kolommen te koppelen.

• Primary keys.
• Foreign keys.

In deze module bekijken we:

1. Waarom meerdere tabellen met onderlinge relaties handig zijn.
2. Wat primary- en foreign keys zijn.

---

1. Relaties tussen tabellen

We werken met de IMDb database.

Hierin staat data van films.

---

1.1. Tabel movies

De database bevat meerdere tabellen, waaronder tabel movies.

In deze tabel is iedere rij een unieke film.

Bijvoorbeeld:

id	name	year	rank
10920	Aliens	1986	8.2
17173	Animal House	1978	7.5
18979	Apollo 13	1995	7.5
...	...	...	...

Columns: 4
Rows: 36

We hebben in deze tabel informatie van 36 films.

---

1.2. Tabel roles

Ook is er tabel roles.

In deze tabel is iedere rij een rol uit een film.

Bijvoorbeeld:

actor_id	movie_id	role
16844	10920	Lydecker
36641	10920	Russ Jorden
...	...	...
22591	17173	Chip Diller
43842	17173	Charming guy with guitar
...	...	...
7979	18979	Anchor
9275	18979	Mission Controller
...	...	...

Columns: 3
Rows: 1.989

---

1.3. Koppeling tussen movies en roles

1.3.1. Data in 1 tabel

We hebben data van:

• Films
• Rollen in films

Dit zouden we in 1 tabel kunnen verwerken.

Iedere rij is dan een rol, met bijbehorende film.

Zie onderstaand voorbeeld:

id	name	year	rank	actor_id	role
10920	Aliens	1986	8.2	16844	Lydecker
10920	Aliens	1986	8.2	36641	Russ Jorden
...	...	...	...	...	...
17173	Animal House	1978	7.5	22591	Chip Diller
17173	Animal House	1978	7.5	43842	Charming guy with guitar
...	...	...	...	...	...
30959	Batman Begins	2005	(NULL)	7979	Anchor
30959	Batman Begins	2005	(NULL)	9275	Mission Controller
...	...	...	...	...	...

Columns: 6
Rows: 1.989

In dit geval zijn er 6 kolommen * 1.989 rijen = 11.934 datapunten.

---

1.3.2. Data in 2 tabellen: movies en roles

We werken met twee tabellen.

• Tabel movies bevat kolom id
  • Een uniek nummer voor iedere unieke film.
• Tabel roles bevat kolom movie_id
  • Hiermee kun je tabellen movies en roles koppelen.

Met het JOIN commando kun je data uit tabellen samenvoegen. Hier staan we later uitgebreid bij stil.

In dit geval hebben we 2 tabelen:

• movies: 4 kolommen * 36 rijen = 144 datapunten.
• roles: 3 kolommen * 1.989 rijen = 5.967 datapunten.

Dit maakt een totaal van 144 + 5.967 = 6.111 datapunten.

• Met 1 tabel: 11.934 datapunten.
• Met 2 tabellen: 6.111 datapunten.
• Met 2 tabellen bijna de helft van het aantal datapunten.

---

1.4. Voordelen van meerdere tabellen

We hebben in het voorbeeld van tabellen movies en roles gezien dat 2 tabellen tot aanzienlijk minder datapunten leidt.

Dit heeft enkele voordelen:

• Er is minder opslagcapaciteit nodig.
• Het is overzichtelijker.
  • Specifieke details.
    • movies bevat alleen details op hoofdniveau van films
    • roles bevat alleen details op hoofdniveau van rollen
  • Aanpassingen.
    • Als je een kenmerk van één film aan wilt passen, hoef je dit maar op één plaats aan te passen.

---

2. Primary keys

2.1. Introductie

We weten nu dat een datamodel met meerdere tabellen met onderlinge relaties voordelen heeft.

Hiervoor dienen de relaties tussen tabellen gedefinieerd te zijn.

Deze relaties geef je aan met sleutels (keys) .

Allereerst de primary key. Hiervoor kijken we weer naar de tabellen movies en roles.

Tabel movies

• In tabel movies is iedere rij een unieke film.
• Kolom id (identifier) bevat een uniek nummer voor iedere film.

Tabel roles

• In tabel roles is iedere rij een unieke rol in een film.
• Kolom movie_id bevat het id van een film.

---

2.2. Primary key in tabel movies

Kolom movie_id uit tabel roles bevat waarden die terug te vinden zijn in kolom id in tabel movies.

• Hierdoor zijn tabellen movies en roles aan elkaar gekoppeld via de kolommen movies.id en roles.movie_id.

Kolom id in tabel movies is hier de primary key (primaire sleutel).

• Een uniek nummer voor iedere unieke film.

---

2.3. Eigenschappen

Primary keys hebben de volgende eigenschappen:

• Het is een kolom (of combinatie van kolommen), die rijen in een tabel uniek maakt.
• Iedere tabel kan slecht één primary key hebben.
• De primary key mag niet leeg (NULL) zijn.

---

3. Foreign keys

3.1. Introductie

We hebben gezien wat de primary key is.

Nu bekijken we de foreign key. Hiervoor kijken we weer naar de tabellen movies en roles.

---

3.2. Foreign key in tabel roles

In tabel roles bevat kolom movie_id waarden uit kolom id in tabel movies.

• Kolom id in tabel movies is in deze tabel gedefinieërd als primary key.

Een kolom die linkt naar een primary key in een andere tabel moet worden gedefinieërd als een foreign key.

• Kolom movie_id in tabel roles heeft daarom een foreign key op kolom id in de tabel movies.

---

3.3. Eigenschappen

Kolommen met foreign keys hebben de volgende eigenschappen:

• Iedere tabel kan meerdere foreign keys bevatten.
• Het linkt een kolom naar de primary key in een andere tabel.
• De waardes in de kolom met foreign key komen altijd voor in de kolom met primary key in de andere tabel.
• Als er geprobeerd zou worden een waarde aan de foreign-key-kolom toe te voegen die niet in de primary-key-kolom bestaat, zal de database een foutmelding geven.

---

4. Keys als constraints

Met onderstaand voorbeeld brengen we constraints aan bij het aanmaken van een tabel:

CREATE TABLE roles (
  actor_id INT UNIQUE NOT NULL,
  movie_id INT NOT NULL,
  role VARCHAR (50) NOT NULL
);

We gebruiken hierbij de constraints UNIQUE en NOT NULL.

In onze tabel willen we een primary- en foreign key aanbrengen:

Hiervoor kunnen we ook constraints gebruiken:

CREATE TABLE roles (
  actor_id INT PRIMARY KEY,
  movie_id INT FOREIGN KEY,
  role VARCHAR (50) NOT NULL
);

• PRIMARY KEY: Combinatie van UNIQUE en NOT NULL: uniek en geen missende waarden.
• FOREIGN KEY: Waarborgt een relatie tussen tabellen door geen waardes toe te staan in de kolom met foreign key die niet voorkomen in de kolom met primary key.

---

Samenvatting

• Data opdelen in meerdere tabellen:
  • Er is minder opslagcapaciteit nodig.
  • Het is overzichtelijker.
• Keys (sleutels) zijn constraints die de relaties tussen tabellen beschrijven.
  • Primary keys:
    • Toegepast op de kolom die de unieke objecten in een tabel identificeert.
    • Vaak aangeduid in een schema met PK of een 🔑 symbool.
    • Slechts één primary key per tabel.
    • Mag niet leeg (NULL) zijn.
    • Waardes moeten uniek zijn
  • Foreign keys:
    • Toegepast op een kolom die linkt naar een primary key in een andere tabel.
    • Vaak aangeduid in een schema met FK.
    • Meerdere foreign keys per tabel mogelijk.
    • Waardes in een kolom met foreign key moeten voorkomen in de kolom met primary key waaraan gelinkt wordt.