• De afgelopen dagen zijn er meerdere fora waarop bestaande accounts worden overgenomen door spammers. De gebruikersnamen en wachtwoorden zijn via een hack of een lek via andere sites buitgemaakt. Via have i been pwned? kan je controleren of jouw gegeven ook zijn buitgemaakt. Wijzig bij twijfel jouw wachtwoord of schakel de twee-staps-verificatie in.

[SQL] Foreign Keys: Wat zijn het en hoe gebruik ik ze?

Ben je positief over deze handleiding?

  • Nee

    Stemmen: 0 0,0%
  • Dit is niet mijn gebied

    Stemmen: 0 0,0%

  • Totaal aantal stemmers
    4
Status
Niet open voor verdere reacties.

ikki007

NCF Goeroe
Lid geworden
28 sep 2004
Berichten
21.723
Waarderingsscore
401
<a name="top"></a>
<u>Foreign Keys: Wat zijn het en hoe gebruik ik ze?</u>

<hr noshade="noshade" />
<a name="1.1_inhoudsopgave"></a><strong>1.1 Inhoudsopgave</strong>
<hr noshade="noshade" /><p>
<a href="#1.1_inhoudsopgave">1.1 Inhoudsopgave</a>
<a href="#1.2_inleiding">1.2 Inleiding</a>

<a href="#2.1_een-fk-definieren">2.1 Een <span class="help" title="Foreign Key">FK</span> definiren</a>
<a href="#2.2_de-fk-in-werking">2.2 De <span class="help" title="Foreign Key">FK</span> in werking</a>
<a href="#2.3_een-fk-uitbreiden-met-opties">2.3 Een <span class="help" title="Foreign Key">FK</span> uitbreiden met opties</a>
<a href="#2.4_constraint-fail-herkennen-en-afhandelen">2.4 Constraint fail herkennen en afhandelen</a>

<a href="#3.1_nawoord">3.1 Nawoord</a>
<a href="#3.2_bronnen">3.2 Bronnen</a>
</p>
<a href="#top">^ top</a>
<hr noshade="noshade" />
<a name="1.2_inleiding"></a><strong>1.2 Inleiding</strong>
<hr noshade="noshade" /><p>
Ik heb deze handleiding zo'n twee jaar geleden geschreven, maar het leek mij ook een goede aanwinst voor het NCF :biggrin:

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.

Veel lees- en leerplezier!
</p>
<a href="#top">^ top</a>
<hr noshade="noshade" />
<a name="2.1_een-fk-definieren"></a><strong>2.1 Een FK definiren</strong>
<hr noshade="noshade" /><p>
Nu we weten waar FK's voor gebruikt worden willen we natuurlijk weten hoe we deze relatie tussen twee tabellen definiren.
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.

<strong>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!</strong>

Code:
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.

Code:
ALTER TABLE `producten` ADD INDEX ( `bedrijf_id` )

Nu de index aanwezig is kunnen we de Foreign Key gaan definiren.

Code:
ALTER TABLE `producten` ADD FOREIGN KEY ( `bedrijf_id` ) REFERENCES `bedrijven` ( `id` );

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: <a href="#2.4_constraint-fail-herkennen-en-afhandelen">Constraint fail herkennen en afhandelen</a>).
De syntax hiervoor is: "<i>ADD [CONSTRAINT [symbol]] FOREIGN KEY</i>", in ons geval zal dit worden:

Code:
ALTER TABLE `producten` ADD CONSTRAINT fk_bedrijf_id FOREIGN KEY ( `bedrijf_id` ) REFERENCES `bedrijven` ( `id` );

<strong>Noot: Je kunt een bestaande FK niet overschrijven, je dient deze eerst te verwijderen alvorens een nieuwe te definiren (<i>ALTER TABLE producten DROP FOREIGN KEY fk_bedrijf_id;</i>).</strong>

Tot zover het het definiren 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: "<a href="#2.2_de-fk-in-werking">De FK in werking</a>".

<div align="center"><img src="http://i48.tinypic.com/kq98j.png" title="Grafische weergave van de relatie" />
<em style="color: grey;">Grafische weergave van de relatie</em></div>
</p>
<a href="#top">^ top</a>
<hr noshade="noshade" />
<a name="2.2_de-fk-in-werking"></a><strong>2.2 De FK in werking</strong>
<hr noshade="noshade" /><p>
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:

<i>#1452 - Cannot add or update a child row: a foreign key constraint fails</i>

Echter, zodra wij nu een bedrijf willen verwijderen, en dit bedrijf heeft nog producten, zal dit ook een error opleveren:

<i>#1451 - Cannot delete or update a parent row: a foreign key constraint fails</i>

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 efficint en omslachtig, want FK's hebben hier zelf een simpele oplossing voor, namelijk reference options.
Hierover lees je meer in het volgende gedeelte: "<a href="#2.3_een-fk-uitbreiden-met-opties">Een FK uitbreiden met opties</a>".

<div align="center"><img src="http://i50.tinypic.com/osycme.gif" height="273" width="750" title="a foreign key constraint fails" />
<em style="color: grey;">"a foreign key constraint fails"</em></div>
</p>
<a href="#top">^ top</a>
<hr noshade="noshade" />
<a name="2.3_een-fk-uitbreiden-met-opties"></a><strong>2.3 Een FK uitbreiden met opties</strong>
<hr noshade="noshade" /><p>
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:

<ul><li>RESTRICT</li><li>CASCADE</li><li>SET NULL</li><li>NO ACTION</li><li>SET DEFAULT</li></ul>

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:

Code:
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: <a href="#2.1_een-fk-definieren">Een FK definiren</a>). Verder is alleen de ON DELETE CASCADE optie toegevoegd.
Als we nu een onze tabellen voorstellen met de volgende data:

<i>Tabel bedrijven</i>
<strong>id</strong>|<strong>bedrijfsnaam</strong>
1|Logitech
2|Samsung


<i>Tabel producten</i>
<strong>id</strong>|<strong>bedrijf_id</strong>|<strong>productnaam</strong>
1|2|SyncMaster 226BW
2|2|Samsung Syncmaster P2350
3|1|Logitech MX518


Nu voeren wij <i>enkel</i> de volgende query uit:

Code:
DELETE FROM `bedrijven` WHERE `id` = '2'

En het resultaat:

<i>Tabel bedrijven</i>
<strong>id</strong>|<strong>bedrijfsnaam</strong>
1|Logitech


<i>Tabel producten</i>
<strong>id</strong>|<strong>bedrijf_id</strong>|<strong>productnaam</strong>
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: <a href="#2.4_constraint-fail-herkennen-en-afhandelen">Constraint fail herkennen en afhandelen</a>
</p>
<a href="#top">^ top</a>
<hr noshade="noshade" />
<a name="2.4_constraint-fail-herkennen-en-afhandelen"></a><strong>2.4 Constraint fail herkennen en afhandelen</strong>
<hr noshade="noshade" /><p>
Eerder lazen we al dat zodra een relatie botst - deze een "constraint fail" SQL error teruggeeft (ga naar: <a href="#2.2_de-fk-in-werking">De FK in werking</a>).
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:
<?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 */
    
    if(mysql_errno() == Q_FOREIGN_KEY AND strpos(mysql_error(), 'CONSTRAINT `'.$errkey.'` FOREIGN') !== false) {
        return true;
    }
    return false;
}

$_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!
</p>
<a href="#top">^ top</a>
<hr noshade="noshade" />
<a name="3.1_nawoord"></a><strong>3.1 Nawoord</strong>
<hr noshade="noshade" /><p>
Hopelijk heb je veel geleerd van mijn tutorial. 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.

- ikki007
</p>
<a href="#top">^ top</a>
<hr noshade="noshade" />
<a name="3.2_bronnen"></a><strong>3.2 Bronnen</strong>
<hr noshade="noshade" /><p>
<a href="http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html">http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html</a>
<a href="http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html">http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html</a>
<a href="http://en.wikipedia.org/wiki/Foreign_key">http://en.wikipedia.org/wiki/Foreign_key</a>
</p>
<a href="#top">^ top</a>
 
Laatst bewerkt door een moderator:
Goed gedaan Ikki :)
 
Deze komt zeker op tijd, had net zoiets nodig!

Ik was hiervan niet op de hoogte en zocht er al een tijdje naar, wel interessante en makkelijk te volgen tut!

+1 ! :)

thx
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan