login  Naam:   Wachtwoord: 
Registreer je!
 Tutorials

Tutorials > MySQL


Gegevens:
Geschreven door:
ikki007
Moeilijkheidsgraad:
Normaal
Hits:
43377
Punten:
Aantal punten:
 (4.5)
Aantal stemmen:
6
Stem:
Niet ingelogd
Nota's:
 Post een nota
 Lees de nota's (5)
 


Tutorial:

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

Foreign Keys: Wat zijn het en hoe gebruik ik ze?
1.1 Inhoudsopgave

1.1 Inhoudsopgave
1.2 Inleiding

2.1 Een FK definiŽren
2.2 De FK in werking
2.3 Een FK uitbreiden met opties
2.4 Constraint fail herkennen en afhandelen

3.1 Nawoord
3.2 Bronnen

pijl top
1.2 Inleiding

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!

pijl top
2.1 Een FK definiŽren

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.

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

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

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".


Grafische weergave van de relatie

pijl top
2.2 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".


"a foreign key constraint fails"

pijl top
2.3 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 afhandelen

pijl top
2.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 */
    
    
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!

pijl top
3.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.

- ikki007
www.jarnovanleeuwen.nl
www.cmsshow.info

pijl top
3.2 Bronnen

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html
http://en.wikipedia.org/wiki/Foreign_key

pijl top

« Vorige tutorial : AND en OR Volgende tutorial : Een start maken met SQL »

© 2002-2019 Sitemasters.be - Regels - Laadtijd: 0.02s