SQL Foreign Key: houd je databasetoewijzingen op orde

Samengestelde en zelfverwijzende foreign keys

Een samengestelde sleutel bestaat uit twee of meer kolommen die samen een record uniek identificeren en die samen moeten worden verwezen. Dit patroon wordt gebruikt wanneer de natuurlijke sleutel meerdere velden beslaat, of als je dubbele relaties wilt voorkomen. Zo kun je een cursusaanbod hebben dat wordt geïdentificeerd door (course_id, semester) of een koppeltabel met student_id en course_id als samengestelde primary key.

-- Defines courses table
CREATE TABLE course_offerings (
    course_id INT,
    semester VARCHAR(10),
    
    -- Composite primary key ensures a course can only be offered once per semester
    PRIMARY KEY (course_id, semester) 
);

-- Create enrollment table
CREATE TABLE enrollment (
    student_id INT,
    course_id INT,
    semester VARCHAR(10),

    -- This foreign key links enrollment records to a valid course offering
    FOREIGN KEY (course_id, semester)
        REFERENCES course_offerings(course_id, semester)
);Mogelijk gemaakt door

Een zelfverwijzende foreign key of recursieve foreign key is een foreign key die verwijst naar de primary key binnen dezelfde tabel. Dit patroon komt vaak voor in hiërarchische data zoals Werknemer → Manager. Zo kun je recursieve relaties weergeven zonder extra tabellen te maken.

In de query hieronder verwijst manager_id naar employee_id in dezelfde tabel.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),

    -- manager_id references employee_id in the same table
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);Mogelijk gemaakt door

Werken met data en integriteit waarborgen

We weten nu dat foreign keys worden gebruikt om dataintegriteit in databases te garanderen. In deze sectie bekijken we de praktische overwegingen bij het werken met foreign keys voor SQL-bewerkingen, veelvoorkomende uitdagingen en hoe je constraints beheert.

INSERT-, UPDATE- en DELETE-bewerkingen

Kijken we hoe deze bewerkingen interageren met foreign keys.

Insert-bewerkingen met foreign keys

Om een kindrecord in te voegen, moet de verwezen parentwaarde bestaan. Zo niet, dan mislukt de insert. De query hieronder zal bijvoorbeeld het record in de tabel orders invoegen als de tabel users een record bevat waar user_id 1 is.

-- users table contains user_id = 1
INSERT INTO orders (order_id, user_id, order_date)
VALUES (100, 1, '2024-02-01');Mogelijk gemaakt door

Als je probeert een record in te voegen voor een gebruiker die niet bestaat in de parenttabel, krijg je een foutmelding die een overtreding van de foreign key-constraint aangeeft.

-- user_id = 999 does NOT exist in users table
INSERT INTO orders (order_id, user_id, order_date)
VALUES (101, 999, '2024-02-01');Mogelijk gemaakt door

ERROR: insert or update on table "orders" violates foreign key constraint "fk_orders_users"Mogelijk gemaakt door

Update-bewerkingen en cascaderend gedrag

Updates kunnen betrekking hebben op de foreign key of op de primary key-tabel. Als je bijvoorbeeld de foreign key in de orders-tabel bijwerkt, slaagt dit alleen als de primary key bestaat in de parenttabel. De update hieronder slaagt bijvoorbeeld alleen als users.user_id = 5 bestaat.

-- updating foreign key value
UPDATE orders
SET user_id = 5
WHERE order_id = 100;Mogelijk gemaakt door

Uit het bovenstaande voorbeeld blijkt dat je de primary key niet kunt updaten zonder ON UPDATE CASCADE, omdat kindrijen nog steeds naar 1 verwijzen. Om dit te voorkomen, laten we eerst de bestaande foreign key-constraint vallen en maken we deze vervolgens opnieuw met ON UPDATE CASCADE ingeschakeld. Met deze methode worden de bijbehorende waarden in orders.user_id automatisch bijgewerkt.

-- Drop existing foreign key constraint
ALTER TABLE orders
DROP CONSTRAINT fk_orders_users;

-- Recreate FK with ON UPDATE CASCADE enabled
ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id)
    REFERENCES users(user_id)
    ON UPDATE CASCADE;   -- Automatically update child rows when parent key changesMogelijk gemaakt door

UPDATE users
SET user_id = 500
WHERE user_id = 1;Mogelijk gemaakt door

Delete-bewerkingen en cascades

Als je probeert een parentrij te verwijderen zonder dat er een cascade-regel is ingesteld, zal de delete mislukken en krijg je een foutmelding. Als je een ON DELETE CASCADE-clausule toevoegt, vertel je de database om alle gerelateerde kindrijen automatisch te verwijderen wanneer het parentrecord wordt verwijderd, zodat alles consistent blijft.

