Sleutels en constraints

In de voorgaande hoofdstukken hebben we gekeken naar de belangrijkste bewerkingen die we op de data in een database kunnen uitvoeren. Voordat we nu echter verder kunnen moeten we eerst wat dieper ingaan op de opbouw van een tabel binnen een database en de onderlinge relatie tussen tabelen.

Sleutels en indexen
Bij het aanmaken van de tabel werknemers hebben we al kort kennnis gemaakt met het gebruik van sleutels in een tabel. Op de kolom id hebben we namelijk een primaire sleutel, ook wel PRIMARY KEY, aangebracht.

Een PRIMARY KEY zorgt ervoor dat alle gegevens in die kolom uniek moeten zijn. We zullen dus nooit twee records met dezelfde waarde in die kolom tegenkomen. Er kan maximaal één PRIMARY KEY per tabel voorkomen, maar iedere tabel heeft er een nodig.

Naast de primaire sleutel kunnen ook een UNIQUE KEY en INDEX op een kolom aanbrengen. De eerste doet hetzelfde als de PRIMARY KEY maar mag wel meerdere keren in een tabel voorkomen. Een INDEX wordt gebruikt om de data in die kolom te indexeren zodat bepaalde queries sneller uitgevoerd kunnen worden.

Meer informatie over sleutels en indexen in MySQL vind je in mijn MySQL Indexes tutorial.

Aanbrengen van sleutels en indexen (SQL)
Het aanbrengen van sleutels en indexen op een tabel kunnen we op verschillende manieren doen. Allereerst kunnen we daar phpMyAdmin voor gebruiken. Als we daar de structuur van een tabel bekijken zien we in het 'action' veld van een kolom verschillende knopjes.

- PRIMARY KEY
- UNIQUE KEY
- INDEX

Ook kunnen we een query gebruiken om sleutels en indexen op een tabel aan te brengen. Zo'n query ziet er bijvoorbeeld zo uit:

Met deze query zouden we een PRIMARY KEY op de kolom id aanbrengen.

Foreign Key Constraints
Zoals ik eerder al vertelde is het opzetten van een goed datamodel belangrijk. In een goed datamodel zullen er altijd onderlinge relaties tussen de verschillende tabellen zijn. Zonder die relaties hangt alles als een los hoopje zand aan elkaar en is de kans op corrupte data vrij groot.

Om die onderlinge relaties aan te brengen gebruiken we zogenaamde foreign key constraints. We koppelen als het ware een kolom uit de ene tabel aan een kolom uit een andere tabel. Helaas is de InnoDB engine de enige die het gebruik van foreign keys ondersteund, dit is ook de voornaamste reden waarom wij deze gebruiken.

Voordat we deze relaties aan kunnen gaan brengen hebben hebben we eerst een tweede tabel nodig waarin we gegevens op gaan slaan. In dit voorbeeld gebruiken we een tabel functies waarin de functie van een werknemer staat. De SQL om de benodigde tabellen aan te maken ziet er als volgt uit:

We zien een nieuwe tabel functies met daarin een kolom voor het id en een kolom voor de functienaam. We zien ook dat onze werknemers tabel uitgebreid is met een extra kolom functie_id.

Foreign key constraint aanbrengen (SQL)
Zoals je wellicht wel verwacht had, is de kolom functie_id de zogenaamde foreign key uit de werknemers tabel die verwijst naar de functies tabel. Het aanbrengen van de relatie doen we met de volgende query:

Met deze query zal er automatisch een INDEX aangemaakt worden op de kolom functie_id en zal een onderlinge relatie gelegd worden met de kolom id uit de functies tabel.

De ON DELETE en ON UPDATE regels geven aan welke handeling uitgevoerd moet worden als een waarde uit de kolom functies.id gewijzigd of verwijderd wordt. RESTRICT houdt in dat de bewerking tegengegaan wordt en CASCADE zorgt ervoor dat de foreign key ook geupdate wordt.

De andere methode is door de onderlinge relatie in de relation view van phpMyAdmin in te stellen. Deze relation view ziet er als volgt uit:


Hierin kunnen we aangeven dat de kolom functie_id verwijst naar de kolom functies->id, oftewel de kolom id uit de functies tabel.

Het aanbrengen van deze onderlinge relaties is belangrijk, zorg dus dat je dat altijd op een nette manier doet. Zonder deze relaties kun je de gegevens in je database namelijk niet vertrouwen.

« Lees de omschrijving en reacties