Relaties tussen tabellen in een Access-database definiëren

Opmerking

Beginner: Vereist kennis van de gebruikersinterface op computers met één gebruiker. Dit artikel is alleen van toepassing op een Microsoft Access-database (.mdb of .accdb).

Samenvatting

In dit artikel wordt beschreven hoe u relaties in een Microsoft Access-database definieert. Het artikel omvat:

• 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
• Verbindingstypen

Wat zijn tabelrelaties?

In een relationele database kunt u met relaties redundante gegevens voorkomen. Als u bijvoorbeeld een database ontwerpt die informatie over boeken bijhoudt, hebt u mogelijk een tabel met de naam 'Titels' waarin informatie over elk boek wordt opgeslagen, zoals de titel van het boek, de datum van publicatie en de uitgever. Er zijn ook gegevens die u mogelijk wilt opslaan over de uitgever, zoals het telefoonnummer, het adres en de postcode van de uitgever. Als u al deze informatie opslaat in de tabel Titels, 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 met de naam 'Uitgevers'. Vervolgens plaatst u een aanwijzer in de tabel Titels die verwijst naar een vermelding in de tabel Uitgevers.

Om ervoor te zorgen dat uw gegevens gesynchroniseerd blijven, kunt u referentiële integriteit tussen tabellen afdwingen. Referentiële integriteitsrelaties helpen ervoor te zorgen dat informatie in de ene tabel overeenkomt met informatie in een andere tabel. Elke titel in de tabel Titels moet bijvoorbeeld worden 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 bestaat.

Met logische relaties in een database kunt u efficiënt query's uitvoeren op gegevens en rapporten maken.

Soorten tabelrelaties

Een relatie werkt door gegevens in sleutelkolommen te vergelijken, 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 naar 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 vreemde sleutel.

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

Een-op-veel-relaties

Een een-op-veel-relatie is het meest voorkomende soort relatie. In dit soort relaties kan een rij in tabel A veel overeenkomende rijen bevatten in tabel B. 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 gezegd, elke uitgever produceert veel titels. Maar elke titel is afkomstig van slechts één uitgever.

Er wordt een een-op-veel-relatie gemaakt als slechts één 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 getal 1. De vreemde sleutelzijde van een relatie wordt aangeduid met het oneindigheidssymbool.

Veel-op-veel-relaties