In het onderstaande voorbeeld maken we een foreign key op orders.user_id die verwijst naar users.user_id. Met ON DELETE CASCADE worden alle gerelateerde orders automatisch verwijderd als een gebruiker uit de tabel users wordt verwijderd.

-- Add a new foreign key constraint to the orders table
ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id)
    REFERENCES users(user_id)
    ON DELETE CASCADE;             -- Delete orders automatically when user is deletedMogelijk gemaakt door

Soms wil je dat de database de verwijderingen automatisch afhandelt. Je kunt dit afdwingen met ON DELETE SET NULL bij het aanmaken van de tabel. Dit zet de foreign key-kolommen in de kindtabel op NULL als het parentrecord verandert of wordt verwijderd.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,               -- FK referencing users.user_id
    order_date DATE,
    FOREIGN KEY (user_id)
        REFERENCES users(user_id)
        ON DELETE SET NULL     -- If a user is deleted, set user_id in orders to NULL
);Mogelijk gemaakt door

Veelvoorkomende uitdagingen aanpakken

Naarmate je complexere databases ontwerpt, kom je waarschijnlijk uitdagingen tegen met foreign keys. Hieronder staan de veelvoorkomende problemen die ik met foreign keys heb ervaren en praktische oplossingen voor elk.

• Niet-overeenkomende datatypen: Als je foreign key- en verwijzende primary key-kolommen onverenigbare datatypen hebben, kan de constraint niet worden aangemaakt. Standaardiseer daarom altijd typen over gerelateerde tabellen.

• Circulaire afhankelijkheden: Ontstaan wanneer twee tabellen naar elkaar verwijzen, wat kan leiden tot insert-deadlocks. Herzie in dat geval het schema-ontwerp of maak de tabel eerst zonder foreign keys, laad de basisdata en gebruik daarna ALTER TABLE om de constraints toe te voegen.

• Hangende foreign keys: Dit probleem doet zich voor wanneer de foreign key van een kind verwijst naar een niet-bestaande parenttabel. Los dit op met cascaderende acties om de data te valideren tijdens delete- en updatebewerkingen.

Wijzigingen beheren en constraints droppen

Soms wil je de foreign key-constraints aanpassen bij het herontwerpen van je databaseschema. Hieronder staan manieren om de foreign key te droppen, met kleine syntaxisverschillen tussen de verschillende SQL-dialecten.

In PostgreSQL, SQL Server en Oracle gebruiken we de instructie DROP CONSTRAINT.

-- Drop foreign key
ALTER TABLE orders
DROP CONSTRAINT fk_orders_users;Mogelijk gemaakt door

In MySQL gebruiken we echter de instructie DROP FOREIGN KEY om de foreign key te verwijderen.

-- Drop foreign key
ALTER TABLE orders
DROP FOREIGN KEY fk_orders_users;Mogelijk gemaakt door

Let erop dat het verwijderen van een foreign key-constraint ervoor zorgt dat de database de referentiële integriteit niet meer afdwingt. Na verwijdering kun je data invoegen of updaten die de relatie schendt, wat kan leiden tot data- of inconsistentie.

Prestaties en optimalisatie

Uit de bovenstaande voorbeelden hebben we gezien hoe foreign keys zorgen voor een effectief dataontwerp door dataintegriteit te behouden. Het is echter belangrijk te begrijpen hoe je foreign keys optimaliseert om de prestaties van je databases te verbeteren. Hieronder vind je tips die ik nuttig heb gevonden bij het werken met foreign keys:

Indexering en query-prestaties

Wanneer je een foreign key indexeert, kan de database tijdens insert-, update- of delete-bewerkingen snel wijzigingen valideren door overeenkomende sleutels in de parenttabel te vinden. Evenzo versnellen indexen join-bewerkingen die kind- en parenttabellen koppelen, doordat de database-engine rijen kan matchen op basis van foreign key-waarden.

Als best practice raad ik aan foreign key-kolommen expliciet te indexeren als het DBMS dit niet automatisch doet.

Query-optimalisatietechnieken

Sommige SQL-engines gebruiken het bestaan van foreign keys om join-eliminatie toe te passen, een techniek die onnodige joins identificeert en uit het query-uitvoeringsplan verwijdert. Als een query bijvoorbeeld alleen kolommen uit de kindtabel benadert maar een join op een parenttabel bevat voor filtering die door de foreign key-constraint is gegarandeerd, kan de join worden geoptimaliseerd en weggelaten.

