Blog

Powiązania tabel

Skoro mamy już wszystkie tabele, należy się zastanowić, w jaki sposób je powiązać, czyli utworzyć między nimi relacje. W głównej mierze tworzenie bazy danych polega na takim jej zaprojektowaniu, by można było w przyszłości łatwo z niej korzystać, w tym zmieniać nawet struktury poszczególnych tabel, nie wpływając na inne tabele bazy danych.

Musimy jakoś powiązać tabelę ksiazki z tabelami autor i wydawnictwo. Możemy wprowadzić do tabeli ksiazki nowe kolumny: autor_id i wydawnictwo_id, w których będziemy przechowywali id odpowiednich autorów i wydawnictw. Taka tabela byłaby w zasadzie bardzo podobna do naszej poprzedniej tabeli, z tą różnicą, że dane autorów i wydawnictw przechowywalibyśmy w innych tabelach. Jest to jakiś postęp – w tabelach autorów i wydawnictw moglibyśmy dodawać nowe dane (kolumny) dotyczące już istniejących rekordów lub całkiem nowe rekordy jeszcze nie wykorzystywane w tabeli książek. Takie podejście ma jedną wadę z naszego punktu widzenia – mianowicie w sytuacji kiedy daną książkę napisało kilku autorów. Każdy autor ma swoje własne id, a mielibyśmy możliwość przypisania książce tylko jedno id autora. Można wtedy powielić wszystkie dane książki i wpisać id innego autora. Właśnie tak:

Encyklopedię (przyjmijmy, że w naszym przypadku wszystkie 6 tomów są spięte w jedną książkę) zapewne tworzyło wielu ludzi, więc siłą rzeczy ma ona wielu autorów. Jak widać na powyższym rysunku, ma 3 autorów o id = 4, 2 i 11. Oczywiście, rok_wydania i ISBN będą się powtarzać, no ale trudno. Gdyby się okazało, że zrobiliśmy jakąś literówkę w tytule, musimy zmieniać we wszystkich pozycjach o tym samym tytule. To już umiemy, więc nie jest to jakiś problem. Gorzej, że możemy nie pamiętać dokładnie całego tytułu i za każdym razem wpisać nieco inny. Czyli musimy pamiętać, co wpisywaliśmy wcześniej. Co gorsza – ta sama pozycja ma różne id (2,5,6), co nie jest dobrą praktyką. Co będzie, kiedy w zbiorach będziemy mieli dwie identyczne książki? Wszystko będzie podwójnie, bowiem autorzy byliby ci sami. Nie będziemy też wiedzieli, ile w rzeczywistości mamy egzemplarzy danej książki. W poprzednich częściach mieliśmy taką możliwość, bo autorzy byli wpisani do tej samej kolumny (Adam Mickiewicz, Juliusz Słowacki, Henryk Sienkiewicz). W sumie – same wady takiego rozwiązania. To samo byłoby, gdybyśmy mieli kilka wydawnictw przypisanych do tej samej książki – powiedzmy, że byłaby współpraca dwóch wydawnictw wydających naszą encyklopedię. Wtedy mielibyśmy jeszcze więcej rekordów związanych z jedną pozycją – trzech autorów i dwa wydawnictwa – w sumie 6 rekordów związanych z jedną encyklopedią. Marnotrawstwo miejsca. Trzeba inaczej podejść do problemu.

Tak więc tabela ksiazki pozostaje bez zmian – nie dodajemy do niej kolumny id_autor. Musimy jednak jakoś książki powiązać zarówno z autorami, jak i wydawnictwami. Zrobimy to za pomocą dodatkowych tabel, które będą nam jedynie przechowywały relacje pomiędzy innymi tabelami. Pierwszą tabelą niech będzie tabela ksiazki_autor. Teraz się zorirntowałem, że tabela ksiazki jest w liczbie mnogiej, pozostałe są w pojedynczej. Powinienem zmienić nazwę tabeli na ksiazka, ale zostawmy już jak jest. Na przyszłość, jak coś będziemy tworzyli, to pamiętajmy jednak, by nazwy tabel były albo w liczbie pojedynczej, albo mnogiej. No dobra, zmieńmy nazwę tabeli ksiazki na ksiazka:

Po wciśnięciu przycisku „Wykonaj” otrzymujemy potwierdzenie wykonania operacji i przy okazji SQLowy odpowiednik tej operacji:

Teraz możemy kliknąć w menu „Przeglądaj” i będziemy mieli tą samą zawartość. Dobrze, wracamy do meritum.

No więc nową tabelę łącząca książki z autorami nazwiemy ksiazka_autor. Będzie zawierała tylko dwie kolumny: id_ksiazka i id_autor. Oczywiście samo id danego rekordu też będzie, ale to ma każda tabela niezależnie czego dotyczy.

Tworzymy tabelę:

Klikamy na „Wykonaj” i wypełniamy formularz:

Klikamy „Zapisz” i mamy nową tabelę. Teraz musimy się zastanowić jak ją wypełnić. Pytanie zasadnicze: czy każda książka musi mieć autora? Każdy powie, że „Oczywiście że tak!”. Otóż niekoniecznie, mogą się zdarzyć sytuacje, że autor jest nieznany. Co wtedy? Po prostu książka nie będzie miała przypisanego autora. Albo można stworzyć autora o nazwie „Autor nieznany” – to oczywiście w tabeli autor.

Tak samo tworzymy tabelę łączącą książki z wydawnictwami. Wszystko robimy identycznie, różnica będzie polegała tylko na nazwie jednej kolumny. Czytelnik powinien już samodzielnie utworzyć tą tabelę:

• ksiazka_wydawnictwo
• id: integer, klucz główny, autoinkrementacja
• id_ksiazka: integer
• id_wydawnictwo: integer

