Informatief vraagje over referentiële integriteit bij relaties

[HeuvelP]

Vroeg ik me zomaar af:

Als je (via de grafische interface) een relatie aanmaakt tussen twee tabellen in Access, toont Access een lijn tussen die twee tabellen.
In het window Relaties bewerken kun je dan aanvinken Referentiële integriteit afdwingen. Als (en pas als) je dat doet, toont Access (bij een één op veel) de 1 en het oneindigteken.
Je kunt dan ook de twee vervolgregels aanklikken (bij bijwerken en bij verwijderen). Dat hoeft echter niet.

Wat doet de referentiële integriteit eigenlijk als je niet één van de twee vervolgregels aanvinkt?
En waarom toont het zonder referentiële integriteit niet het 1 en oneindigteken?

Misschien mis ik nog iets in het begrijpen van de referentiële integriteit?
Wie weet hoe dat zit?

Bedankt!

Philip

 

[bron]

F1 .

Als u referentiële integriteit voor een relatie wilt afdwingen, schakelt u het selectievakje Referentiële integriteit afdwingen in.

Als u een primaire sleutel bijwerkt terwijl referentiële integriteit wordt afgedwongen en u de optie Gerelateerde velden trapsgewijs bijwerken hebt ingeschakeld, worden in Access automatisch alle velden bijgewerkt die naar de primaire sleutel verwijzen.

Als u een record aan de kant van de primaire sleutel van de relatie verwijdert terwijl referentiële integriteit wordt afgedwongen en u de optie Gerelateerde records trapsgewijs verwijderen hebt ingeschakeld, worden in Access automatisch alle records verwijderd die naar de primaire sleutel verwijzen.

 

[HeuvelP]

Bedankt voor je reactie, bron.
Eigenlijk is mijn vraag echter nog wat anders, want inderdaad ik begrijp wat er gebeurt als ik de twee vinkjes bij bijwerken zet.
Maar die twee vinkjes zijn niet verplicht. Je kunt dus referentiële integriteit aanvinken, zonder de twee bijwerkopties. Mijn vraag is dan, wat houdt in zo'n situatie die referentiële integriteit nog in. Wat blijft er nog over, als je die twee vinkjes niet aanzet.
Groet,
Philip

 

[OctaFish]

Wat blijft er nog over, als je die twee vinkjes niet aanzet.

Alles, want eigenlijk is dat eerste vinkje het belangrijkste. De overige twee zijn 'nice-to-haves'. De laatste optie (Verwijderen) is zelfs bloedlink, want kan je database behoorlijk vergallen als je die te pas en te onpas uitzet. Denk bijvoorbeeld aan het verwijderen van een klant uit je klantentabel. Met de laatste optie aangevinkt, worden óók alle bestellingen en bestelregels, facturen etc. van die klant verwijderd. En daarmee is in één klap je hele boekhouding naar gort, want dan klopt er niks meer van je cijfers. Die derde optie is dus er tricky en moet met (zeer veel) beleid en overleg worden toegepast.
De tweede is een stuk handiger, want dat scheelt je een hoop werk als je een sleutel verandert. Maar die optie heeft geen enkele zin als je sleutel een Autonummer is, want dat kan je niet aanpassen. En dan komt de situatie dus nooit voor.

Waar je vraag dus over gaat, is eigenlijk waarom je RI überhaupt zou gebruiken. Nou, in beginsel is het niet eens nodig, als je alle checks op je formulier uitvoert. Maar dat is dus niet handig. RI stel je in op je tabellen om te voorkomen dat je records invoegt in (bijvoorbeeld) een besteltabel zonder dat er bijbehorende klanten bestaan. Het is niet handig om een bestelling te maken voor een klant die je niet hebt, want dan kun je daar nooit een factuur heen sturen. Overigens is daar wel een uitzondering op te bedenken, maar daar heeft Access weer een ander menuutje voor.
Kortom: relaties tussen tabellen zijn afhankelijk van bestaande records in beide tabellen: alleen bestellingen toevoegen als er een bestaande klant is, alleen bestelregels toevoegen als er een bestelling is etc.

