SQL Primary Key: een uitgebreide technische tutorial

In relationele databases speelt de primary key een cruciale rol bij het waarborgen van de uniciteit en integriteit van gegevens binnen een tabel. Een primary key is een constraint die elk record in een tabel uniek identificeert en fungeert als fundament voor indexering, het afdwingen van entity-integriteit en het opzetten van relaties tussen tabellen.

In deze tutorial neem ik je mee in een diepgaande verkenning van SQL-primary keys, met de kernprincipes, implementatiemethoden en optimalisatietechnieken. Ik bespreek ook veelgemaakte fouten en best practices, plus scenario’s uit de praktijk. Als je nog SQL aan het leren bent, raad ik je aan om onze uitgebreide SQL-roadmap te bekijken en onze SQL Fundamentals-skilltrack te volgen. 

Wat is een SQL-primary key?

Een SQL-primary key is een kolom of een set kolommen die elke rij in een tabel uniek identificeert. Dit betekent dat geen twee rijen dezelfde primary key-waarde mogen hebben. Een tabel kan slechts één primary key hebben, die uit één of meerdere kolommen kan bestaan.

Primary keys zijn essentieel om dataintegriteit af te dwingen en efficiënt te kunnen query’en. Laten we enkele basisaspecten bekijken.

Primary keys zijn onmisbaar voor database-normalisatie en relationele integriteit.

Belangrijkste eigenschappen van primary keys

1. Uniciteit: Zorgt ervoor dat elke waarde in de primary key-kolom(men) uniek is.
2. Niet-nullbaar: Primary key-kolommen mogen geen NULL-waarden bevatten.
3. Onveranderlijkheid: Idealiter blijven primary key-waarden gedurende hun levensduur ongewijzigd om inconsistenties in verwijzende tabellen te voorkomen.

Constraints en indexering

Wanneer een primary key wordt gedefinieerd, maakt de database automatisch een unieke index aan om de constraint af te dwingen. Deze index versnelt de queryprestaties, vooral bij opzoekbewerkingen.

Via dit indexsysteem helpen primary keys de database-engine bij het optimaliseren van uitvoeringsplannen. Daardoor draaien je queries sneller.

Veelvoorkomende datatypes voor primary keys

Primary keys kunnen verschillende typen data gebruiken. Hier zijn enkele veelgebruikte types:

• INTEGER of SERIAL: Efficiënt en het meest gebruikt.
• BIGINT: Handig voor tabellen die miljarden rijen moeten opslaan.
• UUID: Goed voor gedistribueerde systemen en om sequentiële voorspelbaarheid te vermijden.
• CHAR/VARCHAR: Voorzichtig gebruiken, typisch bij natuurlijke sleutels waar nodig.

Typen SQL-primary keys en architectuur

Primary keys kunnen worden ingedeeld op basis van hun structuur en herkomst. Enkele voorbeelden:

• Natuurlijke sleutels: Afgeleid van bestaande data (bijv. burgerservicenummer, e-mail) en gemakkelijk te begrijpen en vaak betekenisvol voor zakelijke gebruikers. Dit brengt echter risico op updates, duplicatie en mogelijke privacyproblemen met zich mee.
• Surrogaat-sleutels: Kunstmatige sleutels, zoals systeemgegenereerde ID’s, die meestal worden geïmplementeerd met SERIAL, BIGSERIAL of UUID. Zulke sleutels bieden stabiliteit, privacy en eenvoudiger onderhoud.

Door ze op kenmerken te vergelijken, komen hun verschillen naar voren in de volgende samenvattingstabel:

Architectuur van samengestelde primary keys

Een samengestelde primary key is een veelgebruikte manier om een unieke sleutel in tabellen te genereren. De sleutel is doorgaans afgeleid van gegevens in twee of meer kolommen.

Dergelijke sleutels komen vaak voor in koppel- of associatietabellen waar de relatie zelf uniciteit vereist.

• Voorbeeld: Een tabel course_enrollments kan een samengestelde sleutel gebruiken van student_id en course_id.

Overwegingen:

• Minimalisme: Vermijd onnodige kolommen in de samengestelde sleutel.
• Complexiteit: Joins en foreign key-verwijzingen worden complexer.
• Indexgrootte: Indexen kunnen groot worden en prestaties beïnvloeden.

Aanvullende sleutels in databases

Naast de primary key zijn er verschillende andere sleutels die je kunt gebruiken, zoals:

