Relaties tussen tabellen in een Access-database definiëren

Origineel KB-nummer: 304466

Samenvatting

In dit artikel wordt beschreven hoe u relaties in een Microsoft Access-database kunt definiëren. Het artikel bevat de volgende onderwerpen:

• Wat zijn tabelrelaties?
• Soorten tabelrelaties
  • Een-op-veel-relaties
  • Veel-op-veel-relaties
  • Een-op-een-relaties
• Relaties tussen tabellen definiëren
  • Een een-op-veel- of een-op-een-relatie definiëren
  • Een veel-op-veel-relatie definiëren
• Referentiële integriteit
• Trapsgewijze updates en verwijderingen
• Join-typen

Wat zijn tabelrelaties?

In een relationele database kunt u met relaties overbodige gegevens voorkomen. Als u bijvoorbeeld een database ontwerpt die informatie over boeken bijhoudt, heeft u mogelijk een tabel met de naam 'Titels' waarin informatie over elk boek wordt opgeslagen, zoals de titel van het boek, de publicatiedatum en de uitgever. Er is ook informatie die u mogelijk over de uitgever wilt opslaan, zoals het telefoonnummer, adres en postcode van de uitgever. Als u al deze gegevens in de tabel 'Titels' zou opslaan, wordt het telefoonnummer van de uitgever gedupliceerd voor elke titel die de uitgever afdrukt.

Een betere oplossing is om de informatie van de uitgever slechts één keer op te slaan in een afzonderlijke tabel die we 'Uitgevers' noemen. Vervolgens plaatst u een aanwijzer in de tabel Titels die verwijst naar een vermelding in de tabel Uitgevers.

Als u ervoor wilt zorgen dat uw gegevens gesynchroniseerd zijn, kunt u referentiële integriteit tussen tabellen instellen. Referentiële integriteitsrelaties helpen ervoor te zorgen dat informatie in de ene tabel overeenkomt met gegevens in een andere tabel. Elke titel in de tabel 'Titels' moet bijvoorbeeld zijn gekoppeld aan een specifieke uitgever in de tabel 'Uitgevers'. Een titel kan niet worden toegevoegd aan de database voor een uitgever die niet in de database staat.

Logische relaties in een database stellen u in staat efficiënt gegevens op te vragen en rapporten te maken.

Soorten tabelrelaties

Een relatie werkt door gegevens te koppelen in belangrijke kolommen, meestal kolommen (of velden) met dezelfde naam in beide tabellen. In de meeste gevallen verbindt de relatie de primaire sleutel of de unieke id-kolom voor elke rij, van de ene tabel met een veld in een andere tabel. De kolom in de andere tabel wordt de 'refererende sleutel' genoemd. Als u bijvoorbeeld de verkoop van elke boektitel wilt bijhouden, maakt u een relatie tussen de primaire sleutelkolom (laten we deze title_ID noemen) in de tabel Titels en een kolom in de tabel Verkoop met de naam title_ID. De kolom title_ID in de tabel 'Verkoop' is de refererende sleutel.

Er zijn drie soorten relaties tussen tabellen. Het type relatie dat wordt gemaakt, is afhankelijk van de manier waarop de gerelateerde kolommen worden gedefinieerd.

Een-op-veel-relaties

Een een-op-veel-relatie is de meest voorkomende soort relatie. In dit type relatie kan een rij in tabel A veel overeenkomende rijen in tabel B hebben. Maar een rij in tabel B kan slechts één overeenkomende rij in tabel A hebben. De tabellen 'Uitgevers' en 'Titels' hebben bijvoorbeeld een een-op-veel-relatie. Dat wil zeggen dat elke uitgever veel titels produceert. Maar elke titel is afkomstig van slechts één uitgever.

Er wordt een een-op-veel-relatie gemaakt als slechts een van de gerelateerde kolommen een primaire sleutel is of een unieke beperking heeft.

In het relatievenster in Access wordt de primaire sleutelzijde van een een-op-veel-relatie aangeduid met een cijfer 1. De refererende sleutelzijde van een relatie wordt aangeduid met een oneindigheidssymbool.

Veel-op-veel-relaties

In een veel-op-veel-relatie kan een rij in tabel A veel overeenkomende rijen hebben in tabel B en omgekeerd. U maakt zo'n relatie door een derde tabel te definiëren die een verbindingstabel wordt genoemd. De primaire sleutel van de verbindingstabel bestaat uit de refererende sleutels uit zowel tabel A als tabel B. De tabel 'Auteurs' en de tabel 'Titels' hebben bijvoorbeeld een veel-op-veel-relatie die wordt gedefinieerd door een een-op-veel-relatie tussen elk van deze tabellen en de tabel 'Titel auteurs'. De primaire sleutel van de tabel 'Titel auteurs' is de combinatie van de kolom au_ID (de primaire sleutel van de tabel 'Auteurs') en de kolom title_ID (de primaire sleutel van de tabel 'Titels').

Een-op-een-relaties

In een een-op-een-relatie kan een rij in tabel A niet meer dan één overeenkomende rij in tabel B hebben en omgekeerd. Er wordt een een-op-een-relatie gemaakt als beide gerelateerde kolommen primaire sleutels zijn of unieke beperkingen hebben.

Dit soort relaties komt niet vaak voor, omdat de meeste informatie die op deze manier wordt gerelateerd, in één tabel zou staan. U kunt een een-op-een-relatie gebruiken om de volgende acties uit te voeren:

• Een tabel met veel kolommen verdelen.
• Een deel van een tabel isoleren om veiligheidsredenen.
• Gegevens opslaan die van korte duur zijn en gemakkelijk kunnen worden verwijderd door de tabel te verwijderen.
• Gegevens opslaan die alleen van toepassing zijn op een subset van de hoofdtabel.

In Access wordt de primaire sleutelzijde van een een-op-een-relatie aangeduid met een sleutelsymbool. De refererende sleutelzijde wordt ook aangeduid met een sleutelsymbool.

Relaties tussen tabellen definiëren

Wanneer u een relatie maakt tussen de tabellen, moeten de gerelateerde velden niet dezelfde namen hebben. Gerelateerde velden moeten echter hetzelfde gegevenstype hebben, tenzij het primaire sleutelveld een AutoNumber-veld is. U kunt een AutoNumber-veld alleen koppelen aan een Number-veld als de FieldSize-waarde van beide overeenkomende velden hetzelfde is. U kunt bijvoorbeeld een Autonumber-veld en een Number-veld overeenkomen als de FieldSize-waarde van beide velden Long Integer is. Zelfs wanneer beide overeenkomende velden Number-velden zijn, moeten ze dezelfde instelling voor FieldSize-waarde hebben.

Een een-op-veel- of een-op-een-relatie definiëren

Als u een een-op-veel- of een een-op-een-relatie wilt maken, gaat u als volgt te werk:

1. Sluit alle tabellen. U kunt geen relaties tussen geopende tabellen maken of wijzigen.

2. Volg deze stappen in Access 2002 of Access 2003:

   1. Druk op F11 om over te schakelen naar het venster Database.
   2. Klik in het menu Extra op Relaties.

   Klik in Access 2007, Access 2010 of Access 2013 op Relaties in de groep Weergeven/verbergen op het tabblad Databasehulpmiddelen.

3. Als u nog geen relaties in uw database hebt gedefinieerd, wordt automatisch het dialoogvenster Tabel weergeven weergegeven. Als u de tabellen wilt toevoegen die u wilt relateren, maar het dialoogvenster Tabel weergeven verschijnt niet, klikt u op Tabel weergeven in het menu Relaties.

4. Dubbelklik op de namen van de tabellen die u wilt relateren en sluit vervolgens het dialoogvenster Tabel weergeven. Als u een relatie wilt maken tussen een tabel en zichzelf, voegt u die tabel twee keer toe.

5. Sleep het veld dat u wilt relateren van de ene tabel naar het gerelateerde veld in de andere tabel. Om meerdere velden te slepen, drukt u op Ctrl, klikt u op elk veld en sleept u deze.

   In de meeste gevallen sleept u het primaire sleutelveld (dit veld wordt vetgedrukt weergegeven) van de ene tabel naar een soortgelijk veld (dit veld heeft vaak dezelfde naam) dat in de andere tabel de refererende sleutel wordt genoemd.