Laat je de RI optie weg, en maak je de relatie toch, dan zie je géén één-op-veel symbool bij de verbindingslijn. De relatie is dan volslagen nutteloos. Sterker: de lijn die je tussen KlantID in Klanten en KlantID in Bestellingen hebt gelegd, kun je dan net zo goed tussen Besteldatum en Artikelnummer leggen; twee gegevens die totaal niets met elkaar te maken hebben. En toch mag je die relatie rustig leggen. Zet je op die combinatie alsnog het RI vinkje aan, dan heb je dus gelijk een probleem, want die relatie kan je echt niet maken met RI.

RI is dus afhankelijk van twee eisen:
1. in de Brontabel heb je een sleutelveld dat je wilt koppelen
2. in de Doeltabel heb je een veld waarin je het overeenkomende sleutelveld uit de bron opslaat, en dat veld bevat geen waarden die niet bestaan in de brontabel.

En als dat voor elkaar is, heb je een correcte RI afgedwongen.

 

[HeuvelP]

Hallo OctaFish, bedankt voor je, als altijd doorwrochte en praktische antwoord!

Dus dat betekent eigenlijk het volgende (even wat geëxperimenteerd):
1. Als ik alleen de "lijn" trek in het "relatie-maakvenster", dan werkt dat ook als ik eigenlijk geen echte "Relationship" in de zin van SQL heb (ik bedoel tenminste mbv een candidate key). Je kunt dan de record in het ene bestand altijd verwijderen, zonder dat het gerelateerde bestand dat merkt. Ik zie dat Access dan de oorspronkelijke waarde laat staan.
2. Als ik alléén de RI aanvink, dan krijg je een foutmelding als je het record uit het primaire bestand probeert te verwijderen, "omdat er gerelateerde velden zijn in de tabel ...". Als je dit vinkje probeert aan te zetten wanneer dat al de uitgangssituatie is, dan kun je zelfs de RI niet gebruiken.
3. Als je de RI gebruikt, incl. het trapsgewijs bewerken bij verwijderen, dan kan je het record in de primaire tabel weer wel verwijderen, maar dan verwijdert Access ook alle records in de secundaire tabel + zo verder naar beneden als die secundaire tabel zelf weer een primaire tabel is van iets anders. Met een waarschuwing, trouwens.

Stel nu dat ik een relatie tussen primaire tabel A en secundaire tabel B wil, zodanig dat ik bij het verwijderen van een record uit A niet de records uit B verwijder, maar de betreffende FK in tabel B op NULL wordt gezet. Dan spreek je, dacht ik, nog wel van RI, maar kan dat ook in Access?

Ik probeer eigenlijk precies te begrijpen hoe relaties (en joins) onder water in Access werken, want ik merk dat de schermpjes zelf op mij steeds weer verwarrend overkomen. Het "relatie-maak"-venster en het "join-maak"-venster lijken nogal op elkaar. Je kan zelfs binnen de relatie al opgeven wat het jointype is. (Terwijl dat volgens mij geen eigenschap is van de relatie.)
Hoewel het gebruikersgemak groot is, verkijk ik me dan soms toch op wat ik precies construeer

Groet,
Philip

 

[OctaFish]

Ik probeer eigenlijk precies te begrijpen hoe relaties (en joins) onder water in Access werken, want ik merk dat de schermpjes zelf op mij steeds weer verwarrend overkomen. Het "relatie-maak"-venster en het "join-maak"-venster lijken nogal op elkaar. Je kan zelfs binnen de relatie al opgeven wat het jointype is. (Terwijl dat volgens mij geen eigenschap is van de relatie.)