Popatrzmy sobie zbiorczo, jakie mamy tabele. Klikamy na górze na „Baza danych: biblioteka”. Potem w menu wybieramy „Widok projektu” (jeśli ekran jest zbyt wąski, trzeba najpierw wybrać „Więcej” i z podmenu wybrać „Widok projektu”. Poniżej rysunek:

Zobaczymy wszystkie tabele w postaci graficznej (poukładałem je żeby ładniej wyglądało) :

Przesuwanie tabel jest nieco kłopotliwe, przynajmniej u mnie nie zawsze działało, jak trzeba, ale ostatecznie udało się. W lewej kolumnie mamy główne tabelki, w prawej tabele łączące. Najpierw zajmiemy się tabelą ksiazka_autor.

Aha, dla dla późniejszego ułatwienia, zmieniłem kolejność kolumn imie oraz nazwisko. Jak to zrobić? Wybieramy jako aktywną tabelę autor i w menu wybieramy „Struktura”. Pod tabelą mamy do wybory parę działań z których wybieramy „Przenieś kolumny”. Klikamy na to i pokaże nam się okienko z kolumnami tabeli. Teraz klikamy myszką na kolumny i przeciągamy w górę lub dół. Zatwierdzamy potem całość klikając na „Wykonaj”:

Sprawa załatwiona, kolumny mamy w potrzebnej nam kolejności. Później zobaczymy, po co nam to było potrzebne.

Zajmijmy się teraz tabelą ksiazka_autor. Jak wprowadzać odpowiednie dane? No cóż, trzeba będzie znać dane z obu tabel i je odpowiednio wiązać. Dla ułatwienia na początek poniżej będzie zawartość obu tabel.

Zawartość obu tabel będzie nam potrzebna, żebyśmy wiedzieli, co z czym mamy połączyć. id nie będziemy wypełniać, zostanie nadane automatycznie. Zaczniemy od książek. Jak widzimy, mamy trzy encyklopedie. Rzeczywiście może tak być w bibliotece, że powtarzają się identyczne książki. Im przypiszemy autora zbiorowego. Reszta książek zgodnie z poniższą tabelką:

ksiazka_autor

Przyporządkowaliśmy książkom autorów. Oczywiście, gdyby jakaś książka miała więcej niż jednego autora, to trzeba byłoby dodać nowy rekord z id książki i przypisać jej kolejnego autora. Teraz kolej na wprowadzenie danych:

1. INSERT INTO ksiazka_autor (id_autor, id_ksiazka)
2. VALUES (2,3),(3,2),(1,3),(1,4),(3,5),(3,6);

Po wykonaniu powyższej instrukcji mamy takie dane:

Nic dziwnego, wszak sami je wpisaliśmy. Ale to już coś. Teraz możemy spokojnie sprawdzać zawartość naszej bazy używając trzech tabel : autor, ksiazka, ksiazka_autor.

Na początek chcemy wiedzieć, kto jest autorem każdej książki z osobna. Spróbujmy:

SELECT * FROM ksiazka, autor;

Coś dziwnego dostaliśmy:

Każdej książce został przyporządkowany każdy autor, wszystko w osobnych wierszach. Niefajnie, dalej nie wiemy, kto jest autorem jakiej książki. Mamy za to w nagłówku tabeli dwa razy id, każde z innej tabeli. W tej chwili wiemy, które id dotyczy której tabeli. No, ale, przecież obie tabele nie wiedzą o sobie niczego, same w sobie nie są połączone w żaden sposób. Dodajmy więc trzecią tabelę ksiazka_autor:

SELECT * FROM ksiazka, autor, ksiazka_autor;

Otrzymamy:

Powyżej jest tylko fragment całości, która zawiera 144 wiersze. Jest jeszcze gorzej. Każdej grupie z poprzedniego zapytania zostały przyporządkowane kolejne rekordy w tabeli ksiazka_autor. Trzecia tabela miała nam ułatwić życie, a utrudniła. W każdym bądź razie tak tylko nam się wydaje. Zmieńmy selecta:

1. SELECT * FROM ksiazka, autor, ksiazka_autor
2. WHERE ksiazka.id = ksiazka_autor.id_ksiazka AND autor.id = ksiazka_autor.id_autor;

W pierwszym punkcie mamy wymienione wszystkie potrzebne do użycia tabele, w drugim warunek, jaki powinien być spełniony, by wszystko nam pasowało. Oto wynik:

O wiele lepiej. W zasadzie to jest to co chcieliśmy uzyskać, to znaczy przyporządkowanie książek i autorów. Przy okazji widzimy, że w każdym wierszu id z tabeli ksiazka odpowiada id_ksiazka z tabeli ksiazka_autor. Tak samo id z tabeli autor odpowiada id_autor z tabeli ksiazka_autor. Zauważmy, że nie pojawia się id=1 z tabeli ksiazka. Dlaczego? Bo nie został tej książce przypisany żaden autor, a klauzula WHERE wyraźnie mówi nam, że chcemy tylko te książki, które mają przypisanego autor (czyli odpowiednie id z jednej tabeli muszą być identyczne z id_ksiazka lub id_autor z tabeli ksiazka_autor). Co jeszcze widzimy? Że sortowanie jest po id z tabeli ksiazka_autor. Może dlatego, że w części FROM daliśmy ją na końcu. Spróbujmy zmienić naszą instrukcję, zmieniając jedynie kolejność tabel:

Hm, dane z tabeli ksiazka_autor są teraz na początku. Ale kolejność sortowania się nie zmieniła. Coś znowu zmienimy:

1. SELECT * FROM ksiazka_autor, ksiazka, autor
2. WHERE ksiazka_autor.id_ksiazka=ksiazka.id AND ksiazka_autor.id_autor = autor.id;

Zmieniliśmy kolejność w klauzuli WHERE i otrzymamy:

Czyli o sortowaniu nie decyduje kolejność wpisania tabel w klauzuli FROM. Przyznam, że trochę zgłupiałem. Ale ok, jak chcemy mieć własne sortowanie, trzeba będzie to zaznaczyć wyraźnie na przykład po tytule książki:

1. SELECT * FROM ksiazka_autor, ksiazka, autor
2. WHERE ksiazka_autor.id_ksiazka=ksiazka.id AND ksiazka_autor.id_autor = autor.id
3. ORDER BY ksiazka.tytul;

I otrzymamy w wyniku:

Na zielonym pasku mamy informację, że posortowane wyniki mamy od „Encyklopedia PWN….” do „Tożsamość Bournea”. Dodatkowo w samej tabelce mamy zaznaczone przy nazwie kolumny tytul trójkącik wskazujący sortowanie po tej kolumnie. Możemy na niego kliknąć i będziemy mieli wszystkie wyniki posortowane w odwrotnej kolejności. Co więcej – klikając na nazwę jakiejkolwiek kolumny – sprawimy, że dane zostaną posortowane według tej kolumny rosnąco, a powtórne kliknięcie na nią spowoduje sortowanie malejące.

Tabela ksiazka_autor nie jest nam w zasadzie potrzebna do wyświetlania, sama w sonie nie przynosi nam żadnej informacji. Spróbujmy więc się bez niej obejść – wyświetlimy tylko dane z tabeli ksiazka i tabeli autor:

1. SELECT * FROM ksiazka, autor
2. WHERE ksiazka_autor.id_ksiazka=ksiazka.id AND ksiazka_autor.id_autor = autor.id
3. ORDER BY ksiazka.tytul;

Wynik:

No cóż, nigdzie nie została wprowadzona tabela ksiazka_autor, poza klauzulą WHERE. Przywróćmy ją zatem, ale zmieńmy naszego SELECTa:

1. SELECT ksiazka.*, autor.nazwisko, autor.imie
2. FROM ksiazka_autor,ksiazka, autor
3. WHERE ksiazka_autor.id_ksiazka=ksiazka.id AND ksiazka_autor.id_autor = autor.id ORDER BY ksiazka.tytul;

Zobaczymy:

Mamy to co chcieliśmy. No, prawie.

Nie wiem, czy któryś z Czytelników wyłapał błąd przypisania. Otóż instrukcja INSERT zawiera błąd – źle przepisałem liczby z tabelki nad tą instrukcją. Mea culpa. Nie będę już poprawiał, w każdym bądź razie przez ten błąd mamy złe przypisanie książce „Tożsamość Bournea” dwóch autorów (Mariano Iglesias nie jest współautorem), natomiast książka „Cake PHP 1.3” nie ma przypisanego autora (którym właśnie jest Mariano Iglesias). Przez ten błąd udało się pokazać, jak jedna książka może mieć dwóch autorów. Po wtóre mamy książkę, która nie ma przypisanego autora.

Co w związku z tym? W powyższej instrukcji praktycznie nie jesteśmy wychwycić braku autora jakiejś książki. Stąd nie dowiemy się, że jakaś książka jest na stanie, ale nie przypisano jej autora, wiec nam się nie wyświetliła. Coś z tym trzeba będzie zrobić.

Spróbujemy inaczej. Chcemy mieć wszystkie książki, łącznie z tymi, które nie mają przypisanego autora. Na chwilę obecną najprościej zrobić to ostatnie, czyli uzyskać książki bez przypisanego autora:

1. SELECT DISTINCT ksiazka.*
2. FROM ksiazka, ksiazka_autor
3. WHERE ksiazka.id NOT IN (SELECT id_ksiazka FROM ksiazka_autor)
4. ORDER BY ksiazka.tytul;

Klauzula DISTINCT jest po to, żeby się wyniki nie powtarzały. Bez niej mielibyśmy 6 identycznych rekordów. Otrzymamy:

Jak dotąd mamy tylko jedną taką książkę. Pytanie, jak dołączyć powyższą pozycję do pozostałych z poprzedniego rysunku, gdzie mieliśmy podanych też autorów? Chodzi o to, żeby w przypadku tej książki w kolumnach dotyczących autorów uzyskać wartości NULL.

JOIN

JOIN oznacza dołącz. W naszym przypadku dołącz tabelę, ale nie byle jak, tylko spełniając określony warunek. W zasadzie są trzy rodzaje JOIN – INNER JOIN (czyli domyślnie, gdy używamy tylko JOIN), LEFT JOIN (lewe dołączenie) oraz RIGHT JOIN (prawe dołączenie). Jest jeszcze FULL JOIN, ale nie wspierany przez MySQL (przynajmniej w wersji 5.7). O co chodzi z tym dołączaniem tabel? Jest to coś w rodzaju operacji na zbiorach, w naszym przypadku zbiorach tabel. Jeżeli mamy dwie tabele t1 oraz t2 (nieważne czego dotyczą) to:

JOIN lub INNER JOIN:

Tabelę t2 dołączamy do tabeli t1 następującą składnią

1. SELECT *
2. FROM t1
3. JOIN t2 ON t1.id = t2.id;

Tabela t1 jest główną tabelą i dołączamy do niej tabelę t2. Do tego potrzebny jest jeszcze warunek łączenia – to ta część za klauzulą ON. Można powiedzieć, że w tym przypadku ON ma takie znaczenie jak WHERE. Mamy dwie tabele t1 i t2. Obie tabele są równorzędne. W powyższym warunku wyświetlone zostaną tylko te rekordy obu tabel, które mają takie samo id w obu tabelach. W przypadku naszych książek instrukcja miałaby postać:

1. SELECT *
2. FROM ksiazka
3. JOIN ksiazka_autor ON ksiazka_autor.id_ksiazka = ksiazka.id;

Czyli mielibyśmy tylko te książki, które mają przypisanego autora. No, a jaki to autor? Musimy rozszerzyć nasze zapytanie i dodać do niego tabelę autor.

1. SELECT *
2. FROM ksiazka
3. JOIN ksiazka_autor ON ksiazka_autor.id_ksiazka = ksiazka.id
4. JOIN autor ON ksiazka_autor.id_autor = autor.id;

W wyniku powyższej instrukcji otrzymamy:

Jak widać z powyższego rysunku, mamy teraz dane wszystkich książek łącznie z autorami. Ale znów – tylko te, które mają przypisanego autora w tabeli ksiazka_autor. Zauważmy też, że dane z tabeli ksiazka_autor są w środkowej części tabeli wynikowej.Chcielibyśmy mieć teraz wszystkie książki, łącznie z tymi, które nie mają przypisanego autora.

LEFT JOIN:

LEFT JOIN jest innym rodzajem łączenia tabel. Tak, jak powyżej, mamy dwie tabele t1 i t2 i wykonajmy instrukcję

1. SELECT *
2. FROM t1
3. LEFT JOIN t2 ON t1.id = t2.id;

Powyższa instrukcja także dołącza tabelę t2, ale już na innej zasadzie niż poprzednio. Lewe łączenie (LEFT JOIN) oznacza, że tabela t1 jest lewą tabelą, zaś tabela t2 – prawą. Łączenie tego typu można streścić następująco – bierzemy wszystkie elementy z tabeli t1 i dopasowujemy do niej te elementy t2, które spełniają warunek po klauzuli ON, ale UWAGA! Elementy z tabeli t1, które nie mają swojego odpowiednika w tabeli t2 też będą brane pod uwagę i będą miały w wynikowej tabeli wpisane puste wartości w kolumnach z tabeli t2. Gdybyśmy mówili o tabelach t1 i t2 jako zbiorach – powiedzielibyśmy, że bierzemy pod uwagę cały zbiór t1 oraz część wspólną zbiorów t1 i t2.

Najprościej pokazać to na konkretnym przykładzie. Teraz weźmy tą samą instrukcję z dwiema tabelami, jaką mieliśmy wcześniej, ale dodajmy LEFT do niej:

1. SELECT *
2. FROM ksiazka
3. LEFT JOIN ksiazka_autor ON ksiazka_autor.id_ksiazka = ksiazka.id;

I popatrzmy teraz na rysunek:

Na powyższym rysunku widzimy ten sam rezultat co poprzednio z jednym dodatkiem – pokazała się na samym dole książka, która nie ma przypisanego autora. Zatem – pierwsze 6 wierszy stanowi część wspólną tabel ksiazka oraz ksiazka_autor, natomiast ostatni wiersz nie ma swojego odpowiednika w tabeli ksiazka_autor. Trzy ostatnie kolumny dla tego wiersza mają wartość NULL.

Chcemy mieć jednak autorów pierwszych 6 pozycji. Musimy więc dodać w naszej instrukcji tabelę autor. Oczywiście, za pomocą następnego JOIN. Będziemy sprawdzali też, co będzie, kiedy użyjemy samego JOIN i kiedy użyjemy LEFT JOIN.

1. SELECT *
2. FROM ksiazka
3. LEFT JOIN ksiazka_autor ON ksiazka_autor.id_ksiazka = ksiazka.id
4. JOIN autor ON ksiazka_autor.id_autor = autor.id;

Oto wynik:

Widzimy, że znowu mamy to, co mieliśmy wcześniej. Dodatkowy JOIN zmienił nam całkowicie sens naszej instrukcji. Sprawił, że została wybrana część wspólna wszystkich tabel. Zmieńmy teraz JOIN na LEFT JOIN:

1. SELECT *
2. FROM ksiazka
3. LEFT JOIN ksiazka_autor ON ksiazka_autor.id_ksiazka = ksiazka.id
4. LEFT JOIN autor ON ksiazka_autor.id_autor = autor.id;

Nareszcie mamy to co chcieliśmy. Oczywiście, można pominąć wyświetlenie danych z tabeli ksiazka_autor, będzie wtedy wyglądało ładniej:

1. SELECT ksiazka.*, autor.*
2. FROM ksiazka
3. LEFT JOIN ksiazka_autor ON ksiazka_autor.id_ksiazka = ksiazka.id
4. LEFT JOIN autor ON ksiazka_autor.id_autor = autor.id;

Wynik powyższej operacji:

O wiele lepiej. Kolumnę id z tabeli autor (to ta piąta kolumna) można sobie darować, kolumnę o_autorze także – nie wnoszą na razie żadnych informacji dla nas, ponieważ są puste. Zatem zmieńmy naszą instrukcje w następujący sposób:

1. SELECT ksiazka.tytul, autor.imie, autor.nazwisko
2. FROM ksiazka
3. LEFT JOIN ksiazka_autor ON ksiazka_autor.id_ksiazka = ksiazka.id
4. LEFT JOIN autor ON ksiazka_autor.id_autor = autor.id;

W rezultacie otrzymamy:

Wygląda o wiele lepiej, mamy wszystko, co byśmy chcieli.

RIGHT JOIN

RIGHT JOIN działa tak samo jak LEFT JOIN, tylko, że odwrotnie. Tak jak poprzednio, mamy dwie tabele t1 i t2. W dalszym ciągu tabela t1 jest lewą, tabela t2 – prawą. RIGHT JOIN spowoduje, że to wszystkie dane z tabeli t2 będą wyświetlone, zaś z tabeli t1 – tylko te rekordy, które są wspólne dla obu tabel. Od razu przejdźmy do naszych własnych tabel – od razu weźmiemy pod uwagę wszystkie trzy. Pamiętajmy, że w tabeli autor mamy Marcina Lisa, któremu nie jest przypisana żadna książka.

1. SELECT * FROM ksiazka
2. LEFT JOIN ksiazka_autor ON ksiazka_autor.id_ksiazka = ksiazka.id
3. RIGHT JOIN autor ON ksiazka_autor.id_autor = autor.id;

Widzimy teraz, że w ostatnim wierszu pojawił się autor, któremu żadna książka nie jest przypisana. Równocześnie, mimo zastosowania LEFT JOIN ksiazka_autor, nie mamy książki do której nie przypisano żadnego autora. A przecież mieliśmy wcześniej!. Owszem, ale wtedy tabelę autor dołączaliśmy z pomocą LEFT JOIN, czyli można powiedzieć – pierwszeństwo mają dane z lewej tabeli. Zmiana LEFT na RIGHT zmieniła nam rezultat. Nic nam nie pomoże zmiana kolejności zastosowania JOINów. Gdybyśmy najpierw dołączyli tabelę autor, a potem ksiazka_autor, to byłby błąd zapytania, gdyż wtedy w JOINie z tabelą autor nieznana byłaby tabela ksiazka_autor. Wszystkie tabele w momencie odwołania do nich (czyli za klauzulą ON) muszą być znane wcześniej. Czyli muszą być wcześniej dołączone.

FULL JOIN

W MySQL nie ma czegoś takiego jak FULL JOIN (pełne dołączenie). Przydałoby się w tym momencie, ale niestety, nie mamy możliwości użyć instrukcji której nie zaimplementowano. Ale od czego Google? Na szczęście znalazłem na forach odpowiedź jak samemu stworzyć coś w rodzaju FULL JOIN. Trzeba mianowicie użyć operatora UNION. I tu się na chwilę zatrzymajmy. Operator UNION pozwala łączyć kilka zapytać SELECT, ale są pewne zasady. Kolumny w każdym z tych SELECTów muszą być w tej samej ilości, kolejności i tego samego typu – nazwa kolumn wyświetlonych będzie taka, jak w pierwszym SELECTcie (czy jak się tam to odmienia). Przykład:

Powiedzmy, że mamy dwie tabele: nauczyciel oraz uczen. W każdej z nich będą jakieś kolumny opisujące nauczycieli i uczniów, nie będą takie same – tabela dotycząca nauczyciela może zawierać dane takie jak: wykładany_przedmiot, staż_pracy, awanse_zawodowe i tak dalej – typowo belferskie dane. Tabela ucznia może zawierać dane takie jak: olimpiady, nagany, pochwały – też takie typowo uczniowskie dane (nie piszę o ocenach, bo do tego potrzebna by była oddzielna tabela, albo i kilka tabel – nie będziemy wchodzili w szczegóły takiej bazy danych). W każdym bądź razie dwie kolumny w obu tabelach będą identyczne – pod względem typu – dotyczące imienia i nazwiska. Pomijając inne dane, obie tabele wyglądałyby tak:

Widzimy, że kolumny dotyczące imienia i nazwiska są identyczne co do typu (może się okazać, że typy nie są aż tak identyczne, ale mogą być podobne i wtedy też zapytanie może dać poprawne wyniki – czytelnik może się potem na własną rękę pobawić operatorem UNION i sprawdzić, jak działa, my może jeszcze kiedyś do niego wrócimy). Teraz pytanie brzmi – jak wyświetlić za jednym zamachem imiona i nazwiska uczniów i nauczycieli w jednej tabeli wynikowej. Oczywiście nie będziemy wiedzieli, które dane kogo dotyczą, ale zawsze można temu zaradzić dodając do każdej tabeli na przykład kolumnę status (u- uczeń, n – nauczyciel). Teraz dzięki UNION możemy dać takie zapytanie:

1. SELECT imie_belfra as imię, nazwisko_belfra as nazwisko, status FROM nauczyciel
2. UNION
3. SELECT imie_ucznia, nazwisko_ucznia, status FROM uczen;

Takie zapytanie powinno nam dać tego typu rezultat:

Myślę, że powyższa tabela obrazuje nam jak działa operator UNION – najpierw wyświetla nam dane z pierwszego zapytania SELECT, a pod nim z drugiego. Pewnie dałoby się jeszcze posortować te dane, ale chwilowo się tym nie zajmujemy. Chodzi o pokazanie ogólnej zasady. Aha, ważna rzecz – UNION powoduje, że nie wyświetlą się duplikaty rekordów. Gdyby było dwóch uczniów Jan Nowak – pokazałby nam się tylko jeden rekord – mylące by to było, ale można temu zaradzić używając UNION ALL – pokażą nam się wszystkie wyniki z obu tabel, także powtarzające się.

Wracamy do naszej biblioteki. Po co nam ten operator UNION? Z jego pomocą możemy zrealizować pełne dołączenie – coś w rodzaju FULL JOIN. W jaki sposób? Będziemy mieli dwa zapytania SELECT połączone operatorem UNION. Pierwsze zapytanie ma nam pokazać książki, także te, które nie mają przypisanego autora. Drugie zapytanie ma nam podać wszystkich autorów, także tych, którzy nie mają jeszcze przypisanej żadnej książki. Jak to będzie wyglądało? Jak poniżej:

1. SELECT * FROM ksiazka
2. LEFT JOIN ksiazka_autor ON ksiazka_autor.id_ksiazka = ksiazka.id
3. LEFT JOIN autor ON ksiazka_autor.id_autor = autor.id
4. UNION
5. SELECT * FROM ksiazka
6. LEFT JOIN ksiazka_autor ON ksiazka_autor.id_ksiazka = ksiazka.id
7. RIGHT JOIN autor ON ksiazka_autor.id_autor = autor.id;

Sprawdźmy jak to działa:

Otrzymaliśmy dane, które chcieliśmy – i książkę bez autora i autora bez książki – że to tak skrótowo ujmę. Czyli – cel osiągnięty.

SPÓJNOŚĆ DANYCH

Mamy tabele powiązane ze sobą, tak jak planowaliśmy na początku. Póki używamy tylko SELECTa, wszystko gra. Z INSERTem jest troszkę inna sprawa – musimy nowe dane wprowadzać nie tylko do tabel ksiazka i autor, ale też do tabel ksiazka_autor i ksiazka_wydawnictwo. Jak o tych ostatnich zapomnimy, to klops – nie będziemy wiedzieli, kto napisał to co napisał oraz kto wydał to co wydał. Tak samo z UPDATem musimy uważać. Wszystkie tabele muszą być spójne. Jednak jeżeli tabele wiążące (ksiazka_autor, ksiazka_wydawnictwo) nie będą wiązały jakichś rekordów z odpowiednich tabel, to nic. W końcu taką sytuację mamy na ostatnim rysunku – książka jest bez autora (nie ma jej w tabeli ksiazka_autor) oraz autor nie ma przypisanej książki (również i jego nie ma w tabeli ksiazka_autor). Inaczej jest, kiedy tabela wiążąca ma dane już nieistniejące w którejś z tabel ksiazka lub autor. Czy to w ogóle możliwe? No bo jak można wiązać ze sobą rekordy, których nie ma? No, można. Po prostu, kasując (instrukcja DELETE) jakiś rekord w tabeli ksiazka lub autor, ale zostawiając powiązanie w tabeli ksiazka_autor właśnie uzyskujemy taką sytuację. W tabeli ksiazka_autor mamy wpisane id rekordów, które już nie istnieją, bo zostały skasowane! A jak jeszcze zapomnimy usunąć je z tej tabeli, to… No, zobaczmy sami. Usuńmy jedną z encyklopedii w tabeli ksiazka, na przykład o id=5. Do niej został przypisany autor o id=3 czyli „Praca zbiorowa”. Wykonujemy instrukcję:

DELETE FROM ksiazka WHERE id=5;

Usunęliśmy rekord z id = 5 i zostaje nam:

Nie mamy rekordu z id=5. A co mamy w tabeli ksiazka_autor? Spójrzmy:

I co teraz? Spróbujmy teraz wykonać ostatnią instrukcję typu FULL JOIN (choć takiej nie ma przecież w MySQL), tylko nieco zmodyfikowaną (w drugim SELECT mamy RIGHT JOIN ksiazka_autor oraz ponazywałem wszystkie kolumny zawierające kolumny id z poszczególnych tabel – w podzapytaniu nie mogą występować identyczne nazwy kolumn, dodałem też kolumnę „L.p.” – to zmienna pokazująca numer kolejnego wyświetlanego wiersza – @lp):

1. SET @lp = 0;
2. SELECT @lp:=@lp+1 as "L.p.", t.* FROM
3. (
4. SELECT ksiazka.id as ksiazka_id,tytul,rok_wydania,ISBN,ksiazka_autor.id as ksiazka_autor_id, id_autor, id_ksiazka,autor.id as autor_id, nazwisko, imie, o_autorze FROM ksiazka
5. LEFT JOIN ksiazka_autor ON ksiazka_autor.id_ksiazka = ksiazka.id
6. LEFT JOIN autor ON ksiazka_autor.id_autor = autor.id
7. UNION
8. SELECT ksiazka.id as ksiazka_id,tytul,rok_wydania,ISBN,ksiazka_autor.id as ksiazka_autor_id, id_autor, id_ksiazka,autor.id as autor_id, nazwisko, imie, o_autorze FROM ksiazka
9. Right JOIN ksiazka_autor ON ksiazka_autor.id_ksiazka = ksiazka.id
10. RIGHT JOIN autor ON ksiazka_autor.id_autor = autor.id
11. ) t;

I mamy:

Popatrzmy na wiersz 7. Na czerwono podkreśliłem dane z tabeli ksiazka_autor, które powiązały autora z id=3 do nieistniejącej książki – dane książki pokazują NULL. Niby nie jest źle, ale tak być nie powinno. Co będzie, kiedy zechcemy mieć wszystkie książki autora „Praca zbiorowa”? Pokażą nam się 2 encyklopedie i same NULL. Nie wygląda to dobrze:

1. SELECT autor.*, ksiazka.* FROM autor
2. LEFT JOIN ksiazka_autor ON ksiazka_autor.id_autor = autor.id
3. LEFT JOIN ksiazka ON ksiazka_autor.id_ksiazka = ksiazka.id
4. WHERE autor.nazwisko LIKE "Praca zbiorowa";

I otrzymujemy w wyniku:

Jeden rekord jeszcze jakoś ujdzie, gorzej, jak się okaże, że usunęliśmy więcej książek, zapominając usunąć dane z tabeli łączącej książki z autorami lub wydawnictwami. A jeżeli książka miała wielu autorów, to w tabeli ksiazka_autor zostanie wiele rekordów z autorami przypisanymi do nieistniejącej już książki.

Inna sprawa – książki bez autorów lub autorzy bez książek. Oczywiście, można też dodać dodatkowy rekord w tabeli autor – „Nieznany autor” lub też po prostu „Brak autora” lub coś w tym stylu i przypisać do książki. Wtedy nie będzie problemu z książkami, które nie mają w tabeli ksiazka_autor żadnego przypisanego autora. Wszystkie książki będą miały jakiegoś autora, nawet nieznanego. Z drugiej strony tak samo można w tabeli ksiazka dopisać książkę „Nieznany tytuł” i w tabeli ksiazka_autor przypisać autorom tą pozycję. Dzięki temu wszyscy autorzy i wszystkie książki będą miały odpowiedniki w tabeli ksiazka_autor co ułatwi nam potem przeglądanie danych. Oczywiście, jeśli będzie dużo takich książek lub autorów, zwiększy nam to zawartość tabeli ksiazka_autor. W jaki sposób tworzyć bazę danych – czy z pełnym przypisaniem danych na zasadzie jak powyżej opisałem, czy też tak jak wcześniej robiliśmy – to już zależy wyłącznie od projektującego bazę danych. Ważne jest jedynie żeby zachować spójność danych.

Czyli kasując cokolwiek należy pamiętać o spójności tabel. Nie zostawiamy rekordów, które już nie mają powiązań z innymi tabelami (dotyczy to tabel wiążących inne tabele). Jak się przed tym ustrzec?

KLUCZE OBCE I OGRANICZENIA

Trzeba coś zrobić, żeby ustrzec się przed tego typu błędami. I jest na to sposób. Należy zastosować klucze obce i ograniczenia dotyczące zmian przeprowadzanych na tabelach. Co to oznacza? To, że SQL sam będzie pilnować spójności danych i nie pozwoli nam zapomnieć, że nie przypilnowaliśmy spójności danych – zamiast wykonać jakąś instrukcję pokaże nam błąd.

Na czym to polega? Powróćmy do momentu, kiedy usuwaliśmy w tabeli ksiazka pozycję o id=5. Mimo, iż w tabeli ksiazka_utor był rekord z tym samym id – czyli id_ksiazka = 5, mogliśmy ją usunąć z tabeli ksiazka. A nie powinniśmy mieć takiej możliwości. Najpierw powinniśmy usunąć z tabeli ksiazka_autor rekord o id=5 (id tabeli ksiazka_autor, nie piszę teraz o id_ksiazka które jest tożsame z id z tabeli ksiazka, zresztą proszę spojrzeć na przedostatni rysunek z czerwonym podkreśleniem – akurat przypadkowo id rekordu w tabeli ksiazka_autor ma tą samą wartość, co id_ksiazka), a dopiero potem usunąć w tabeli ksiazka pozycję o id = 5. Wtedy mielibyśmy utrzymaną spójność danych w tabeli ksiazka_autor – nie byłoby pozycji, w której id_ksiazka nie miałoby swojego odpowiednika w tabeli ksiazka. Ale znając życie, często z automatu w pierwszej kolejności zajęlibyśmy się usunięciem książki, której już nie mamy, a potem zapomnielibyśmy usunąć odpowiednią pozycję w tabeli ksiazka_autor.

Z pomocą przychodzą nam tak zwane klucze obce. Klucz obcy w tabeli to taka kolumna, która ma jawne przypisanie kolumny w innej tabeli. Czyli w tabeli ksiazka_autor kolumna id_ksiazka byłaby kluczem obcym powiązanym z id z tabeli ksiazka (czyli ksiazka.id). Tak samo kolumna id_autor byłaby kluczem obcym powiązanym z id z tabeli autor (czyli z autor.id).

Zanim cokolwiek dalej zrobimy, skasujmy wszystkie dane w tabeli ksiazka_autor. Kasujemy zawartość (TRUNCATE) i przeglądamy zawartość, by sprawdzić, czy rzeczywiście tabela jest pusta:

Tabela jest pusta. Możemy jeszcze sprawdzić autoincrement (po wyborze menu „Struktura” należy stronę przewinąć na sam dół):

Autoincrement jest ustawione na 1, więc następna pozycja w tej tabeli będzie miała id=1;

Pozostaniemy jeszcze na tej stronie. Przewijamy stronę do góry i patrzymy na poszczególne kolumny. Widać, że kolumna id jest kluczem głównym, pozostałe nie są w żaden sposób indeksowane, co widzimy też w części „Indeksy”. Na razie indeksami się nie zajmujemy. Wystarczy powiedzieć, że indeksy przyspieszają wyszukiwanie danych po kolumnach indeksowanych. Indeksem jest klucz główny. Jest nim też klucz obcy. O ile klucz główny zawsze jest jeden, to kluczy obcych może być wiele. W naszej tabeli ksiazka_autor będą dwa klucze obce. Będą one łączyły odpowiednie kolumny tabeli ksiazka_autor z odpowiadającymi im kolumnami tabel ksiazka i autor.

Jak to zrobić? Po co, zobaczymy później.

W menu „Struktura” klikamy na „Widok relacyjny”, ukaże nam się okienko jak poniżej:

Na powyższym rysunku widzimy na razie puste pola. Musimy je wypełnić, ale po kolei.

• „Ograniczenie nazwy” zostawimy puste.
• Kolumna – wybieramy kolumnę z naszej tabeli, czyli id_autor (później to samo zrobimy z id_ksiazka)
• Baza danych – tu po prostu biblioteka.
• Tabela – tabela autor
• Kolumna – to kolumna id w tabeli autor
• On delete i On update pozostawiamy na Restrict.

Po wypełnieniu nasze dane będą wyglądać tak:

Klikamy na „Zapisz” i otrzymamy:

Mamy utworzone pierwsze powiązanie – tabeli ksiazka_autor z tabelą autor. Nazywamy to ograniczeniem, ponieważ od tej chwili zostało nałożone pewne ograniczenie w tabeli autor – nie usuniemy jakiegoś autora, jeśli jest on powiązany z tabelą ksiazka_autor. Najpierw musielibyśmy usunąć odpowiedni rekord w tabeli ksiazka_autor, a dopiero potem możemy usunąć odpowiedni rekord w tabeli autor. Zauważmy, że nazwa ograniczenia została nadana automatycznie – „ksiazka_autor_ibfk_1”. Oczywiście, można to jeszcze samemu zmienić i nazwać sobie dowolnie, ważne jednak by w nazwie było jasno pokazane, których tabel dotyczy ograniczenie, czy jak ja to mówię – powiązanie.

Teraz zróbmy to samo dla kolumny id_ksiazka. Wszystko pokazuje poniższy rysunek, oczywiście, już po zapisaniu nowego powiązania, tym razem tabel ksiazka_autor i ksiazka (po wypełnieniu drugiego wiersza podkreślonego na zielono i kliknięciu przycisku „Zapisz”) :

Mamy już wprowadzone dwa ograniczenia, czyli powiązania pomiędzy odpowiednimi tabelami. Innymi słowy – ustaliliśmy pomiędzy tymi tabelami relacje. Oczywiście, wcześniej też niejako mieliśmy te relacje, ale tylko w naszym umyśle, sami pilnowaliśmy, by odpowiednie dane z jednej tabeli były powiązane z odpowiednimi danymi z innej tabeli. Teraz to sama baza danych będzie za nas pilnować, by wszystko grało.

Na samej górze na ciemnoszarym pasku kliknijmy na nazwę bazy danych biblioteka. Zobaczymy zawartość bazy danych biblioteka – wszystkie jej tabele.

I teraz w menu wybieramy „Widok projektu”. Zobaczymy relacje pomiędzy tabelami (tabelki zostały przeze mnie odpowiednio rozmieszczone – z automatu są one poukładane przypadkowo):

Widzimy powiązania pomiędzy poszczególnymi tabelami. Brakuje nam jeszcze powiązania tabeli ksiazka_wydawnictwo z tabelami ksiazka i wydawnictwo.

Na samej górze kliknijmy na „Baza danych: biblioteka”, w nowym oknie na tabelę ksiazka_wydawnictwo, z menu wybierzemy „Struktura”, potem klikamy na „Widok relacyjny” i stwórzmy odpowiednie ograniczenia, zupełnie tak samo, jak robiliśmy wcześniej. Ostatecznie uzyskamy poniższe ograniczenia:

Teraz przejdźmy z powrotem do widoku projektu. Wybieramy najpierw bazę danych biblioteka, po czym z menu wybieramy „widok projektu”. I mamy teraz:

Widzimy teraz jak powiązane są ze sobą poszczególne tabele – które kolumny jednej tabeli z którymi kolumnami innej tabeli. Rzut oka i wiemy co z czym jest powiązane i czy czegoś nie przegapiliśmy.

Mamy pustą tabelę ksiazka_autor. Możemy teraz ja uzupełnić. Dane wpisujemy tak, jak dotychczas – wybieramy tabelę ksiazka_autor, a potem z menu „Wstaw”. I przechodzimy do okienka wstawiania, jak na poniższym rysunku:

Chcemy wstawić teraz id autora. Klikając na wartość tego pola (czy tam kolumny), pokaże nam się lista rozwijalna z możliwymi autorami do wyboru. Powiedzmy, że wybierzemy Ludluma (id=1 w tabeli autor), tak samo będziemy mieli do wyboru książki:

Jest to jakieś ułatwienie, kiedy nie pamiętamy, jakie mamy dane w której z tabel. Niestety, nie ma możliwości wpisywania części na przykład nazwiska, by lista skróciła się tylko do nazwisk zawierających wprowadzony tekst. A byłoby fajnie mieć taką możliwość. Być może pod tym względem kiedyś phpMyAdmin zostanie jeszcze udoskonalone. Wracając do tematu. Z listy książek wybierzmy sobie „Tożsamość Bournea” – id=3. Pamiętajmy, że cokolwiek wybierzemy, jako wartość wpisane zostanie odpowiednie id, a nie nazwisko autora lub tytuł książki. Po wciśnięciu przycisku „wykonaj” pokaże nam się okienko:

Podkreślona instrukcja INSERT INTO pokazuje, że wprowadzone zostały odpowiednie id do tabeli ksiazka_autor. Sprawdzimy zawartość tej tabeli klikając na menu „Przeglądaj” (podkreślone na zielono):

Mamy już pierwszy wpis. Pouzupełniajmy resztę danych – menu „Wstaw”. Powstawiamy od razu więcej rekordów. Poniżej tylko pokażę instrukcję, z pomocą której zostały wstawione nowe rekordy:

I do skopiowania ta sama instrukcja (jakby się komuś nie chciało wpisywać samemu danych):

1. INSERT INTO ksiazka_autor (id, id_autor, id_ksiazka)
2. VALUES (NULL, '3', '2'), (NULL, '3', '5'), (NULL, '3', '6'), (NULL, '1', '4');

I przegląd powtórny tabeli:

Na tą chwilę powiązaliśmy wszystkie możliwe książki i autorów. Z autorami, którzy nie mają przypisanych książek, oraz książkami bez przypisanych autorów będziemy mieli ten sam problem, co dotychczas – trzeba będzie ich szukać z pomocą odpowiednich instrukcji. Jest też jeszcze jedno wyjście, o którym nie wspomniałem – utworzenie tabel „autorów bez książek” oraz „książek bez autorów”. Innymi słowy – w dotychczasowych tabelach autor oraz ksiazka mielibyśmy tylko te pozycje, które są powiązane z pomocą tabeli ksiazka_autor. W nowych, nazwijmy je autor_bk (autor bez ksiażki) oraz ksiazka_ba (ksiażka bez autora) byłyby tylko te pozycje, których nie byłoby w tabeli ksiazka_autor. Musiałaby by być jeszcze tabela ksiazka_bw (książka bez wydawnictwa) – a ponieważ mogłoby się zdarzyć, że wiele książek bez autora nie miałaby także żadnego wydawnictwa, tabele ksiazka_ba i ksiazka_bw mogłyby mieć zdublowane dane. Niepotrzebne nam to by było. Za to można by stworzyć jedną tabelę ksiazka_baw (książka bez autora lub wydawnictwa) i w niej zaznaczać czego brakuje (w osobnych kolumnach). Pomyślimy jeszcze czy to ma sens. Ale jeżeli miałbym wybierać, to myślę, że większy sens byłby, gdybyśmy mieli w tabeli autor rekord „Autor nieznany” (lub po prostu „Brak”) a w tabeli ksiazka rekord „Tytuł nieznany” (lub „Brak”), tak samo w tabeli wydawnictwo mielibyśmy rekord „Wydawnictwo nieznane” (lub „Brak”) i wszystkie pozycje byłyby odpowiednio powiązane w tabelach łączacych (jak ja nazywam tabele ksiazka_autor i ksiazka_wydawnictwo). Nie byłoby też kłopotów z wyszukiwaniem potrzebnych nam pozycji. Gdybyśmy mieli wszędzie rekord „Brak”, wystarczyłoby uruchomić odpowiednią instrukcję SELECT i wyszukać tych pozycji które mają przypisany „Brak” w jakiejkolwiek innej tabeli. I szybko można by potem te braki uzupełnić aktualizując dane w tabelach łączących (wiążących) inne tabele. Najprościej by było, gdyby w każdej tabeli głównej ów rekord był pierwszy. W każdej id=1 miałby wpisany do odpowiedniej kolumny „Brak”. Wróćmy do tematu.

Po co właściwie zrobiliśmy te powiązania? Na chwilę obecną wydaje się, że nic się nie zmieniło. No, oprócz tego, że teraz w tabeli ksiazka_autor łatwiej robić wpisy, bo pojawia się lista pasujących danych z tabel głównych. Poza tym, nic.

Wstawmy jedną pozycję do tabeli ksiazka:

1. INSERT INTO ksiazka (id, tytul, rok_wydania, ISBN)
2. VALUES (NULL, 'Napisało mnie kilku autorów', '2022', '98-69-65-23');

Powyższy kod kopiujemy (zaznaczamy, klikamy CTRL+C, potem w okienku SQL w phpMyAdmin klikamy myszką ustawiając kursow, po czym wklejamy wciskając CTRL+V, to tak dla przypomnienia).

Z kolei do tabeli autor dodamy dwóch autorów:

1. INSERT INTO autor (id, nazwisko, imie, o_autorze)
2. VALUES (NULL, 'Nazwisko 1', 'Imię 1', NULL), (NULL, 'Nazwisko 2', 'Imię 2', NULL);

Teraz uzupełniamy tabelę ksiazka_autor:

Klikamy na przycisk „Wykonaj” (podkreślony na zielono) i do tabeli zostaną wprowadzone dane. Jak widać – jednej książce przypisano dwóch autorów. Sprawdźmy:

SELECT * FROM ksiazka_autor;

Jak widzimy – jednej książce (id_ksiazka=7) przypisano dwóch autorów (id_autor = 6 i id_autor = 5). Na razie nie ma nic ciekawego. To już wcześniej robiliśmy. Teraz spróbujmy usunąć książkę, którą właśnie wprowadziliśmy:

Po wpisaniu instrukcji SQL (podkreślonej na czerwono)

DELETE FROM ksiazka WHERE id = 7;

wciśnięciu przycisku „Wykonaj” (podkreślony na zielono), a następnie zatwierdzeniu, że na pewno chcemy wykonać tą instrukcję przyciskiem OK (podkreślony na zielono) pokaże nam się:

Błąd:

Nie można usunąć ani zaktualizować wiersza nadrzędnego /rodzicielskiego (czyli tego, który chcemy): klucz obcy ogranicza nam taką możliwość:

biblioteka.ksiazka_autor, CONSTRAINT ksiazka_autor_ibfk_2 FOREIGN KEY (id_ksiazka) REFERENCES ksiazka (id))