In een veel-op-veel-relatie kan een rij in tabel A veel overeenkomende rijen bevatten in tabel B en omgekeerd. U maakt een dergelijke relatie door een derde tabel te definiëren die een verbindingstabel wordt genoemd. De primaire sleutel van de verbindingstabel bestaat uit de vreemde sleutels van 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 van elk van deze tabellen naar de tabel "TitelAuteurs". De primaire sleutel van de tabel TitleAuthors is de combinatie van de kolom au_ID (de primaire sleutel van de tabel Auteurs) en de title_ID kolom (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 relatie is niet gebruikelijk, omdat de meeste informatie die op deze manier is gerelateerd, zich in één tabel bevindt. U kunt een een-op-een-relatie gebruiken om de volgende acties uit te voeren:

• Een tabel delen met veel kolommen.
• Een deel van een tabel isoleren om veiligheidsredenen.
• Sla gegevens op die kortlopend zijn en kan eenvoudig worden verwijderd door de tabel te verwijderen.
• Sla informatie op die alleen van toepassing is op een subset van de hoofdtabel.

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

Relaties tussen tabellen definiëren

Wanneer u een relatie tussen tabellen maakt, hoeven de gerelateerde velden niet dezelfde namen te hebben. Gerelateerde velden moeten echter hetzelfde gegevenstype hebben, tenzij het primaire-sleutelveld een AutoNummering-veld is. U kunt een AutoNummering-veld alleen vergelijken met een getalveld als de VeldSizeproperty van beide overeenkomende velden hetzelfde is. U kunt bijvoorbeeld een AutoNummering-veld en een getalveld op elkaar afstemmen als de veldgrootte-eigenschap van beide velden Long Integer is. Zelfs wanneer beide overeenkomende velden numerieke velden zijn, moeten ze dezelfde instellingFieldSizeproperty 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, voert u de volgende stappen uit:

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

2. Voer in Access 2002 of Access 2003 de volgende stappen uit:

   1. Druk op F11 om over te schakelen naar het databasevenster.
   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 Hulpmiddelen voor databases .

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

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

5. Sleep het veld dat u uit de ene tabel wilt relateren aan het gerelateerde veld in de andere tabel. Als u meerdere velden wilt 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 vergelijkbaar veld (dit veld heeft vaak dezelfde naam) dat de vreemde sleutel in de andere tabel genaamd is.

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

   Stel de relatieopties in als dat nodig is. Als u informatie over een specifiek item in het dialoogvenster Relaties bewerken moet hebben, klikt u op de knop Vraagteken en klikt u vervolgens op het item. (Deze opties worden verderop 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, wordt u gevraagd of u de indeling wilt opslaan. Of u nu de indeling opslaat of de indeling niet opslaat, de relaties die u maakt, worden opgeslagen in de database.

   Opmerking

   U kunt niet alleen relaties maken in tabellen, maar ook in query's. Echter, wordt referentiële integriteit 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 zullen hebben.

2. Maak een derde tabel. Dit is de verbindingstabel. Voeg in de verbindingstabel nieuwe velden toe met dezelfde definities als de primaire-sleutelvelden uit elke tabel die u in stap 1 hebt gemaakt. In de verbindingstabel functioneren de primaire sleutelvelden als vreemde sleutels. U kunt andere velden toevoegen aan de verbindingstabel, 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 TitleAuthors bestaat de primaire sleutel bijvoorbeeld uit de velden Order-id en Product-id .

   Opmerking

   Voer de volgende stappen uit 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. Als u één veld wilt selecteren, klikt u op de rijkiezer voor het gewenste veld. Als u meerdere velden wilt selecteren, houdt u 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 deze velden in de tabel, klikt u op Indexen op de werkbalk om het dialoogvenster Indexen weer te geven en rangschikt u 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 wordt 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 wanneer 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 AutoNummering-veld kan worden gerelateerd aan een numeriek veld met een FieldSize eigenschapsinstelling lange integer en een AutoNummering-veld met een FieldSize eigenschapsinstelling van de replicatie-id kan worden gerelateerd aan een numeriek veld met een FieldSize eigenschapsinstelling van de replicatie-id.
• Beide tabellen behoren tot dezelfde Access-database. Als de tabellen gekoppelde tabellen zijn, moeten ze tabellen zijn in Access-indeling en moet u de database openen waarin ze worden opgeslagen om 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 van de vreemde sleutel in de gerelateerde tabel die niet bestaat in de primaire sleutel van de hoofdtafel. U kunt echter in de vreemde sleutel een Null-waarde invoeren. Hiermee geeft u op 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 order hebben die aan niemand is toegewezen door een Null-waarde in te voeren in het veld CustomerID .
• U kunt een record niet verwijderen uit een primaire tabel als overeenkomende records aanwezig zijn in een gerelateerde tabel. U kunt bijvoorbeeld geen werknemersrecord verwijderen uit de tabel Werknemers als er orders 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 bevat. U kunt bijvoorbeeld de id van een werknemer niet wijzigen in de tabel Werknemers als er orders 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 access gerelateerde records automatisch moet bijwerken of trapsgewijs verwijderen. Als u deze opties instelt, worden verwijderings- en updatebewerkingen die meestal worden voorkomen door regels voor referentiële integriteit ingeschakeld. Wanneer u records verwijdert of primaire-sleutelwaarden in een primaire tabel wijzigt, worden de benodigde wijzigingen aangebracht in gerelateerde tabellen om referentiële integriteit te behouden.

Als u klikt om het selectievakje Gerelateerde velden trapsgewijs bijwerken in te schakelen wanneer u een relatie definieert, wordt de primaire sleutel van een record in de primaire tabel automatisch door Microsoft Access bijgewerkt naar de nieuwe waarde in alle gerelateerde records. Als u bijvoorbeeld de klant-id in de tabel Klanten wijzigt, wordt het veld Klant-id in de tabel Orders automatisch bijgewerkt voor elk van de orders van die klant, zodat de relatie niet wordt verbroken. Access voert updates trapsgewijs uit zonder een melding weer te geven.

Opmerking

Als de primaire sleutel in de primaire tabel een AutoNummering-veld is, heeft het selectievakje Gerelateerde velden trapsgewijs bijwerken geen effect omdat u de waarde in een AutoNummering-veld niet kunt wijzigen.

Als u het selectievakje Gerelateerde records trapsgewijs verwijderen inschakelt wanneer u een relatie definieert, worden gerelateerde records in de gerelateerde tabel automatisch verwijderd wanneer u records in de primaire tabel verwijdert. Als u bijvoorbeeld een klantrecord uit de tabel Klanten verwijdert, 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 verwijdert uit een formulier of gegevensblad wanneer het selectievakje Gerelateerde 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, worden de records in gerelateerde tabellen automatisch verwijderd zonder een waarschuwing weer te geven.

Verbindingstypen

Er zijn drie jointypen. U kunt deze zien in de volgende schermafbeelding:

Optie 1 definieert een inner join. Een inner join is een join waarbij 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 standaarddeelname een inner join die alleen records selecteert als waarden in de gekoppelde velden overeenkomen.

Optie 2 definieert een linkse buitenste join. Een left outer join is een join waarin alle records aan 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 gekoppelde veld uit de tabel aan de rechterkant zijn.

Optie 3 definieert een rechter outer join. Een right outer join is een join waarin alle records aan 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 zijn in het gekoppelde veld uit de tabel aan de linkerkant.