relationele databases
9.1
hoofdstuk 9 referentiële integriteit waarborgen overige constraints
9.1 Referentiële integriteit
Als voorbeeld nemen we een eenvoudige database, bestaande uit twee tabellen. De ene tabel PERS bevat de gegevens van ziekenhuispersoneel waarbij we, om de zaak te beperken, alleen de naam en de afdeling waar het personeelslid werkzaam is hebben weergegeven. De tweede tabel heet AFD en bevat een aantal gegevens over de afdelingen waarover het ziekenhuis beschikt. Laten we om wille van de eenvoud aannemen dat de tabellen compleet zijn. PERS medewerker
afdeling
Dam G. van Buitenen J. van Wit O. de Karels P. Dijk A. van Donkers L. Drost V. Hylkema Y.
Ra De On Ra Ra De Ra Td
AFD afdeling De On Ra
naam Dermatologie Oncologie Radiologie
etage 4 7 2
Als we van een willekeurig persoon uit de tabel PERS iets over de etage van het ziekenhuis willen weten waar hij of zij werkt komen we bij de laatste persoon uit de tabel bedrogen uit. De afdeling waar deze persoon werkt bestaat blijkbaar niet. Er is dan ook iets grondig mis met de tabellen van deze simpele database. Blijkbaar is het mogelijk om over personeel te beschikken terwijl we niet weten waar dit personeel werkt. Geheel analoog zijn tabellen met tentamenresultaten van niet bestaande studenten of voor niet bestaande modulen, tabellen van voetballers die uitkomen voor niet bestaande clubs of tabellen met klanten die niet bestaande artikelen geleverd zijn. En zo zijn nog talloze voorbeelden te verzinnen. Nou kun je natuurlijk afspreken dat een personeelslid slechts een bestaande afdeling toebedeeld mag krijgen. In de praktijk gebeurt dit ook wel eens maar als er even een
relationele databases
9.2
slordigheidje bij het typwerk geleverd wordt is de database wellicht waardeloos geworden. En dan bestaat nog altijd de mogelijkheid om bijvoorbeeld de eerste rij van AFD te wissen wat voor de artsen van Buitenen en Donkers betekent dat zij ten onrechte op de personeelslijst staan.
Ook bij de database van de boekenclub is sprake van referentiële sleutels. U zou er toch raar van moeten opkijken als u in de tabel ORDERS lidnummers tegenkwam die niet LEDEN staan. Evenzo zou het voorkomen van niet bestaande boeknummers in de tabel ORDERREGELS uw wenkbrauwen moeten doen fronsen. En al even vreemd zou het zijn als orderregels betrekking hebben op niet bestaande orders. Gelukkig maar dat in de tabellen niets van dit alles het geval is. Een database die niet behept is met de hierboven geschetste problematiek heet referentieel integer. We zeggen ook wel dat hij voldoet aan de eisen van referentiële integriteit. En die integriteit kunnen we eenvoudig afdwingen door bij het maken van de database vooraf met deze valkuil rekening te houden. De kolom afdeling in de tabel PERS mag in een gezonde situatie alleen waarden bevatten die in de kolom afdeling van de tabel AFD voorkomen. Anders zullen we zo ongeveer bij elke query die zich over beide tabellen uitstrekt problemen kunnen verwachten. De kolom PERS.afdeling wordt wel aangeduid met de term verwijzende sleutel (foreign key of referential key). Hoe we zo'n verwijzing realiseren is onderwerp van de volgende paragraaf. Denk er wel om dat in dit voorbeeld de referentële integriteit slechts verondersteld dat alle waarden die in PERS.afdeling gebruikt worden ook in AFD.afdeling voorkomen. Niet omgekeerd!
9.2 Foreign keys
Om te voorkomen dat problemen van bovengeschetste aard ontstaan definiëren we eerst de tabel AFD. Het volgende script definieert de tabel AFD.
CREATE TABLE afd (afdeling TEXT(2) PRIMARY KEY, naam TEXT(20), etage INTEGER);
Het script voor de personeelstabel komt pas daarna aan de beurt.
CREATE TABLE pers (naam TEXT(25) PRIMARY KEY, afdeling TEXT(2) REFERENCES afd(afdeling));
De constraint REFERENCES afd(afdeling) is ervoor verantwoordelijk dat we onmogelijk in staat zijn codes te gebruiken die niet voorkomen in de kolom afdeling van de AFDtabel. Merk op dat de verwijzing hier als kolomconstraint is opgegeven. In veel gevallen kunt u volstaan met zo'n kolomconstraint. Soms echter strekt de referentiële integriteit zich over
relationele databases
9.3
meerdere kolommen uit. Dan moeten we een tabelconstraint gebruiken. Vandaar dat we hier ook die oplossing aanbieden.
CREATE TABLE pers (naam TEXT(25) PRIMARY KEY, afdeling TEXT(2), FOREIGN KEY (afdeling) REFERENCES afd(afdeling));
Beide definities van de tabel PERS levert hetzelfde resultaat en het is om het even welk van de twee u gebruikt. Een aantal zaken is belangrijk: - de tabel AFD moet al bestaan vóórdat u er in de tabel PERS naar kunt verwijzen; - de kolom (of combinatie van kolommen) waarnaar u verwijst moet een uniek karakter dragen (bijvoorbeeld de primary key van AFD maar er zijn meer mogelijkheden een kolom te dwingen unieke waarden te bevatten); - wissen van een rij in de tabel waarnaar verwezen wordt (AFD in ons voorbeeld) kan alleen als er geen enkele rij in de verwijzende tabel (hier PERS) daadwerkelijk naar verwijst; - verwijderen van de complete tabel waarnaar verwezen wordt is pas mogelijk nadat eerst de verwijzende tabel verwijderd is (dus DROP TABLE afd; kan pas nadat DROP TABLE pers; is uitgevoerd). Keren we nu terug naar het script van de boekenclubtabellen. Hieronder is een verbeterde versie afgebeeld van het gedeelte met de tabeldefinities waarin rekening gehouden is met de referentiële integriteit.
DROP DROP DROP DROP
TABLE TABLE TABLE TABLE
orderregels; orders; boeken; leden;
CREATE TABLE boeken ( boeknr titel uitgever prijs
TEXT(8) PRIMARY KEY, TEXT(40), TEXT(3), CURRENCY);
CREATE TABLE leden( lidnr naam adres postcode woonplaats inschdat
TEXT(8) PRIMARY KEY, TEXT(20), TEXT(20), TEXT(7), TEXT(20), DATE);
relationele databases
9.4
CREATE TABLE orders ( ordernr TEXT(8) PRIMARY KEY, datum DATE, lidnr TEXT(8) REFERENCES leden(lidnr)); CREATE TABLE orderregels( ordernr TEXT(8) REFERENCES orders(ordernr), boeknr TEXT(8) REFERENCES boeken(boeknr), aantal INTEGER, PRIMARY KEY (ordernr, boeknr));
Merk op dat de tabel ORDERREGELS twee verwijzende sleutels bevat. De volgorde bij het definiëren van de tabellen is – zo zagen we – belangrijk. Voor de tabellen LEDEN en BOEKEN maakt de onderlinge volgorde niet uit maar beiden moeten gedefinieerd zijn voordat we ORDERS kunnen aanmaken. En ORDERS moet op zijn beurt weer bestaan voordat ORDERREGELS in het spel betrokken worden.
9.3 Geen sleutel maar toch uniek
Een enkele keer komt het voor dat in een tabel meerdere kolommen of combinaties van kolommen voor de rol van 'sleutel' in aanmerking komen. Elke tabel kan echter maar één sleutel hebben. Een bekend voorbeeld is de tabel waarin we de landen en hoofdsteden van Europa opsommen. land
hoofdstad
Nederland Denemarken Noorwegen Frankrijk België
Amsterdam Kopenhagen Oslo Parijs Brussel
In deze tabel kan zowel de kolom land als hoofdstad als sleutel dienen. In een dergelijk geval moeten we een keuze maken. Stel dat we de tabel definiëren als CREATE TABLE europa (land TEXT(20) PRIMARY KEY, hoofdstad TEXT(20)); Dan is het nog steeds mogelijk dat we de tabel onderstaande - onzinnige - inhoud geven.
land
hoofdstad
Denemarken Spanje
Kopenhagen Kopenhagen
Nu zal waarschijnlijk niemand op het idee komen om de Spaanse hoofstad te verwarren met die van Denemarken. Maar de database protesteert niet als het toch gebeurt en doet geen recht aan het feit dat de namen van hoofdsteden uniek zijn. Om de hierboven geschetste situatie te voorkomen voorzien we de tabel van een extra constraint.
relationele databases
9.5
CREATE TABLE europa (land TEXT(20) PRIMARY KEY, hoofdstad TEXT(20) UNIQUE);
De kolom hoofdstad gedraagt zich min of meer als ware het de sleutel. Elke hoofdstad moet uniek zijn, net als het land. Er is een klein verschil met een echte sleutel: de kolom hoofdstad mag lege waarden bevatten. We weten uit het vorige hoofdstuk hoe we lege waarden kunnen voorkomen. Misschien dat
CREATE TABLE europa (land TEXT(20) PRIMARY KEY, hoofdstad TEXT(20) UNIQUE NOT NULL); nog fraaier is. De tweede kolom gedraagt zich nu als een volwaardige (kandidaat-)sleutel.
9.4 CHECK-constraint
De laatste constraint die behandeld wordt is de CHECK-constraint. In zijn eenvoudigste vorm biedt die de mogelijkheid om de inhoud van een kolom een beperking op te leggen. Maar let op: in JetSQL kan een CHECK-constraint uitsluitend als tabelbeperking worden opgegeven. Na elke mutatie wordt de tabel gecontroleerd op consistentie; anders wordt de mutatie alsnog geweigerd. Dat kan - vooral bij grote tabellen - ten koste gaan van de snelheid (performance). Als voorbeeld nemen we de tabel PERSOON waarbij we eisen dat het geslacht slechts de waarden m of v aan mag nemen.
CREATE TABLE (persnr naam geslacht CHECK
persnr
naam
geslacht
00001 00002 00003 00005 00007
Henk Ahmed Cora Anita Wim
m m v v m
persoon TEXT(5) PRIMARY KEY, TEXT(20) NOT NULL, CHAR(1), (geslacht IN ('m', 'v')));
Merk op dat de formulering na CHECK tussen haakjes staat; tussen deze haakjes staat een voorwaarde. In dit voorbeeld hadden we net zo goed (of beter) gebruik kunnen maken van referentiële integriteit door eerst een aparte tabel SEXE te creëren. geslacht m v
relationele databases
9.6
De tabel persoon ontstaat dan met de formulering CREATE TABLE (persnr naam geslacht
persoon TEXT(5) PRIMARY KEY, TEXT(20) NOT NULL, CHAR(1) REFERENCES sexe(geslacht));
Maar soms kunnen we ons niet redden met een foreign key. Bijvoorbeeld in een tabel waarin we de besteldatum en leverdatum van een order opnemen en waarin we willen dat de leverdatum altijd op dezelfde of ná de besteldatum plaatsvindt.
CREATE TABLE bestelling (bestelnr TEXT(10) PRIMARY KEY, besteldatum DATE, leverdatum DATE, CHECK (leverdatum >= besteldatum));
Er zijn ook ingewikkeler toepassingen van de CHECK-constraint te bedenken. Zelfs referentiële integriteit is er mee na te bootsen. Het voorbeeld laat zien hoe het kan maar in de praktijk doet u dat vanwege de performance natuurlijk niet. (MySQL heeft zich in oudere versies van deze manier bediend. Foreign keys bestaan pas in later versies.)
CREATE TABLE (persnr naam geslacht CHECK
persoon TEXT(5) PRIMARY KEY, TEXT(20) NOT NULL, CHAR(1), (geslacht IN (SELECT geslacht FROM sexe)));
Een ander ingewikkeld voorbeeld. Stelt u zich voor dat een tabel hooguit uit 5 rijen mag bestaan. Als dit maximum bereikt is zult u eerst een rij moeten verwijderen voordat u er een toe kunt voegen. Laat u niet van de wijs brengen door de haakjes!
CREATE TABLE max5rij (kolom1 TEXT(20), kolom2 TEXT(20), kolom3 INTEGER, CHECK ((SELECT COUNT(*) FROM max5rij)< 6));
Er is met de tabel MAX5RIJ nog iets merkwaardigs aan de hand. Als u deze probeert te verwijderen uit uw database (DROP TABLE max5rij) weigert het systeem dit. Er wordt immers ergens naar de tabel verwezen (in de CHECK-constraint). Die constraint zit op dat moment danig in de weg. Hoe we dit oplossen valt buiten het bestek van deze cursus. Er zit dan ook niets anders op dan het hele mdb-bestand te wissen of uw kennis over constraints en tabellen verder uit te breiden. Voor dat laatste geldt dat het statement ALTER TABLE de oplossing biedt. Dan hebt u een aanknopingspunt voor verdere studie in SQL.