czyli jest jakieś powiązanie tego wiersza ( o id = 7) z tabelą ksiazka_autor.

Rzeczywiście, w tabeli ksiazka_autor mamy rekord z id_ksiazka = 7. I to dwukrotnie. Z tego powodu nie możemy usunąć w tabeli ksiazka rekordu z id=7. Co zrobić? Najpierw trzeba usunąć w tabeli ksiazka_autor wszystkie rekordy, w których najduje się kolumna id_ksiazka o wartości 7. Więc do dzieła:

DELETE FROM ksiazka_autor WHERE id_ksiazka = 7;

Usuwamy teraz rekordy w tabeli ksiazka_autor, gdzie id_ksiazka=7. Klikamy najpierw przycisk „Wykonaj”, potem OK i :

Sprawdźmy, czy rzeczywiście nie mamy już rekordów z id_ksiazka = 7:

Rzeczywiście, oba rekordy zostały usunięte. Teraz możemy wrócić do usunięcia rekordu z tabeli ksiazka.

Wybierzmy teraz tabelę ksiazka, kliknijmy na „Przeglądaj” i zobaczymy, że mamy 7 rekordów:

Widzimy, że mamy nowo wprowadzony rekord o id = 7, którego nie udało nam się wcześniej usunąć. Ale ponieważ usunęliśmy w tabeli ksiazka_autor wszystkie rekordy w których id_ksiazka = 7, to teraz powinniśmy bez problemów usunąć ten rekord w tabeli ksiazka. Zobaczmy.