Hier moest ik een paar keer opnieuw overlezen wat je bedoelt, maar ik vermoed dat ik het nu snap. Je hebt een apart venster (Relaties) dat je opent vanuit <Hulpprogramma's>. Daarin leg je de koppelingen vast tussen de verschillende tabellen. Elke relatie (in het Engels: "Join") heeft eigenschappen, en die stel je in onder de knop <Joineigenschappen>. Die Join (of: relatie) kent 3 varianten: Inner Join, Left Outer Join en Right Outer Join. De laatste twee zijn varianten van elkaar. De eerste variant is de belangrijkste. Inner Join bepaalt namelijk dat je géén records mag hebben in de gekoppelde tabel die níet bestaan in de hoofdtabel. Dus géén bestellingen verwijderen als de klantID niet bestaat. Haal je RI weg uit het relaties venster, dan heb je dus geen één-op-veel relatie, en dan mag het wel. Die relatievorm is dus, zoals ik al schreef, volslagen waardeloos. Ik raad al iedereen dus altijd aan om a) Referentiële Integriteit altijd aan te zetten en b) niet te rommelen aan het Jointype, maar altijd Inner Join te gebruiken. Daarnaast is het gebruik van opties Bijwerken en Verwijderen optioneel. Bijwerken kan zinvol zijn bij handmatige sleutelvelden, en Verwijderen dus bij het verwijderen van alle gekoppelde data. De waarschuwing kun je overigens ook uitzetten (bij mij standaard uit) maar dat kan dus rampzalige gevolgen hebben. Ik zeg dus: wees daar voorzichtig mee, of gebruik het in beginsel niet. Je kunt het ook anders oplossen.

Als je een correcte relatie hebt gelegd, en je maakt een nieuwe query op basis van die twee tabellen, dan zal Access de relatie tussen de twee tabellen ook laten zien als een één-op-veel relatie. En net als in het Relaties venster, kun je dubbelklikken op de lijn. Maar waar je in het Relaties venster in het hoofdvenster van de relatie terecht komt zodat je daar aanpassingen kunt doen, kom je in de query gelijk uit bij de knop <Join eigenschappen>. Is logisch, want in een queyr kun je de relatie niet aanpassen. Maar dus wél de Joineigenschappen. En dat is perfect, want dit is m.i. de enige zinvolle plek waar je dat wilt kunnen doen. In een query dus.

In beginsel wil je queries maken waarin de gegevens overeenkomen in beide tabellen (Inner Join). Maar soms wil je een lijst met klanten die nog nooit iets besteld hebben. Dat kan alleen met een Outer Join: je laat dan alle records zien uit Klanten, en alleen de gerelateerde uit Bestellingen. Vervolgens filter je het veld KlantID uit Bestellingen op Null (leeg). Je ziet dan dus alle klanten zonder bestellingen. Handig als je die wilt mailen bijvoorbeeld.

Dus dat is hopelijk het antwoord op je vraag: wat jij denkt dat licht verschillend is, is eigenlijk volkomen identiek.

 

[HeuvelP]

Opnieuw bedankt voor je reactie, OctaFish.
Ik ben zelf heel erg voorzichtig, als ik niet helemaal zeker ben van mijn theoretisch begrip van de achtergrond. En op het gevaar af dat deze discussie erg theoretisch wordt nog een reactie.

Voor mijn begrip werkt het het beste als ik twee dingen helemaal uit elkaar houdt:
- "echte" relaties tussen tabellen op basis van een PK en een FK of in ieder geval een sleutelveld (SQL:"relationships")
- joins, als tijdelijke verbinding tussen tabellen ten behoeve van een query, een bevraging (heet in SQL volgens mij niet "relationship")

Access geeft ons dan de volgende twee schermen:
- Via Hulpmiddelen > Relaties het tabblad Relaties ("Relationships"), met eventueel het venster "Relaties bewerken" en subvenster "Joineigenschappen". (Afb. 1).

- Natuurlijk de query-ontwerpweergave, waarin inderdaad niet de relaties kunnen worden bewerkt, maar alleen de eigenschappen van de join, t.b.v. de query. (Afb. 2)


Wat ik dan verwarrend vind is dat in het tabblad Relaties niet alleen de "echte" relaties kunnen worden geconstrueerd, maar ook allerlei andere verbanden die je soms in een query kunt gebruiken (en die dan een join heten), en zelfs onzinrelaties zolang de velden maar hetzelfde datatype hebben. En dat je in al die gevallen ook nog "join-eigenschappen" kunt instellen.

Jij zegt in je antwoord:

"Inner Join bepaalt namelijk dat je géén records mag hebben in de gekoppelde tabel die níet bestaan in de hoofdtabel. Dus géén bestellingen verwijderen als de klantID niet bestaat."

