Deze handleiding zal gaan over "Foreign Keys" in MySQL. Allereerst zal ik even in een notendop uitleggen wat Foreign Keys eigenlijk zijn, want misschien is het een totaal nieuw begrip voor jou.
Foreign Keys (oftewel verwijzende sleutels), ook wel afgekort tot FK's, duiden relaties aan tussen twee tabellen in een database. Een waarde in een tabel zal verbonden worden met een andere tabel door twee waardes altijd overeen te laten komen.
Hoe deze relatie precies in elkaar steekt, en wat je er allemaal mee kan, zal uitgebreid aan bod komen. Verder ga ik tijdens deze tutorial er vanuit dat je de basis SQL syntaxen kent, zoals create table, alter table, insert en delete.
Nu we weten waar FK's voor gebruikt worden willen we natuurlijk weten hoe we deze relatie tussen twee tabellen definiëren.
Dit gaat verrassend simpel; ik ga in ons geval een relatie aanbrengen tussen twee tabellen, namelijk: "bedrijven" en "producten".
Echter, in ons geval zal elk bedrijf een assortiment aan producten hebben.
Hieronder staan de twee tabellen ZONDER Foreign Keys, zodat we een beeld krijgen waarmee we gaan werken. Een belangrijke noot is dat ik de database engine InnoDB gebruik, dit aangezien de veel gebruikte (en veelal standaard geselecteerde) database engine MyISAM geen FK's ondersteunt!
CREATE TABLE `bedrijven` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bedrijfsnaam` varchar(75) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB ;
CREATE TABLE `producten` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bedrijf_id` int(11) NOT NULL,
`productnaam` varchar(75) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB ;
De oplettende lezer zal het veld "bedrijf_id" in de tabel producten al wel opgevallen zijn. Hiermee gaan we een product aan een bedrijf verbinden, de relatie tussen producten en bedrijven.
We kunnen nu leuk de tabel bedrijven en producten gaan vullen, maar een aantal dingen kunnen misgaan. Bijvoorbeeld: een product met een bedrijf_id die verwijst naar een bedrijf dat niet (meer) bestaat.
In een goed databasemodel hoort dit natuurlijk niet thuis, met een FK zou dit niet mogelijk zijn, wat we het liefst willen bereiken.
We gaan producten.bedrijf_id in relatie brengen met bedrijven.id, dus dienen we een FK aan producten.bedrijf_id toe te voegen die dit beschrijft. Om dit te kunnen dient bedrijf_id een INDEX te hebben, dit gaan we als eerst doen.
ALTER TABLE `producten` ADD INDEX ( `bedrijf_id` )
Nu de index aanwezig is kunnen we de Foreign Key gaan definiëren.
De query spreekt al redelijk voor zich, maar ik zal even uitleggen wat er gebeurt.
ALTER TABLE `producten` => We geven aan dat we de tabel producten gaan aanpassen.
ADD FOREIGN KEY ( `bedrijf_id` ) => Hiermee wordt bepaald aan welk veld binnen producten de FK toegevoegd wordt.
REFERENCES `bedrijven` => De tabel waarmee de tabel producten een relatie aangaat.
( `id` ) => Het veld binnen bedrijven waarmee bedrijf_id overeen dient te komen.
De database zal automatisch een naam toevoegen aan de FK, echter het is aan te raden zelf een naam op te geven zodat je errors kan analyseren (ga naar: Constraint fail herkennen en afhandelen).
De syntax hiervoor is: "ADD [CONSTRAINT [symbol]] FOREIGN KEY", in ons geval zal dit worden:
Noot: Je kunt een bestaande FK niet overschrijven, je dient deze eerst te verwijderen alvorens een nieuwe te definiëren (ALTER TABLE producten DROP FOREIGN KEY fk_bedrijf_id;).
Tot zover het het definiëren van een Foreign Key, hopelijk is het al duidelijk hoe dit werkt.
Als je meer wilt weten over de werking en/of mogelijkheden van een FK, lees dan verder in het volgende gedeelte: "De FK in werking".
In dit gedeelte ga ik wat verder in over de werking van een Foreign Key, hoe reageren de tabellen op het toevoegen van data, en wat is nu wel en niet mogelijk.
Nu de FK aanwezig is zal je gerust bedrijven kunnen toevoegen, maar zodra je een product wil toevoegen zal dat alleen werken zolang bedrijf_id een waarde heeft die overeenkomt met het id van een bedrijf.
Als dit laatste niet het geval is krijgen we een MySQL error terug; deze ziet er als volgt uit:
#1452 - Cannot add or update a child row: a foreign key constraint fails
Echter, zodra wij nu een bedrijf willen verwijderen, en dit bedrijf heeft nog producten, zal dit ook een error opleveren:
#1451 - Cannot delete or update a parent row: a foreign key constraint fails
Dit is ook vrij logisch, want dan klopt het databasemodel niet meer, er mogen NOOIT producten los rondzwerven zonder een toegewezen bedrijf.
Dit is op te lossen door eerst alle producten van een dergelijk bedrijf te verwijderen en vervolgens het bedrijf zelf pas.
In de praktijk is dit niet zo heel efficiënt en omslachtig, want FK's hebben hier zelf een simpele oplossing voor, namelijk reference options.
Hierover lees je meer in het volgende gedeelte: "Een FK uitbreiden met opties".
In de vorige paragraaf hebben we gelezen dat een FK erg handig kan zijn in een goed databasemodel, maar dat een "standaard" Foreign Key nog wel eens problemen kan opleveren zodra het om verwijderen van (in ons geval) bedrijven gaat.
Hiervoor hebben Foreign Keys reuze handige opties, het gaat om een vijftal opties die op twee manieren aangeroepen kunnen worden, namelijk ON DELETE en ON UPDATE.
MySQL staat niet toe om een waarde aan te passen waarvan de foreign key constraint faalt. We kennen de volgende opties:
RESTRICT
CASCADE
SET NULL
NO ACTION
SET DEFAULT
RESTRICT zou je bijvoorbeeld kunnen instellen in een database die met de tabellen producten en bestellingen werkt. Zolang een product nog aanwezig is in een bestelling kan je het product niet updaten.
CASCADE is wat wij gaan gebruiken, als een cascade is ingesteld zal er een domino-effect optreden. Zodra wij een bedrijf verwijderen gaan alle producten die bij dit bedrijf horen automatisch ook weg.
SET NULL zal, zoals de optie zelf al aangeeft, de velden NULL maken. Let hierbij op dat de velden dit wel toe moeten laten (nullable)! Als een datatype dit niet toelaat zal dit niet niet werken.
NO ACTION is wat een FK standaard doet als er geen andere optie gekozen is.
SET DEFAULT zal de waardes vervangen/terugzetten naar de standaardwaardes.
In ons geval gaan we een CASCADE instellen, zodat je veilig een bedrijf kan verwijderen zonder dat er producten blijven rondzwerven.
Hiervoor hoeven we alleen een ON DELETE CASCADE optie toe te voegen aan onze FK:
ALTER TABLE `producten` ADD FOREIGN KEY ( `bedrijf_id` ) REFERENCES `bedrijven` (`id`) ON DELETE CASCADE ;
Het grootste gedeelte van deze query is in een eerder gedeelte van deze tutorial beschreven (ga naar: Een FK definiëren). Verder is alleen de ON DELETE CASACDE optie toegevoegd.
Als we nu een onze tabellen voorstellen met de volgende data:
Tabel bedrijven
id
bedrijfsnaam
1
Logitech
2
Samsung
Tabel producten
id
bedrijf_id
productnaam
1
2
SyncMaster 226BW
2
2
Samsung Syncmaster P2350
3
1
Logitech MX518
Nu voeren wij enkel de volgende query uit:
DELETE FROM `bedrijven` WHERE `id` = 2
En het resultaat:
Tabel bedrijven
id
bedrijfsnaam
1
Logitech
Tabel producten
id
bedrijf_id
productnaam
3
1
Logitech MX518
Zoals je ziet is het bedrijf Samsung uit de tabel bedrijven verwijderd en zijn alle bijbehorende producten automatisch meegegaan. Erg handig, en dit is slechts een simpel voorbeeld.
In de volgende paragraaf ga ik iets meer in op wat PHP voorbeelden van het hele verhaal, hier laat ik zien hoe je een constraint fail gebruiksvriendelijk afhandelt.
Ga naar: Constraint fail herkennen en afhandelentop2.4 Constraint fail herkennen en afhandelen
Eerder lazen we al dat zodra een relatie botst - deze een "constraint fail" SQL error teruggeeft (ga naar: De FK in werking).
Als gebruikers zelf producten kunnen toevoegen aan een gekozen bedrijf en er op één of andere duistere wijze een verkeerde bedrijf_id meegegeven wordt, kan dit de gebruiker erg afschrikken.
Daarom kan je, net als elke andere SQL error, de fout gaan analyseren. Ik heb hieronder een voorbeeld opgezet, ervan uitgaande dat je enige PHP kennis bezit.
Belangrijk is dat je de constraint namen hebt meegegeven, zodat je kan nagaan welke constraint afgaat.
<?php
define('Q_FOREIGN_KEY', 1452); // deze errno staat vast voor een add/update constraint fail
function is_constraint($errno, $errkey) {
/* Dit is de functie die de error analyseert
Er wordt gekeken of de errorcode (ernno) overeenkomt met die een add/update constraint fail
Vervolgens kijkt hij of het om de opgegeven constraint gaat en retourneert */
$_bedrijfid = 9; // vb.
$_productnaam = 'Corsair 750W';
$result = mysql_query("INSERT INTO producten (bedrijf_id, productnaam) VALUES (".$_bedrijfid.", '".$_productnaam."')");
if(!$result) {
if(is_constraint(Q_FOREIGN_KEY, 'fk_bedrijf_id')) { // gaat hier om fk_bedrijf_id?
print 'Kan geen producten toevoegen aan een ongeldig bedrijf.';
}
}
?>
Deze code is redelijk basis en kan je helemaal naar je eigen wensen aanpassen, maar ik denk dat het een makkelijk opzetje is.
Tot zover mijn handleiding over Foreign Keys, hopelijk is alles wat duidelijker geworden en begrijp je waar het allemaal handig voor is!
top3.1 Nawoord
Hopelijk heb je veel geleerd van mijn tutorial. Voor mij was het gebruik van Foreign Keys tot enkele maanden geleden nog totaal onbekend.
Ik ben van mening dat goede tutorials over dit onderwerp redelijk schaars zijn, daarom is deze tutorial erg welkom!
Als er toch vragen op zijn komen borrelen, kun je die altijd hier op het forum stellen.
Wat is jouw favoriete javascript framework? (S: 18, R: 2)
(4.5) 