Ponownie wpisujemy instrukcję usuwania:

DELETE FROM ksiazka WHERE id = 7;

Jak widzimy, mamy taki sam obrazek jak przedtem, jednak dla odróżnienia, klawisze do naciśnięcia zaznaczyłem dwiema zielony kreskami. Klikamy kolejno „Wykonaj”, potem OK i…

Usunęłonam! Sprawdzimy jeszcze:

Brak rekordu o id = 7. Czyli teraz nam usunęło, tak jak chcieliśmy. Trochę musieliśmy się natrudzić, ale…

Ale teraz mamy spójne dane – w tabeli ksiazka_autor nie ma odwołania do nieistniejącej pozycji z tabeli ksiazka. Sam SQL nam to przypilnował, dzięki mechanizmowi klucza obcego i powiązania z jego pomocą tabeli ksiazka z tabelą ksiazka_autor. Dokładnie to samo byłoby, gdybyśmy chcieli usunąć z tabeli autor któregoś z nowych autorów, a którzy byli przypisani do tej samej książki – musielibyśmy w tabeli ksiazka_autor usnąć najpierw rekord z tym autorem, a dopiero potem rekord z autorem w tabeli autor. Oczywiście, wtedy z pozostałych danych by wynikało, że dana książka ma tylko jednego autora (bo drugi autor by pozostał nietknięty, książka przecież miała dwóch autorów). Ćwiczenie z usunięciem autora/autorów pozostawiam Czytelnikowi.