Je kunt altijd de execution plans bekijken om te zien hoe de query-optimizer omgaat met foreign keys en joins. Deze optimalisaties en hun efficiëntie verschillen per SQL-engine, zoals PostgreSQL, MySQL en SQL Server. Inzicht in het gedrag van de optimizer van jouw specifieke database helpt je om performante queries te schrijven die optimaal profiteren van foreign key-constraints.

Je kunt bijvoorbeeld de volgende query gebruiken om het execution plan in PostgreSQL te bekijken:

-- View execution plan
EXPLAIN ANALYZE
SELECT o.order_id, u.email
FROM orders o
JOIN users u ON o.user_id = u.user_id;Mogelijk gemaakt door

Deze tabel vat samen hoe je het execution plan in verschillende databases bekijkt:

Ik aanbeveel onze Joining Data in SQL-cursus om de verschillende soorten joins in SQL te leren en hoe je met verschillende gerelateerde tabellen in de database werkt.

Geavanceerde use-cases en best practices

Naast de basisimplementatie kun je foreign keys gebruiken om een robuuste, schaalbare en onderhoudbare database-architectuur te bouwen. In deze sectie leg ik de gespecialiseerde scenario’s en ontwerpprincipes op hoog niveau uit waar je rekening mee moet houden bij het gebruik van foreign keys.

Relaties over databases of schema’s heen

Hoewel foreign keys zijn ontworpen om integriteit binnen één database of schema af te dwingen, vereisen complexere enterprise-omgevingen vaak relaties over deze grenzen heen. De tabel hieronder vat cross-schema-ondersteuning en cross-database-ondersteuning samen voor verschillende SQL-dialecten.

Onderhoud en auditing

Wanneer je foreign keys opzet, merk je dat ze veranderen zodra je bedrijfslogica verschuift. Overweeg de volgende manieren om de foreign keys te auditen en te onderhouden zodat ze up-to-date blijven:

• Constraints herzien: Audit foreign key-constraints periodiek om te zorgen dat ze in lijn blijven met veranderende bedrijfsregels en schemawijzigingen.
• Regelmatige index-audit: Controleer altijd of er indexen bestaan op alle foreign key-kolommen om optimale join- en validatieprestaties te behouden, vooral na grote data-imports of systeemupgrades.
• Documentatie en visualisatie: Gebruik Entity-Relationship (ER)-diagrammen of schemavisualisatietools om relaties te documenteren. Deze visuele hulpmiddelen zorgen ervoor dat alle developers en analisten de relaties en de integriteitsregels die door je foreign keys worden afgedwongen, begrijpen.

Best practices voor robuust databaseontwerp

Haal het meeste uit hoe foreign keys werken door je aan de volgende richtlijnen te houden:

• Stem je sleutels af op echte bedrijfslogica: Zorg dat primary en foreign keys echte entiteiten en hun onderlinge relaties representeren, in plaats van te vertrouwen op kunstmatige of instabiele identifiers.

• Gebruik duidelijke, consistente namen: Kies beschrijvende constraintnamen, zoals fk_orders_user_id, zodat je schema makkelijker te onderhouden, te debuggen en samen te gebruiken is.

• Balans tussen strengheid en flexibiliteit: Constraints moeten de datakwaliteit beschermen, maar je ontwerp moet nog steeds ruimte laten voor praktische randgevallen of validaties in de applicatielaag.

• Vermijd circulaire relaties: Structureer je schema zo dat foreign key-afhankelijkheden geen lus vormen, want die maken inserts, updates en deletes onnodig complex.

• Optimaliseer prestaties: Indexeer je foreign keys goed en pas cascaderende acties zorgvuldig toe, zodat je integriteit behoudt zonder je systeem te vertragen.

Conclusie

Zoals met elke vaardigheid, leer je foreign keys beheersen door te oefenen. Ik moedig je aan om te experimenteren met schemamodellering, cascaderende acties te testen en te begrijpen hoe constraints zich in praktijksituaties gedragen. Na verloop van tijd leer je hoe je strikte regels in balans brengt met de flexibiliteit die je applicaties nodig hebben, zodat je databases maakt die zowel veerkrachtig als aanpasbaar zijn wanneer de eisen evolueren.

Nu je begrijpt hoe je foreign keys gebruikt in databaseontwerp, raad ik je aan ons carrièrepad Associate Data Engineer in SQL te proberen om de basis van data engineering en datawarehousing te leren. Als je je databasebeheervaardigheden voor big data wilt aanscherpen, raad ik ten slotte onze cursus Introduction to Data Modeling in Snowflake aan om meer te leren over dimensioneel modelleren.