5. Relacje między tabelami

5.1. Klucze obce

Relacyjna baza danych to zbiór tabel połączonych ze sobą relacjami. Weźmy przykład oparty na poprzedniej tabeli [BIBLIO], której struktura wyglądała następująco:

Przykładowa zawartość wyglądała następująco:

Możemy chcieć uzyskać informacje o różnych autorach tych dzieł, na przykład o nom i prénom, ich datach urodzenia, nationalité. Utwórzmy taką tabelę. Kliknijmy prawym przyciskiem myszy na [DBBIBLIO / Tables], a następnie wybierzmy opcję [New Table]:

Teraz utwórzmy następującą tabelę [AUTEURS]:

`id`	klucz główny tabeli – służy do jednoznacznej identyfikacji wiersza
`nom`	nazwisko autora
`prénom`	imię autora, jeśli takie posiada
`date_naissance`	data urodzenia
`nationalite`	jego kraj pochodzenia

Zawartość tabeli [AUTEURS] mogłaby wyglądać następująco:

Wróćmy do tabeli [BIBLIO] i jej zawartości:

W kolumnie [AUTEUR] tabeli nie ma już potrzeby wpisywania nazwiska autora. Zamiast tego lepiej jest wpisać numer identyfikacyjny (id), jaki posiada on w tabeli [AUTEURS]. Utwórzmy zatem nową tabelę o nazwie [LIVRES]. Aby ją utworzyć, wykorzystamy skrypt [biblio.sql] utworzony w punkcie 3.14. Skrypt ten ładujemy za pomocą narzędzia [Script Executive, Ctrl-F12]:

Modyfikujemy skrypt tworzenia tabeli BIBLIO, aby dostosować go do skryptu tabeli LIVRES:

CREATE TABLE LIVRES (
ID          INTEGER NOT NULL,
TITRE       VARCHAR(30) NOT NULL,
AUTEUR      INTEGER,
GENRE       VARCHAR(30) NOT NULL,
ACHAT       DATE NOT NULL,
PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
DISPONIBLE  CHAR(1) NOT NULL
);

INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (2, 'Les fleurs du mal', 8, 'POèME', '1978-01-01', 120, 'n');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (3, 'Tintin au Tibet', 6, 'BD', '1990-11-10', 70, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (4, 'Du côté de chez Swann', 3, 'ROMAN', '1978-12-08', 220.5, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (5, 'La terre', 4, 'ROMAN', '1990-06-12', 55.13, 'n');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (6, 'Madame Bovary', 1, 'ROMAN', '1988-03-12', 143.33, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (7, 'Manhattan transfer', 7, 'ROMAN', '1987-08-30', 352.8, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (8, 'Tintin en Amérique', 6, 'BD', '1991-05-15', 70, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (10, 'Le père Goriot', 5, 'Roman', '1991-09-01', 210, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (11, 'Poèmes saturniens', 2, 'Poème', '1992-09-02', 200, 'o');

COMMIT WORK;



/******************************************************************************/
/****                                                     Ograniczenia unikalności ****/
/******************************************************************************/

ALTER TABLE LIVRES ADD CONSTRAINT UNQ1_LIVRES UNIQUE (TITRE);


/******************************************************************************/
/****                                                           Klucze główne ****/
/******************************************************************************/

ALTER TABLE LIVRES ADD CONSTRAINT PK_LIVRES PRIMARY KEY (ID);

Komentujemy tylko zmiany:

wiersz 4: kolumna [AUTEUR] w tabeli staje się liczbą całkowitą. Numer ten odnosi się do jednego z autorów tabeli [AUTEURS] utworzonej wcześniej.
wiersze 11–19: nazwiska autorów zostały zastąpione ich numerami autorskimi.
wiersz 29: nazwa ograniczenia została zmieniona. Wcześniej nazywało się ono [ UNQ1_BIBLIO ]. Teraz nazywa się [ UNQ1_LIVRES ]. Nazwa ta może być dowolna. Najlepiej jednak, aby miała sens. W tym przypadku nie podjęto takiego wysiłku. Ograniczenia dotyczące różnych pól i różnych tabel w bazie danych muszą być rozróżniane za pomocą różnych nazw. Przypomnijmy, że ograniczenie w wierszu 29 wymaga, aby tytuł był unikalny w tabeli.
wiersz 36: zmiana nazwy ograniczenia dotyczącego klucza głównego na ID.

Uruchommy ten skrypt. Jeśli się powiedzie, otrzymamy następującą nową tabelę [LIVRES]:

Można się zastanawiać, czy ostatecznie zyskaliśmy na tej zmianie. W rzeczywistości tabela [LIVRES] zawiera numery autorów zamiast ich nazwisk. Ponieważ autorów są tysiące, powiązanie książki z jej autorem wydaje się trudne. Na szczęście z pomocą przychodzi nam język SQL. Pozwala on na jednoczesne przeszukiwanie kilku tabel. Jako przykład przedstawiamy zapytanie SQL, które pozwala nam uzyskać tytuły książek z biblioteki wraz z informacjami o ich autorach. Skorzystajmy z edytora SQL (F12), aby wygenerować następujące polecenie SQL:

SQL> select LIVRES.titre, AUTEURS.nom, AUTEURS.prenom,AUTEURS.date_naissance
FROM LIVRES inner join AUTEURS on LIVRES.AUTEUR=AUTEURS.ID
ORDER BY AUTEURS.nom asc

Jest jeszcze zbyt wcześnie, aby wyjaśnić to polecenie SQL. Powrócimy do tego wkrótce. Wynik tego zapytania jest następujący:

Każda książka została poprawnie przypisana do swojego autora oraz powiązanych z nią informacji.

Podsumujmy to, co właśnie zrobiliśmy:

mamy dwie tabele zawierające informacje różnego rodzaju:
- tabela AUTEURS zawiera informacje o autorach
- tabela LIVRES zawiera informacje o książkach zakupionych przez bibliotekę
tabele te są ze sobą powiązane. Każda książka ma obowiązkowo jednego autora. Może nawet mieć kilku. Ten przypadek nie został tutaj uwzględniony. Kolumna [AUTEUR] w tabeli [LIVRES] odwołuje się do wiersza w tabeli [AUTEURS]. Nazywamy to relacją.

Relacja łącząca tabelę [LIVRES] z tabelą [AUTEURS] jest w rzeczywistości pewnego rodzaju ograniczeniem: wiersz w tabeli [LIVRES] musi zawsze zawierać numer autora, który istnieje w tabeli [AUTEURS]. Gdyby wiersz w tabeli [LIVRES] zawierał numer autora, który nie istnieje w tabeli [AUTEURS], mielibyśmy do czynienia z sytuacją nieprawidłową, w której nie bylibyśmy w stanie zidentyfikować autora książki.

Tabela SGBD pozwala sprawdzić, czy to ograniczenie jest zawsze spełnione. W tym celu dodamy ograniczenie do tabeli [LIVRES]:

Powiązanie łączące pozycję [AUTEUR] w tabeli [LIVRES] z polem [ID] w tabeli [AUTEURS] nazywane jest powiązaniem klucza obcego. Kolumna [AUTEUR] w tabeli [LIVRES] jest określana w powyższym kreatorze jako „klucz obcy” lub „foreign key”. Zdefiniowanie klucza obcego oznacza, że wartość kolumny [c1] w tabeli [T1] musi istnieć w kolumnie [c2] tabeli [T2]. Kolumna [c1] jest tzw. „kluczem obcym” tabeli T1 względem kolumny [c2] w tabeli [T2]. Kolumna [c2] jest często kluczem głównym tabeli [T2], ale nie jest to obowiązkowe.

Definiujemy klucz obcy [AUTEUR] z tabeli [LIVRES] na polu [ID] z tabeli [AUTEURS] w następujący sposób:

nazwa ograniczenia: dowolna
kolumna „klucz obcy”, w tym przypadku kolumna [AUTEUR] z tabeli [LIVRES]
tabela, do której odwołuje się klucz obcy. W tym przypadku kolumna [AUTEUR] z tabeli [LIVRES] musi mieć wartość w kolumnie [ID] z tabeli [AUTEURS]. W związku z tym odwołaniem jest tabela [AUTEURS].
Kolumna, do której odwołuje się klucz obcy. W tym przypadku jest to kolumna [ID] w tabeli [AUTEURS].

Sprawdzamy poprawność tego ograniczenia:

Jeśli wszystko jest w porządku, zostanie ona zaakceptowana:

Jakie są konsekwencje tego nowego ograniczenia klucza obcego? Za pomocą edytora SQL (F12) spróbujmy wstawić wiersz do tabeli LIVRES z nieistniejącym numerem autora:

Powyższa operacja [INSERT] próbowała wstawić książkę z nieistniejącym numerem autora (100). Wykonanie zapytania zakończyło się niepowodzeniem. Powiązany komunikat o błędzie wskazuje, że doszło do naruszenia ograniczenia klucza obcego „FK_LIVRES_AUTEURS”. Jest to ograniczenie, które właśnie zdefiniowaliśmy.

5.2. Operacje łączenia dwóch tabel

Wciąż w bazie [DBBIBLIO] (lub innej, to nie ma znaczenia) utwórzmy dwie tabele testowe o nazwach TA i TB, zdefiniowane w następujący sposób:

Tabela TA

- ID: klucz główny tabeli TA

- DATA: dowolna wartość

Tabela TB

- ID: klucz główny tabeli TB

- IDTA: klucz obcy tabeli TB, który odwołuje się do kolumny ID w tabeli TA. W związku z tym wartość z kolumny IDTA tabeli TA musi występować w kolumnie ID tabeli TA

- VALEUR: dowolna wartość

W edytorze SQL (F12) wygenerujemy polecenia SQL, wykorzystujące jednocześnie obie tabele TA i TB.

SQL>select * from TA,  TB

Zlecenie SQL wykorzystuje, po słowie kluczowym FROM, obie tabele TA i TB. Operacja FROM TA, TB spowoduje tymczasowe utworzenie nowej tabeli, w której każdy wiersz tabeli TA zostanie powiązany z każdym wierszem tabeli TB. Jeśli więc tabela TA ma NA wierszy, a tabela TB ma NB wierszy, tabela wynikowa będzie miała NA × NB wierszy. Pokazuje to powyższy zrzut ekranu. Ponadto każdy wiersz zawiera kolumny z obu tabel. Kolumny coli określone w kolejności [SELECT col1, col2, ... FROM ...] wskazują te, które należy zachować. W tym przypadku słowo kluczowe * oznacza, że wymagane są wszystkie kolumny tabeli wynikowej. Czasami mówi się, że tabela wynikowa poprzedniego zamówienia SQL jest iloczynem kartezjańskim tabel TA i TB.

W powyższym przykładzie każdy wiersz tabeli TA został powiązany z każdym wierszem tabeli TB. Zazwyczaj chcemy powiązać z wierszem tabeli TA te wiersze tabeli TB, które są z nim powiązane. Związek ten często przybiera formę ograniczenia klucza obcego. Tak jest w tym przypadku. Z wierszem tabeli TA można powiązać wiersze tabeli TB, które spełniają relację TB.IDTA=TA.ID. Istnieje kilka sposobów sformułowania takiego zapytania:

SQL>select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA, TB where TA.ID=TB.IDTA

Poprzednie zlecenie SQL jest analogiczne do poprzedniego, z dwiema jednak różnicami:

wiersze wynikające z iloczynu kartezjańskiego TA × TB są filtrowane przez klauzulę WHERE, która powiązuje z wierszem tabeli TA, jedynie te wiersze tabeli TB, które spełniają relację TB.IDTA=TA.ID
żądane są tylko niektóre kolumny przy użyciu składni [T.col], gdzie T to nazwa tabeli, a col to nazwa kolumny tej tabeli. Składnia ta pozwala wyeliminować niejednoznaczność, która mogłaby wystąpić, gdyby dwie tabele miały kolumny o tej samej nazwie. Gdy taka niejednoznaczność nie występuje, można użyć składni [col] bez określania tabeli, do której należy ta kolumna.

Otrzymany wynik jest następujący:

Ten sam wynik można uzyskać za pomocą następującego polecenia: SQL:

SQL>select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA inner join TB on TA.ID=TB.idta

Od terminu [inner join] pochodzi nazwa „dołączenie wewnętrzne”, nadana tego typu operacjom między dwiema tabelami. Zobaczymy, że istnieje również „dołączenie zewnętrzne”. W przypadku dołączenia wewnętrznego kolejność tabel w zapytaniu nie ma wpływu na wynik: FROM TA inner join TB jest równoważne FROM TB inner join TA.

Poprzednie zapytanie SQL umieszcza w tabeli wynikowej tylko te wiersze z tabeli TA, do których odwołuje się co najmniej jeden wiersz z tabeli TB. W związku z tym wiersz z tabeli TA o numerze [3, data3] nie pojawia się w wyniku, ponieważ nie jest on odwołany przez żaden wiersz z tabeli TB. Możemy chcieć uzyskać wszystkie wiersze z tabeli TA, niezależnie od tego, czy są one odwołane przez wiersz z tabeli TB. W takim przypadku stosuje się połączenie zewnętrzne między tymi dwiema tabelami:

SQL> select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA left outer join TB on TA.ID= TB.IDTA

Mamy tu do czynienia z lewym zewnętrznym połączeniem („left outer join”). Aby zrozumieć wyrażenie „FROM TA left outer join TB”, należy wyobrazić sobie połączenie z tabelą TA po lewej stronie i tabelą TB po prawej stronie. Wszystkie wiersze z lewej tabeli znajdują się w wyniku lewego zewnętrznego połączenia, nawet te, dla których relacja połączenia nie została zweryfikowana. Relacja ta niekoniecznie musi być ograniczeniem klucza obcego, choć jest to najczęstszy przypadek.

W następującej kolejności:

SQL> select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TB left outer join TA on TA.ID= TB.IDTA

to tabela TB znajduje się po „lewej stronie” w połączeniu zewnętrznym. W wyniku połączenia znajdą się zatem wszystkie wiersze z tabeli TB:

W przeciwieństwie do połączenia wewnętrznego kolejność tabel ma tu znaczenie. Istnieją również połączenia zewnętrzne typu „right”:

FROM TA left outer join TB jest równoważne FROM TB right outer join TA: tabela TA znajduje się po lewej stronie
FROM TB lewy zewnętrzny join z TA jest równoważny FROM TA prawy zewnętrzny join z TB: tabela TB znajduje się po lewej stronie

Znając już podstawy jednoczesnego przetwarzania wielu tabel, możemy przejść do bardziej złożonych operacji zapytań w bazach danych.