Dokładnie tak samo wszystko zrobimy z tabelą wydawnictwo oraz z tabelą ksiazka_wydawnictwo. Ćwiczenia z tymi tabelami także pozostawiam Czytelnikowi.

Jak widać, klucze obce i związane z nimi ograniczenia bywają bardzo pomocne. Pozwlają nam utrzymać „czystość” bazy danych. Jednak trochę męczące jest najpierw wprowadzanie danych książki do jednej tabeli (ksiazka), za chwilę dane z tej samej książki wprowadzamy do drugiej tabeli (autor), później do trzeciej (wydawnictwo), by po chwili zacząć wiązać te dane ze sobą (ksiazka_autor i ksiazka_wydawnictwo). Na prawdę nie jest to fajne. I może znalazłby się jakiś sposób, by to jakoś zautomatyzować? To znaczy za jednym zamachem wpisać wszystkie dane z książki, a instrukcja, czy procedura sama by już te dane odpowiednio porozdzielała pomiędzy odpowiednie tabele? Być może byłby sposób. Omówimy sobie to w następnej części.

Wracając jeszcze do usuwania danych. Nie zawsze powinno się je usuwać. Czasem (a może częściej) o wiele lepiej jest je zachować, nadając im pewien status – na przykład „Aktualny”, „Archiwalny”, „Na stanie”, „Brak”, albo po prostu określając liczbami: 1- aktualny, 2 – archiwalny, 3- brak, 4 – na stanie itd. Dzięki temu wybieralibyśmy tylko te dane, które w danym momencie są „w naszym posiadaniu realnym”, że tak to ujmę (bo dane to są w naszym posiadaniu, ale konkretne egzemplarze książek już niekoniecznie). Oczywiście taka kolumna ze statusem byłaby w tabeli ksiazka. Nigdy nie kasowalibyśmy fizycznie danych, ich status określałby dostępność realnego egzemplarza książki w naszej bibliotece. Dodatkowo powinno się jeszcze wprowadzić kolumnę z datą zmiany statusu. Potem okazałoby się jeszcze, że przydałaby się kolumna opisująca status, powód zmiany. Doszliśmy do momentu, że być może powinno się na tą okoliczność wprowadzic dodatkową tabelę status_ksiazek, w której przechowywalibyśmy status wszytskich książek, daty jego zmiany i opisy. Aktualny status znajdowalibyśmy po dacie – najnowsza zmiana statusu oznaczałaby, że to jest właśnie status bieżący danego egzemplarza. A oznaczenia statusów w sensie liczbowym mielibyśmy w tabeli status.