• Candidate key: Een set attributen die een rij uniek kan identificeren.
• Super key: Elke superset van een candidate key (bevat extra kolommen).
• Alternate key: Een candidate key die niet als primary key is gekozen.

Als je beslist welk data-veld je als primary key wilt gebruiken, kun je met de volgende factoren rekening houden:

• Frequentie van gegevenswijzigingen: Hoe vaak verandert de data?
• Datatype en -grootte van de kolom: Is het datatype geschikt voor indexering?
• Verwachte querypatronen: Is dit veld geschikt om op te query’en?

Primary keys definiëren en wijzigen in SQL

Laten we vervolgens kijken hoe we primary keys in PostgreSQL kunnen declareren en beheren met SQL-commando’s. 

We lopen diverse scenario’s door, waaronder het aanmaken van tabellen, het wijzigen van bestaande tabellen en het beheren van constraint-gedrag.

Declaratie- en wijzigingssyntaxis

Tijdens het aanmaken van de tabel

Bij het aanmaken van een tabel leg je velden vast die waarschijnlijk nodig zijn. In dit geval genereren we een werknemer-ID die uit unieke integers bestaat.

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,  -- Automatically generates unique integers
    name TEXT NOT NULL,              -- Basic text field, required
    email TEXT UNIQUE                -- Unique constraint to prevent duplicate emails
);Mogelijk gemaakt door

Meer over SQL-commando’s vind je in onze SQL Basics Cheat Sheet.

Dit voorbeeld laat zien hoe je inline een primary key definieert met het pseudo-type SERIAL. PostgreSQL behandelt SERIAL als een auto-increment integerkolom, ideaal voor surrogaat-sleutels. De e-mailkolom krijgt ook een UNIQUE-constraint en kan zo dienen als alternate key.

Met ALTER TABLE

Laten we nu employee_id instellen als primary key.

ALTER TABLE employees ADD PRIMARY KEY (employee_id);Mogelijk gemaakt door

Dit commando gebruik je wanneer een tabel al is aangemaakt zonder primary key en je die achteraf wilt toevoegen. Zorg dat de kolom waaraan je de primary key toevoegt geen NULLs of dubbele waarden bevat, anders mislukt het commando.

Primary key verwijderen

Nu onze primary key is ingesteld, kun je de primary key-constraint ook verwijderen.

ALTER TABLE employees DROP CONSTRAINT employees_pkey;Mogelijk gemaakt door

Gebruik deze instructie om een bestaande primary key-constraint te verwijderen. De constraint-naam employees_pkey volgt de standaard naamgevingsconventie van PostgreSQL (<table>_pkey). Dit is handig bij het herontwerpen van het schemas of het overschakelen naar een andere primary key-kolom.

In PostgreSQL is de standaardnaam voor primary key-constraints table_name_pkey.

Leer meer over PostgreSQL in onze cheat sheet.

Null-afhandeling en botsingsbeheer

NULL-waarden zijn niet toegestaan in primary key-kolommen. Dubbele waarden veroorzaken ook constraint-fouten.

Enkele strategieën om dit te beheren zijn:

• INSERT ... ON CONFLICT gebruiken om duplicaten te vermijden.
• Transacties gebruiken om de beschikbaarheid van sleutels te controleren vóór inserts.

Auto-increment en strategieën voor globale identifiers

De types SERIAL, BIGSERIAL en IDENTITY in PostgreSQL vereenvoudigen ID-generatie.

Daarnaast bieden UUID’s veilige, globale identifiers. Hun niet-sequentiële en onvoorspelbare karakter verhoogt ook de veiligheid, vooral tegen enumeratie-aanvallen. 

Hier zie je hoe je een UUID kunt aanmaken en gebruiken als primary key

CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE TABLE users (
    user_id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    username TEXT NOT NULL
);Mogelijk gemaakt door

Primary keys in relaties en prestaties verbeteren

Primary keys zijn essentieel om consistentie tussen relationele tabellen te waarborgen en prestaties te verbeteren.

Afdwingen van entity-integriteit

Entity-integriteit wordt afgedwongen via primary keys door NULLs en duplicaten te verbieden. Een unieke identifier helpt gegevensanomalieën tijdens insert/update-bewerkingen te voorkomen.

Foreign keys en referentiële integriteit

