Jak zdefiniować relacje między tabelami w bazie danych programu Access

Oryginalny numer KB: 304466

Podsumowanie

W tym artykule opisano sposób definiowania relacji w bazie danych programu Microsoft Access. W tym artykule omówiono następujące zagadnienia:

• Co to są relacje tabel?
• Rodzaje relacji tabel
  • Relacje jeden-do-wielu
  • Relacje wiele-do-wielu
  • Relacje jeden-do-jednego
• Jak zdefiniować relacje między tabelami
  • Jak zdefiniować relację jeden-do-wielu lub jeden-do-jednego
  • Jak zdefiniować relację wiele-do-wielu
• Integralność referencyjna
• Kaskadowe aktualizacje i kaskadowe usuwanie
• Typy sprzężenia

Co to są relacje tabel?

W relacyjnej bazie danych relacje umożliwiają zapobieganie nadmiarowym danym. Na przykład, jeśli projektuje się bazę danych, która będzie śledzić informacje o książkach, może pojawić się tabela o nazwie „Tytuły”, która przechowuje informacje o każdej książce, takie jak tytuł książki, data publikacji i wydawca. Istnieją również informacje, które można przechowywać o wydawcy, takie jak numer telefonu wydawcy, adres i kod pocztowy. Jeśli wszystkie te informacje mają być przechowywane w tabeli „Tytuły”, numer telefonu wydawcy zostanie zduplikowany dla każdego tytułu drukowanego przez wydawcę.

Lepszym rozwiązaniem jest przechowywanie informacji wydawcy tylko raz w osobnej tabeli, którą będziemy nazywać "Wydawcami". Następnie należy umieścić wskaźnik w tabeli "Tytuły", który odwołuje się do wpisu w tabeli "Publishers".

Aby upewnić się, że dane pozostają zsynchronizowane, można wymusić integralność referencyjną między tabelami. Relacje integracji referencyjnej pomagają upewnić się, że informacje w jednej tabeli są zgodne z informacjami w innej. Na przykład każdy tytuł w tabeli „Tytuły” musi być skojarzony z określonym wydawcą w tabeli „Wydawcy”. Nie można dodać tytułu do bazy danych dla wydawcy, który nie istnieje w bazie danych.

Relacje logiczne w bazie danych umożliwiają efektywne wykonywanie zapytań o dane i tworzenie raportów.

Rodzaje relacji tabel

Relacja działa przez dopasowywanie danych w kolumnach kluczy, zwykle kolumn (lub pól), które mają taką samą nazwę w obu tabelach. W większości przypadków relacja łączy klucz podstawowy lub kolumnę unikatowego identyfikatora dla każdego wiersza z jednej tabeli z polem w innej tabeli. Kolumna w drugiej tabeli jest nazywana "kluczem obcym". Jeśli na przykład chcesz śledzić sprzedaż każdego tytułu książki, utworzysz relację między kolumną klucza podstawowego (nazwijmy ją title_ID) w tabeli "Tytuły" i kolumną w tabeli "Sales" o nazwie title_ID. Kolumna title_ID w tabeli „Sprzedaż” jest kluczem obcym.

Istnieją trzy rodzaje relacji między tabelami. Rodzaj tworzonej relacji zależy od sposobu definiowania powiązanych kolumn.

Relacje jeden-do-wielu

Relacja jeden-do-wielu jest najczęstszym rodzajem relacji. W tego rodzaju relacji wiersz w tabeli A może mieć wiele pasujących wierszy w tabeli B. Jednak wiersz w tabeli B może mieć tylko jeden pasujący wiersz w tabeli A. Na przykład tabele „Wydawcy” i „Tytuły” mają relację jeden do wielu. Oznacza to, że każdy wydawca produkuje wiele tytułów. Ale każdy tytuł pochodzi tylko od jednego wydawcy.

Relacja jeden-do-wielu jest tworzona, jeśli tylko jedna z powiązanych kolumn jest kluczem podstawowym lub ma unikatowe ograniczenie.

W oknie relacji w programie Access strona klucza podstawowego relacji jeden-do-wielu jest oznaczona przez liczbę 1. Strona klucza obcego relacji jest oznaczona symbolem nieskończoności.

Relacje wiele-do-wielu

