SQL keys uitleg (primary key / foreign key): relaties tussen tabellen

Een relationele database bestaat meestal uit meerdere tabellen die onderling relaties (ook interessant: deepdive in SQL relationships) hebben. Deze relaties geef je aan met sleutels (keys) . Hierbij maken we onderscheid tussen Primary keys en Foreign keys.

In dit blog bekijken we:
1. Waarom zou je meerdere tabellen gebruiken in een database?
2. Wat is een Primary key?
3. Wat is een Foreign key?
4. Hoe gebruik je keys als contraints bij aanmaak van een tabel?

1. Waarom zou je meerdere tabellen gebruiken in een database?

We werken in deze tutorial met de IMDb database die we in deze blog toelichten en waar je tevens de dataset kunt downloaden om mee te doen. Als je mee wilt doen met deze tutorial dan dien je tevens SQL en PgAdmin te installeren als je nog geen werkende SQL set-up hebt.

Introductie meerdere tabellen

De database bevat meerdere SQL tabellen, waaronder tabel movies.

In deze tabel is iedere rij een unieke film.

Bijvoorbeeld:

Columns: 4
Rows: 36

We hebben in deze tabel informatie van 36 films.

Ook is er tabel roles.

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

Bijvoorbeeld:

Columns: 3
Rows: 1.989

We hebben dus 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:

Columns: 6
Rows: 1.989

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

Hoe kun je datapunten verminderen met meerdere tabellen in SQL?

We werken nu 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. Je kunt hier meer over lezen in andere blogs van ons.

**Ook interessant: uitleg & tutorial SQL LEFT JOIN

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 kwamen we op 11.934 datapunten. Met 2 tabellen komen we op 6.111 datapunten. Met 2 tabellen hebben we dus bijna de helft van het aantal datapunten. Dat is een enorme winst in computerkracht die nodig is om door een tabel heen te gaan en het zorgt voor meer snelheid aan de gebruikerskant. Daarnaast vermindert het de benodigde data-opslag natuurlijk fors.

Een tweede voordeel is dat meerdere tabellen zorgen voor overzicht. Iedere tabel behandelt een specifiek onderwerp. Datapunten die niet relevant zijn voor het onderwerp verstoren de aandacht dus niet.

Een laatste voordeel is dat aanpassingen makkelijk doorgevoerd kunnen worden, omdat data maar op één centrale plaats staat opgeslagen.

Nu duidelijk is waarom meerdere tabellen wenselijk zijn, gaan we in op de cruciale rol die SQL keys hierin spelen.

2. Wat is een Primary key?

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.

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.
  • Met de relatie movies.id <> roles.movie_id.

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

• Een uniek nummer voor iedere unieke film.

Eigenschappen SQL primary key

Primary keys hebben de volgende eigenschappen:

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

3. Wat is een Foreign key?

We hebben gezien wat de primary key is.

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

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 de primary key.

Een kolom die linkt naar een primary key in een andere tabel, is een foreign key.

• Kolom movie_id in tabel roles is dus een foreign key.

Eigenschappen SQL foreign key

Foreign keys hebben de volgende eigenschappen:

• Het is een kolom link naar de primary key in een andere tabel.
• Iedere tabel kan meerdere foreign keys bevatten.
• De foreign key mag leeg (NULL) zijn, er hoeft dus niet altijd een link te zijn.

4. Hoe gebruik je keys als contraints bij aanmaak van een tabel?

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.

Samenvatting SQL keys introductie

• Data opdelen in meerdere tabellen:
  • Er is minder opslagcapaciteit nodig.
  • Het is overzichtelijker.
• Keys (sleutels) beschrijven de relaties tussen tabellen.
  • Primary keys:
    • De kolom(men) 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.
  • Foreign keys:
    • De kolom die linkt naar een primary key in een andere tabel.
    • Vaak aangeduid in een schema met FK.
    • Meerdere foreign keys per tabel mogelijk.
    • Mag wel leeg (NULL) zijn.
• Constraints, bij aanmaken tabel:
  • PRIMARY KEY:
    • Combinatie van UNIQUE en NOT NULL.
    • Uniek en geen missende waarden.
  • FOREIGN KEY:
    • Waarborgt een relatie tussen tabellen.

Wil je nog veel meer leren over SQL? Bekijk dan onze SQL cursus voor data analyse of download onderstaande opleidingsbrochure. In onze SQL cursus wisselen theorie en praktijkoefening elkaar steeds af. Nadat je deze training hebt gevolgd kun je zelfstandig met SQL werken. Je kunt ook altijd even contact opnemen via info@datasciencepartners.nl of 020 - 24 43 146 als je een vraag hebt.