Foreign keys moeten overeenkomen met bestaande primary key-waarden in de verwezen tabel. Referentiële integriteit zorgt ervoor dat alle verwijzingen tussen tabellen geldig blijven en voorkomt "weesrecords" waarbij records naar niet-bestaande data verwijzen.

Cascading-acties behouden de consistentie wanneer verwezen primary keys veranderen. 

Dit kan onder meer de volgende veelgebruikte cascadefuncties omvatten:

• ON DELETE CASCADE
• ON UPDATE CASCADE
• SET NULL en SET DEFAULT

Indexering en query-optimalisatie

Unieke indexen op primary keys versnellen SELECT- en JOIN-queries. Slechte sleutelontwerpen kunnen echter leiden tot opgeblazen indexen en slechtere prestaties.

Als je queries traag zijn en je vermoedt dat dit komt door slechte indexering, gebruik dan EXPLAIN ANALYZE om queryplannen te inspecteren en het indexgebruik te beoordelen.

Ontwerp-best practices en mogelijke valkuilen

Goed sleutelontwerp bevordert onderhoudbaarheid, prestaties en schaalbaarheid. Laten we enkele best practices en veelgemaakte fouten bekijken.

Richtlijnen voor sleutelselectie

• Kies korte, vaste-lengte datatypes (bijv. INT, UUID).
• Vermijd sleutels die waarschijnlijk veranderen.
• Zorg dat de sleutel geen ingebedde zakelijke betekenis heeft om verstrengeling van logica te vermijden.
• Gebruik surrogaat-sleutels voor eenvoud en normalisatie.

Veelvoorkomende implementatiefouten

• Meerdere primary keys gebruiken (er is per tabel maar één toegestaan).
• Unieke constraints verkeerd labelen als primary keys.
• Geen primary keys definiëren, wat leidt tot weesrecords.

Mogelijke oplossingen:

• Ontbrekende sleutels toevoegen via ALTER TABLE.
• Schemasontwerp normaliseren.
• Consistente naamgevingsconventies gebruiken.

Schaalbaarheidsoverwegingen

Wanneer je een databaseoplossing ontwerpt die kan schalen, gebruik bij voorkeur surrogaat-sleutels om uniformiteit te behouden over grote datasets. Vermijd samengestelde sleutels in gedistribueerde systemen, tenzij noodzakelijk. Regelmatige monitoring van je tabel-/indexgrootte is ook een must.

Geavanceerde implementatiescenario’s

Het omgaan met primary keys in complexe systemen, zoals gedistribueerde databases of legacy-systemen, vereist geavanceerde strategieën.

Gedistribueerde databasesystemen

Gedistribueerde databases zijn lastiger. Je moet zorgen dat sleutelgeneratie conflictvrij is over nodes. Gebruik hiervoor bijvoorbeeld UUID’s en gecentraliseerde keymanagementservices

Overwegingen bij partitioneren:

• Zorg dat sleutels geen hotspots creëren.
• Gebruik gehashte of gerandomiseerde sleutels bij sharding.

Migratie van legacy-systemen

Bij een systeemmigratie van een legacy-database identificeer je eerst bestaande unieke identifiers. Introduceer vervolgens geleidelijk surrogaat-sleutels naast natuurlijke sleutels.

Kies voor een voorzichtige aanpak door tijdelijk beide sleutels te ondersteunen voordat je volledig overstapt.

Migratieproces:

1. Kolom voor surrogaat-sleutel toevoegen.
2. Sleutels voor bestaande records vullen.
3. Applicatiequeries aanpassen.
4. Oude natuurlijke sleutels verwijderen na verificatie.

Demonstratie van primary keys met PostgreSQL

Voordat je tabellen maakt en data invoert, heb je een werkende PostgreSQL-omgeving nodig. Dit deel begint met een korte gids voor het opzetten van PostgreSQL, gevolgd door instructies om een voorbeeld-dataset te definiëren en te gebruiken om primary key-concepten te oefenen.

Stap 1: Eerste setup voor PostgreSQL

Als je PostgreSQL nog niet hebt geïnstalleerd, volg dan deze stappen om te beginnen:

1. Installeer PostgreSQL
• Bezoek de officiële website: https://www.postgresql.org/download/
• Kies je besturingssysteem (Windows, macOS of Linux).
• Download en start de installer, en zorg dat je pgAdmin (een grafische interface) samen met de PostgreSQL-server installeert.
2. Start de PostgreSQL-service
• Na installatie start de PostgreSQL-service meestal automatisch.
• Je kunt deze ook handmatig starten via de services van je OS (bijv. Services op Windows of systemctl op Linux).
3. Start pgAdmin of maak verbinding via de CLI
• Gebruik pgAdmin om databases visueel te beheren en te bevragen.
• Of open een terminal en maak verbinding met:psql -U postgres
• Vervang postgres door je eigen PostgreSQL-gebruikersnaam indien anders.
4. Maak een nieuwe database
• Klik in pgAdmin met de rechtermuisknop op "Databases" en kies "Create > Database".

• Of gebruik het volgende SQL-commando:```sql

CREATE DATABASE pk_tutorial;

Connect to the new database in psql with:```bash
\c pk_tutorialMogelijk gemaakt door

Zodra je omgeving klaar is, kun je verdergaan met de rest van de dataset-setup zoals al in de tutorial is beschreven.

Kijk vervolgens naar een stapsgewijze gids om een voorbeeld-dataset te maken met PostgreSQL. De dataset bestaat uit twee gerelateerde tabellen: customers en orders.

De tabel customers gebruikt een surrogaat-sleutel voor eenvoud, terwijl de tabel orders een op UUID gebaseerde primary key gebruikt om globale uniciteit te illustreren.

Stap 2: Tabellen aanmaken

De eerste stap is het schema definiëren en primary key-constraints voor beide tabellen vastleggen. We definiëren ook een foreign key-relatie tussen orders.customer_id en customers.customer_id om relationele integriteit te demonstreren.

Voor de tabel customers is de primary key customer_id.

Voor de tabel orders is de primary key order_id.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY, -- Auto-incremented surrogate key
    name TEXT NOT NULL,
    email TEXT UNIQUE -- Ensures no duplicate email addresses
);

CREATE TABLE orders (
    order_id UUID DEFAULT gen_random_uuid() PRIMARY KEY, -- Globally unique identifier
    order_date DATE NOT NULL,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE -- Enforces referential integrity
);Mogelijk gemaakt door

Uitleg:

• customer_id gebruikt het sleutelwoord SERIAL om automatisch unieke ID’s te genereren.
• email heeft een UNIQUE-constraint om duplicaten te voorkomen.
• order_id is een UUID die automatisch wordt gegenereerd via gen_random_uuid().
• De clausule ON DELETE CASCADE zorgt ervoor dat bij het verwijderen van een klant ook de bijbehorende orders worden verwijderd.

Zo zou dat eruit moeten zien in de pgAdmin-interface:

Stap 3: Voorbeeldata invoegen

Vervolgens vullen we de tabellen met voorbeeldrecords om te zien hoe primary en foreign keys zich gedragen bij typische insert-bewerkingen. Deze data simuleert een eenvoudige e-commercesituatie.

INSERT INTO customers (name, email) VALUES
('Alice Tan', '[email protected]'),
('Ben Koh', '[email protected]'),
('Clara Lim', '[email protected]');

INSERT INTO orders (order_date, customer_id) VALUES
('2025-07-01', 1),
('2025-07-02', 2),
('2025-07-03', 3);Mogelijk gemaakt door

Zo zou dat eruit moeten zien in de pgAdmin-interface:

Stap 4: Data opvragen

Tot slot voeren we een SQL-JOIN-query uit om informatie uit beide tabellen op te halen, waarmee we laten zien hoe de foreign key naadloze relationele joins tussen orders en klanten mogelijk maakt.

Hier is de code die we gaan draaien:

SELECT o.order_id, o.order_date, c.name, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;Mogelijk gemaakt door

Hier is de voorbeeldoutput:

Zoals je in de bovenstaande afbeelding ziet, toont de uitvoertabel gegevens uit beide tabellen. Zo bevat de samengevoegde tabel bijvoorbeeld het veld order_date uit de tabel orders en het veld name uit de tabel customers.

Conclusie

SQL-primary keys vormen de basis van relationele databases: ze dwingen uniciteit af, maken efficiënte queries mogelijk en ondersteunen dataintegriteit. 

We hebben de principes, classificaties, syntaxis, best practices en geavanceerde strategieën voor SQL-primary keys behandeld. Voor meer leermateriaal, bekijk onze cursus Introduction to Relational Databases in SQL of de cursus Joining Data in SQL.

Als je liever tutorials leest, zijn de gids over integrity constraints in SQL of de SQL Joins Cheat Sheet ook erg behulpzaam.