W relacji wiele-do-wielu wiersz w tabeli A może mieć wiele pasujących wierszy w tabeli B i odwrotnie. Taką relację można utworzyć, definiując trzecią tabelę, która jest nazywana tabelą skrzyżowań. Klucz podstawowy tabeli skrzyżowań składa się z kluczy obcych z tabeli A i tabeli B. Na przykład tabela „Autorzy” i tabela „Tytuły” są w relacji wiele-do-wielu, która jest definiowana przez relację jeden-do-wielu z każdej z tych tabel do tabeli „TytułAutorzy”. Kluczem podstawowym tabeli „TitleAuthors” jest kombinacja kolumny au_ID (klucz podstawowy tabeli „Authors”) i kolumny title_ID (klucz podstawowy tabeli „Titles”).

Relacje jeden-do-jednego

W relacji jeden-do-jednego wiersz w tabeli A może mieć nie więcej niż jeden pasujący wiersz w tabeli B i odwrotnie. Relacja jeden-do-jednego jest tworzona, jeśli obie kolumny powiązane są kluczami podstawowymi lub mają wyjątkowe ograniczenia.

Ten rodzaj relacji nie jest powszechny, ponieważ większość informacji, które są związane w ten sposób znajdzie się w jednej tabeli. Relacji jeden-do-jednego można użyć do podjęcia następujących czynności:

• Podzielenia tabeli z wieloma kolumnami.
• Izolowania część tabeli ze względów bezpieczeństwa.
• Przechowywania danych, które są krótkotrwałe i można je łatwo usunąć, usuwając tabelę.
• Przechowywania informacji, które mają zastosowanie tylko do podzbioru tabeli głównej.

W programie Access strona klucza podstawowego relacji jeden-do-jednego jest oznaczona symbolem klucza. Strona klucza obcego jest również oznaczona symbolem klucza.

Jak zdefiniować relacje między tabelami

Podczas tworzenia relacji między tabelami pola pokrewne nie muszą mieć tych samych nazw. Pola pokrewne muszą jednak mieć ten sam typ danych, chyba że pole klucza podstawowego jest polem Autonumerowania. Pole Autonumerowania można dopasować do pola Liczba tylko wtedy, gdy właściwość RozmiarPola obu pasujących pól jest taka sama. Na przykład można dopasować pole Autonumerowania i pole Liczba, jeśli właściwość RozmiarPola dla obu pól to Długa liczba całkowita. Nawet wtedy, gdy oba pasujące pola są polami liczby, muszą mieć takie samo ustawienie właściwości RozmiarPola.

Jak zdefiniować relację jeden-do-wielu lub jeden-do-jednego

Aby utworzyć relację jeden-do-wielu lub jeden-do-jednego, wykonaj następujące kroki:

1. Zamknij wszystkie tabele. Nie można tworzyć ani zmieniać relacji między otwartymi tabelami.

2. W programie Access 2002 i Access 2003 wykonaj następujące kroki:

   1. Naciśnij klawisz F11, aby przełączyć się do okna Baza danych.
   2. W menu Narzędzia kliknij polecenie Relacje.

   W programie Access 2007, Access 2010 lub Access 2013 kliknij pozycję Relacje w grupie Pokaż/ukryj na karcie Narzędzia baz danych.

3. Jeśli nie zdefiniowano jeszcze żadnych relacji w bazie danych, zostanie wyświetlone automatycznie okno dialogowe Pokaż tabelę. Aby dodać tabele, które chcesz powiązać, ale nie jest wyświetlane okno dialogowe Pokaż tabelę, kliknij polecenie Pokaż tabelę w menu Relacje.

4. Kliknij dwukrotnie nazwy tabel, które chcesz powiązać, a następnie zamknij okno dialogowe Pokaż tabelę. Aby utworzyć relację między tabelą a nią samą, dodaj tę tabelę dwa razy.

5. Przeciągnij pole, które ma zostać powiązane z jednej tabeli, do pola pokrewnego w drugiej tabeli. Aby przeciągnąć wiele pól, naciśnij klawisz Ctrl, kliknij wybrane pola, a następnie przeciągnij je.

   W większości przypadków należy przeciągnąć pole klucza podstawowego (to pole wyświetlane jest pogrubioną czcionką) z jednej tabeli do podobnego pola (to pole często ma tę samą nazwę), które jest nazywane kluczem obcym w drugiej tabeli.

6. Wyświetlane jest okno dialogowe Edytuj relacje. Upewnij się, że nazwy pól wyświetlane w dwóch kolumnach są poprawne. Można zmienić nazwy, jeśli jest to konieczne.

   Ustaw opcje relacji, jeśli jest to konieczne. Jeśli w oknie dialogowym Edytuj relacje musisz zawrzeć informacje o określonym elemencie, kliknij przycisk znaku zapytania, a następnie kliknij element. (Te opcje zostaną szczegółowo wyjaśnione w dalszej części tego artykułu).