6. Het dialoogvenster Relaties bewerken verschijnt. Zorg ervoor dat de veldnamen die in de twee kolommen worden weergegeven, juist zijn. U kunt de namen wijzigen als dat nodig is.

   Stel indien nodig de relatieopties in. Als u informatie wilt hebben over een specifiek item in het dialoogvenster Relaties bewerken, klikt u op de knop vraagteken en vervolgens op het item. (Deze opties worden verder in dit artikel uitgebreid beschreven.)

7. Klik op Maken om de relatie te maken.

8. Herhaal stap 4 tot en met 7 voor elk paar tabellen dat u wilt relateren.

   Wanneer u het dialoogvenster Relaties bewerken sluit, vraagt Access of u de opmaak wilt opslaan. Of u de opmaak nu wel of niet opslaat, de relaties die u aanmaakt worden in de database bewaard.

   Opmerking

   U kunt niet alleen relaties maken in tabellen, maar ook in query's. Referentiële integriteit wordt echter niet afgedwongen met query's.

Een veel-op-veel-relatie definiëren

Volg deze stappen om een veel-op-veel-relatie te maken:

1. Maak de twee tabellen die een veel-op-veel-relatie hebben.

2. Een derde tabel maken. Dit is de verbindingstabel. Voeg in de verbindingstabel nieuwe velden toe die dezelfde definities hebben als de primaire sleutelvelden uit elke tabel die u in stap 1 hebt gemaakt. In de verbindingstabel werken de primaire sleutelvelden als refererende sleutels. U kunt andere velden aan de verbindingstabel toevoegen, net als aan elke andere tabel.

3. Stel in de verbindingstabel de primaire sleutel in om de primaire sleutelvelden uit de andere twee tabellen op te nemen. In een verbindingstabel 'Titel auteurs' bestaat de primaire sleutel bijvoorbeeld uit de velden Orderid en ProductID.

   Opmerking

   Volg deze stappen om een primaire sleutel te maken:

   1. Open een tabel in de ontwerpweergave.

   2. Selecteer het veld of de velden die u als primaire sleutel wilt definiëren. Om één veld te selecteren, klikt u op de rijkiezer voor het gewenste veld. Als u meerdere velden wilt selecteren, houdt u de toets Ctrl ingedrukt en klikt u vervolgens op de rijkiezer voor elk veld.

   3. Klik in Access 2002 of in Access 2003 op Primaire sleutel op de werkbalk.

      Klik in Access 2007 op Primaire sleutel in de groep Extra op het tabblad Ontwerpen.

      Opmerking

      Als u wilt dat de volgorde van de velden in een primaire sleutel met meerdere velden verschilt van de volgorde van die velden in de tabel, klikt u op Indexen op de werkbalk om het dialoogvenster Indexen weer te geven en rangschikt u vervolgens de veldnamen voor de index met de naam PrimaryKey.

4. Definieer een een-op-veel-relatie tussen elke primaire tabel en de verbindingstabel.

Referentiële integriteit

Referentiële integriteit is een systeem van regels dat Access gebruikt om ervoor te zorgen dat relaties tussen records in gerelateerde tabellen geldig zijn en dat u niet per ongeluk gerelateerde gegevens verwijdert of wijzigt. U kunt referentiële integriteit instellen als aan alle volgende voorwaarden wordt voldaan:

• Het overeenkomende veld uit de primaire tabel is een primaire sleutel of heeft een unieke index.
• De gerelateerde velden hebben hetzelfde gegevenstype. Er zijn twee uitzonderingen. Een AutoNumber-veld kan worden gerelateerd aan een Number-veld dat een FieldSize eigenschapinstelling van Long Integer heeft en een AutoNumber-veld dat een FieldSize eigenschapinstelling van Replicatie-id, heeft kan worden gerelateerd aan een Number-veld dat een FieldSize eigenschapinstelling van Replicatie-id heeft.
• Beide tabellen behoren tot dezelfde Access-database. Als de tabellen gekoppelde tabellen zijn, moeten het tabellen in Access-indeling zijn en moet u de database openen waarin ze zijn opgeslagen om de referentiële integriteit in te stellen. Referentiële integriteit kan niet worden afgedwongen voor gekoppelde tabellen uit databases in andere indelingen.

De volgende regels zijn van toepassing wanneer u referentiële integriteit gebruikt:

• U kunt geen waarde invoeren in het veld refererende sleutel van de gerelateerde tabel die niet bestaat in de primaire sleutel van de primaire tabel. U kunt echter wel een Null-waarde invoeren in de refererende sleutel. Dit geeft aan dat de records niet gerelateerd zijn. U kunt bijvoorbeeld geen bestelling hebben die is toegewezen aan een klant die niet bestaat. U kunt echter een bestelling hebben die aan niemand is toegewezen door een Null-waarde in het veld CustomerID in te voeren.
• U kunt een record niet verwijderen uit een primaire tabel als er overeenkomende records bestaan in een gerelateerde tabel. U kunt bijvoorbeeld geen werknemersrecord uit de tabel 'Werknemers' verwijderen als er bestellingen zijn toegewezen aan de werknemer in de tabel 'Orders'.
• U kunt een primaire sleutelwaarde in de primaire tabel niet wijzigen als die record gerelateerde records heeft. U kunt bijvoorbeeld de ID van een werknemer niet wijzigen in de tabel 'Werknemers' als er bestellingen zijn toegewezen aan die werknemer in de tabel 'Orders'.

Trapsgewijze updates en verwijderingen

Voor relaties waarin referentiële integriteit wordt afgedwongen, kunt u opgeven of u wilt dat Access gerelateerde records automatisch trapsgewijs bewerkt of trapsgewijs verwijdert. Als u deze opties instelt, worden verwijder- en updatebewerkingen ingeschakeld die normaal gesproken worden voorkomen door referentiële integriteitsregels. Wanneer u records verwijdert of primaire sleutelwaarden in een primaire tabel wijzigt, brengt Access de nodige wijzigingen aan in gerelateerde tabellen om de referentiële integriteit te behouden.

Als u het selectievakje Verwante velden trapsgewijs bijwerken inschakelt wanneer u een relatie definieert, werkt Microsoft Access elke keer dat u de primaire sleutel van een record in de primaire tabel wijzigt, automatisch de primaire sleutel bij naar de nieuwe waarde in alle gerelateerde records. Als u bijvoorbeeld de ID van een klant wijzigt in de tabel 'Klanten', wordt het veld CustomerID in de tabel 'Orders' automatisch bijgewerkt voor elke bestelling van die klant, zodat de relatie niet wordt verbroken. Krijg toegang tot trapsgewijze updates zonder een bericht weer te geven.

Als u het selectievakje Verwante records trapsgewijs verwijderen inschakelt wanneer u een relatie definieert, verwijdert Access automatisch gerelateerde records in de gerelateerde tabel wanneer u records in de primaire tabel verwijdert. Als u bijvoorbeeld een klantrecord verwijdert uit de tabel 'Klanten', worden alle orders van de klant automatisch verwijderd uit de tabel 'Orders'. (Dit omvat records in de tabel 'Ordergegevens' die zijn gerelateerd aan de records 'Orders'). Wanneer u records uit een formulier of gegevensblad verwijdert wanneer het selectievakje Verwante records trapsgewijs verwijderen is ingeschakeld, waarschuwt Access u dat gerelateerde records mogelijk ook worden verwijderd. Wanneer u echter records verwijdert met behulp van een verwijderquery, verwijdert Access automatisch de records in gerelateerde tabellen zonder een waarschuwing weer te geven.

Join-typen

Er zijn drie join-typen. U kunt ze zien in de volgende schermafbeelding:

Optie 1 definieert een inner join. Een inner join is een join waarin records uit twee tabellen alleen worden gecombineerd in de resultaten van een query als waarden in de gekoppelde velden voldoen aan een opgegeven voorwaarde. In een query is de standaard join een inner join die alleen records selecteert als de waarden in de samengevoegde velden overeenkomen.

Optie 2 definieert een left outer join. Een left outer join is een join waarin alle records van de linkerkant van de LEFT JOIN-bewerking in de SQL-instructie van de query worden toegevoegd aan de resultaten van de query, zelfs als er geen overeenkomende waarden in het samengevoegde veld zijn uit de tabel aan de rechterkant kant.

Optie 3 definieert een right outer join. Een right outer join is een join waarin alle records van de rechterkant van de RIGHT JOIN-bewerking in de SQL-instructie van de query worden toegevoegd aan de resultaten van de query, zelfs als er geen overeenkomende waarden in het samengevoegde veld zijn uit de tabel aan de linkerkant kant.