Maar komt dat inderdaad doordat er een Inner Join is gedefinieerd? Of komt dat doordat je RI hebt aangevinkt?
Je kunt immers een verbinding leggen tussen twee tabellen zónder RI, waarbij dat dus niet gewaarborgd is. Maar volgens Access staat default het jointype dan nog steeds op Inner Join. Ik denk dat die join-eigenschap alleen iets doet bij een query en geen eigenschap is van de relatie.

Voor de veiligheid gebruik ik het venster Relaties in ieder geval alleen voor relaties waarin de brontabel een sleutelveld heeft dat gekoppeld kan worden (zoals je ook in je eerste antwoord zegt.) En vervolgens stel ik de RI in. De joineigenschappen stel ik pas in als ik daadwerkelijk een query maak.
Andere soorten verbindingen gebruik ik alleen in een query en maak ik daarom ook alleen in het query-ontwerpweergave. Daar zit ook het venster join-eigenschappen, maar inderdaad is dat daar voldoende.

Groet, Philip

 

[OctaFish]

... en zelfs onzinrelaties zolang de velden maar hetzelfde datatype hebben. En dat je in al die gevallen ook nog "join-eigenschappen" kunt instellen.

Een 'onzin'relatie is een relatie die geen RI heeft. Daarbij kun je dus werkelijk elk veld van elk type 'koppelen' aan een ander veld van willekeurig type. Dus een tekstveld aan een datumveld, een Ja/Nee veld aan een datumveld, een Ja/Nee veld aan een tekstveld: doe maar wat je wilt, het heeft toch geen enkele betekenis. Daarom is het ook een zinloze koppeling. Ik zie ze overigens nog vaak zat in geposte voorbeelden .

Volgens mij haal je nog steeds wat dingen door elkaar; een Relationship is wel degelijk een Join, alleen een vaste join. Je geeft die ook dezelfde eigenschappen als een 'gewone' join. Vandaar dat het niets uitmaakt waar je de eigenschappen instelt, maar zie je, als je een query maakt, de in de Relationship gemaakte eigenschappen van de relatie terug als Joineigenschappen. Een query erft dus de eigenschappen van een Relationship, maar een Relationship zal nooit de eigenschappen van een query erven. Een Join of een Relationship heeft altijd een Joineigenschap. Standaard is dat altijd een INNER JOIN. Als je dus een Relation legt in het Relaties venster, dan krijgt die relatie standaard altijd de eigenschap Inner Join. Je hoeft daarvoor niet de Joineigenschappen aan te passen.

Dus, samenvattend: in Access kun je relaties leggen tussen tabellen op basis van een sleutelveld uit Tabel A en een koppelveld in Tabel B. Een relatie heeft altijd een JOIN eigenschap; standaard is dat een INNER JOIN. Zo'n relatie is de basis voor alle queries die je maakt; daarbij krijgt de query de JOIN eigenschappen uit de Relationship. Per query kun je de Join aanpassen; daarbij heb je de keuze uit LEFT JOIN (alles uit de bron tabel) of RIGHT JOIN (alles uit de gekoppelde tabel). Meestal is één van de twee in een query nutteloos. Neem weer de bestellingen: daarmee kun je met een LEFT JOIN filteren op klanten die niets besteld hebben. Maar de RIGHT JOIN laat alle bestellingen zien die geen klant hebben. En dat kán helemaal niet, want je hebt in je relaties vastgelegd dat je nooit een bestelling mag maken waarvoor je geen klantID hebt.

Gebruik ik de RIGHT JOIN dan nooit? Nou, meestal dus niet. Maar het komt wel eens voor dat je uit een ander bestand records moet importeren, en relateren aan een brontabel. In dat geval gebruik ik de Right Join om te kijken welke importrecords géén relatie hebben met de brontabel. Die records gaan terug naar de aanleverende partij, zodat die kan aanvullen. Of ik importeer die records (nog) niet.

 

[HeuvelP]

Hallo OctaFish,

Bedankt voor je reacties. Mooie samenvatting.
En ook voor het antwoord op de oorspronkelijke vraag (wat is de RI als je beide andere vinkjes uit hebt staan.) Is me nu ook helder.
Groet,
Philip