7. Kliknij przycisk Utwórz, aby utworzyć relację.

8. Powtórz kroki od 4 do 7 dla każdej pary tabel, które chcesz powiązać.

   Po zamknięciu okna dialogowego Edytuj relacje program Access zapyta, czy chcesz zapisać układ. Niezależnie od tego, czy sam układ jest zapisywany, utworzone relacje są zapisywane w bazie danych.

   Uwaga

   Relacje można tworzyć nie tylko w tabelach, ale także w kwerendach. Jednak integralność referencyjna nie jest wymuszana w przypadku kwerend.

Jak zdefiniować relację wiele-do-wielu

Aby utworzyć relację wiele-do-wielu, wykonaj następujące czynności:

1. Utwórz dwie tabele, które będą miały relację wiele-do-wielu.

2. Utwórz trzecią tabelę. To jest tabela skrzyżowań. W tabeli skrzyżowań dodaj nowe pola, które mają takie same definicje jak pola klucza podstawowego z każdej tabeli utworzonej w kroku 1. W tabeli skrzyżowań pola klucza podstawowego działają jako klucze obce. Do tabeli skrzyżowań można dodawać inne pola, tak samo jak do dowolnej innej tabeli.

3. W tabeli skrzyżowań ustaw klucz podstawowy tak, aby uwzględniał pola klucza podstawowego z pozostałych dwóch tabel. Na przykład w tabeli skrzyżowań „TytułAutorzy” klucz podstawowy będzie składał się z pól OrderID i ProductID.

   Uwaga

   Aby utworzyć klucz podstawowy, wykonaj następujące czynności:

   1. Otwórz tabelę w widoku Projekt.

   2. Zaznacz pole lub pola, które chcesz zdefiniować jako klucz podstawowy. Aby zaznaczyć jedno pole, kliknij selektor wierszy dla żądanego pola. Aby zaznaczyć wiele pól, przytrzymaj naciśnięty klawisz Ctrl, a następnie kliknij selektor wierszy dla każdego pola.

   3. W programie Access 2002 lub Access 2003 kliknij pozycję Klucz podstawowy na pasku narzędzi.

      W programie Access 2007 kliknij pozycję Klucz podstawowy w grupie Narzędzia na karcie Projekt.

      Uwaga

      Jeśli kolejność pól w wielobiegowym kluczu podstawowym ma się różnić od kolejności tych pól w tabeli, kliknij pozycję Indeksy na pasku narzędzi, aby wyświetlić okno dialogowe Indeksy, a następnie uporządkuj nazwy pól dla indeksu o nazwie Klucz podstawowy.

4. Zdefiniuj relację jeden-do-wielu między każdą tabelą podstawową a tabelą skrzyżowań.

Integralność referencyjna

Integralność referencyjna to system reguł używany przez program Access do upewnienia się, że relacje między rekordami w powiązanych tabelach są prawidłowe i że nie można przypadkowo usunąć ani zmienić powiązanych danych. Integralność referencyjną można ustawić, gdy spełnione są wszystkie następujące warunki:

• Pasujące pole z tabeli podstawowej jest kluczem podstawowym lub ma unikatowy indeks.
• Pola pokrewne mają ten sam typ danych. Istnieją dwa wyjątki. Pole Autonumerowanie może być powiązane z polem Liczba, które ma FieldSize ustawienie właściwości Liczba całkowita długa, a pole Autonumerowanie, które maFieldSize ustawienie właściwości Identyfikator replikacji, może być powiązane z polem Liczba, które maFieldSize ustawienie właściwości Identyfikator replikacji.
• Obie tabele należą do tej samej bazy danych programu Access. Jeśli tabele są tabelami połączonymi, muszą być tabelami w formacie programu Access. Należy otworzyć bazę danych, w której są przechowywane, aby ustawić integralność referencyjną. Nie można wymusić integralności referencyjnej dla tabel połączonych z bazami danych w innych formatach.

Podczas korzystania z integralności referencyjnej mają zastosowanie następujące reguły:

• Nie można wprowadzić wartości w polu klucza obcego powiązanej tabeli, która nie istnieje w kluczu podstawowym tabeli podstawowej. Można jednak wprowadzić wartość Null w kluczu obcym. Określa to, że rekordy nie są ze sobą powiązane. Na przykład nie może istnieć zamówienie, które jest przypisane do klienta, który nie istnieje. Można jednak uzyskać zamówienie, które nie będzie przypisane do nikogo, poprzez wprowadzenie Wartości zerowej w polu CustomerID.
• Nie można usunąć rekordu z tabeli podstawowej, jeśli pasujące rekordy istnieją w powiązanej tabeli. Na przykład nie można usunąć rekordu pracownika z tabeli „Pracownicy”, jeśli w tabeli „Zamówienia” są przypisane zamówienia.
• Nie można zmienić wartości klucza podstawowego w tabeli podstawowej, jeśli ten rekord ma rekordy pokrewne. Na przykład nie można zmienić Identyfikatora pracownika w tabeli „Pracownicy”, jeśli w tabeli „Zamówienia” są przypisane zamówienia.

Kaskadowe aktualizacje i kaskadowe usuwanie

W przypadku relacji, w których integralność referencyjna jest wymuszana, można określić, czy program Access ma automatycznie aktualizować kaskadowo, czy usuwać kaskadowo powiązane rekordy. Jeśli ustawisz te opcje, należy usunąć i zaktualizować operacje, które są zwykle blokowane przez reguły integralności referencyjnej. Po usunięciu rekordów lub zmianie wartości klucza podstawowego w tabeli podstawowej program Access wprowadza niezbędne zmiany w powiązanych tabelach w celu zachowania integralności referencyjnej.

Kliknięcie pola wyboru Kaskadowo aktualizuj pola pokrewne podczas definiowania relacji powoduje, że program Microsoft Access automatycznie aktualizuje klucz podstawowy do nowej wartości we wszystkich powiązanych rekordach, po każdej zmianie podstawowego klucza rekordu w tabeli podstawowej. Na przykład, jeśli zmienisz identyfikator klienta w tabeli „Klienci”, pole CustomerID w tabeli „Zamówienia” zostanie automatycznie zaktualizowane dla każdego zamówienia tego klienta, tak aby relacja nie została przerwana. Dostęp do kaskadowych aktualizacji bez wyświetlania żadnych komunikatów.

Jeśli podczas definiowania relacji zostanie zaznaczone pole wyboru Kaskadowo usuń rekordy pokrewne, za każdym razem, gdy rekordy zostaną usunięte w tabeli podstawowej, program Access automatycznie usunie rekordy pokrewne w powiązanej tabeli. Na przykład usunięcie rekordu odbiorcy z tabeli „Klienci” spowoduje automatyczne usunięcie wszystkich zamówień odbiorcy z tabeli „Zamówienia”. (Obejmuje to rekordy w tabeli „Szczegóły zamówienia”, które są związane z rekordami „Zamówienia”). Po usunięciu rekordów z formularza lub arkusza danych po zaznaczeniu pola wyboru Kaskadowo usuń rekordy pokrewne program Access ostrzega, że powiązane rekordy również mogą zostać usunięte. Jednak po usunięciu rekordów przy użyciu kwerendy usuwającej program Access automatycznie usuwa rekordy w powiązanych tabelach bez wyświetlania ostrzeżenia.

Typy sprzężenia

Istnieją trzy typy sprzężenia. Przedstawiono je następującym zrzucie ekranu:

Opcja 1 definiuje sprzężenie wewnętrzne. Sprzężenie wewnętrzne jest sprzężeniem, w którym rekordy z dwóch tabel są łączone w wynikach kwerendy tylko wtedy, gdy wartości w polach sprzężonych spełniają określony warunek. W kwerendzie sprzężenie domyślne jest sprzężeniem wewnętrznym, które wybiera rekordy tylko wtedy, gdy wartości w polach sprzężonych są zgodne.

Opcja 2 definiuje lewe sprzężenie zewnętrzne. Lewe sprzężenie zewnętrzne jest sprzężeniem, w którym wszystkie rekordy z lewej strony operacji LEWE SPRZĘŻENIE w instrukcji kwerendy SQL są dodawane do wyników kwerendy, nawet jeśli nie ma żadnych pasujących wartości w polu sprzężonym z tabeli po prawej stronie.

Opcja 3 definiuje prawe sprzężenie zewnętrzne. Prawe sprzężenie zewnętrzne jest sprzężeniem, w którym wszystkie rekordy z prawej strony operacji PRAWE SPRZĘŻENIE w instrukcji kwerendy SQL są dodawane do wyników kwerendy, nawet jeśli nie ma pasujących wartości w polu sprzężonym z tabeli po lewej stronie.