Łączenie tabel - podstawowe informacje

Dzięki temu, że na wybranych kolumnach w poszczególnych tabelach danych wprowadzamy ograniczenie klucza podstawowego, możemy od tej pory jednoznacznie identyfikować i odnosić się do konkretnych rekordów danych w tych tabelach. Aby umożliwić nam wykorzystanie maksymalnego potencjału systemu zarządzania relacyjną bazą danych, system SQL Server, zgodnie z relacyjnym modelem danych i stosowanymi w tym modelu zasadami normalizacji tabel, zachęca nas do skrupulatnego rozdzielania przechowywanych informacji do postaci osobnych tabel, nazywanych formalnie relacjami. Po takim podziale danych informacje te zostają zazwyczaj pozbawione początkowego kontekstu i sensu, w jakim występowały przed ich rozdzieleniem, tak więc same w sobie stają się poniekąd mniej lub bardziej użytecznymi wykazami danych.

Przykładem może tu być sytuacja, w której nieznormalizowana i wielokolumnowa tabela złożona z identyfikatora pracownika (EmployeeID), jego imienia (FirstName) i nazwiska (LastName), stanowiska (Position) oraz nazwiska jego bezpośredniego przełożonego (Supervisor) zostaje znormalizowana do postaci kilku odrębnych tabel, które przechowują wszystkie te pola oddzielnie. Jeśli spojrzymy na tę tabelę przed procesem normalizacji, łatwo nam będzie wyczytać z niej imiona i nazwiska poszczególnych pracowników, wskazać zajmowane przez nich stanowiska oraz sprawdzić, kto jest bezpośrednim przełożonym konkretnego pracownika. Jeśli jednak dane te będziemy przeglądać oddzielnie w ramach kilku odrębnych tabel, stracimy wówczas część wiedzy o tym, który z naszych pracowników zajmuje konkretne stanowisko, czy też który konkretny pracownik jest bezpośrednim przełożonym danej sooby. Jedyne co wówczas otrzymamy, to trzy odrębne tabele, stanowiące pełny wykaz pracowników, wykaz istniejących stanowisk, oraz listę pracowników, którzy są czyimiś nadzorcami. Aby móc na powrót odzyskać pierwotny kontekst, w jakim dane te występowały przed procesem normalizacji tabel, musimy dokonać na nich odpowiednich powiązań. Tego rodzaju sprzężeń dokonywać będziemy na konkretnych kolumnach tabel, zaś powstałe w ten sposób powiązania nazywać będziemy związkami (ang. relationships).

Przykład prostego złączenia tabel

Wiemy już, że jeśli we wszystkich tabelach zdefiniujemy sobie ograniczenie klucza podstawowego (ang. Primary Key), wówczas w bazie danych będziemy mogli jednoznacznie odnosić się do każdego z rekordów danych w każdej takiej tabeli. Łączenie tabel polegać więc będzie na wybieraniu w określonych tabelach konkretnych kolumn, tak aby przechowywane w nich wartości nadawały naszym danym odpowiedni kontekst i sens. Spójrzmy na prosty przykład.

Przypuśćmy, że w bazie danych mamy już jedną niewielką tabelę o nazwie Customers, przechowującą listę naszych klientów. Do bazy danych chcemy teraz wprowadzić nową tabelę Orders, która gromadzić będzie informacje dotyczące realizowanych przez klientów zamówień. Jasne jest, że w jakiś sposób każde tworzone zamówienie będziemy musieli odpowiednio powiązać z konkretnym klientem w tabeli Customers. Powiązanie takie świadczyć nam będzie również kontrolę integralności danych, uniemożliwiając nam zdefiniowanie zamówienia złożonego przez nieistniejącego klienta. To, co chcemy więc osiągnąć, to dwie złączone ze sobą tabele Orders i Customers - dla zachowania prostoty tego przykładu nazwy zamawianych produktów będziemy wstawiać do tabeli Orders ręcznie.

Planowanie tabel

Rozważmy najpierw tabelę Customers, która przechowuje listę klientów naszej firmy. Tabela ta składać się będzie z kolumn Name i LastName do przechowywania imienia i nazwiska każdego klienta oraz kolumny CustomerID, która w ramach zdefiniowanego na niej ograniczenia klucza podstawowego umożliwi nam odnoszenie się do konkretnego klienta w tej tabeli. To już oczywiście znamy.

Teraz, chcąc zdefiniować w tabeli Orders nowe zamówienie na dany produkt, musimy być w stanie wskazać dla niego konkretnego klienta z tabeli Customers. Tym samym dane z tabel Orders i Customers musimy ze sobą złączyć, tak aby było wiadomo, który klient zakupił dany produkt. Powiązania tego dokonamy w ramach tabeli Orders, która składać się będzie z trzech kolumn. Pierwszą kolumną będzie kolumna o nazwie OrderID, będąca standardowo kluczem podstawowym tej tabeli, pozwalającym nam jednoznacznie odnosić się do poszczególnych zamówień. W drugiej kolumnie, o nazwie CustomerID, przechowywać będziemy informacje na temat konkretnego klienta, który złożył zamówienie. Informacji tych nie będziemy jednak wprowadzać tu sztucznie poprzez wpisywanie nazwiska konkretnego klienta, lecz zamiast tego kolumnę tę zdefiniujemy jako klucz obcy powiązany z kluczem podstawowym tabeli Customers. Innymi słowy, kolumna CustomerID w tabeli Orders zdefiniowana zostanie dokładnie tak, jak zdefiniowana została kolumna CustomerID w tabeli Customers, a dzięki ich powiązaniu będzie ona odtąd przechowywać nie sztuczną wartość z nazwiskiem klienta, lecz identyfikator reprezentujący konkretny rekord w tabeli klientów Customers. Trzecią kolumnę tabeli Orders, o nazwie ProductName, zdefiniujemy sobie jako dane tekstowe typu varchar(100) i będziemy w niej po prostu określać nazwy produktów, które zostały zakupione przez klienta.

Projekt tabeli Orders przedstawia się więc następująco:

    Column Name     Data Type       Allow Nulls
-----------------------------------------------
PK  OrderID         int             -
    CustomerID      int             -
    ProductName     varchar(100)    +	

Zgodnie z powyższym schematem tabela Orders zawiera kolumnę OrderID będącą kluczem podstawowym tej tabeli, jednoznacznie identyfikującym każdy rekord danych w tej tabeli (czyli każde zamówienie). Kolumna CustomerID tabeli Orders zamiast tekstowych danych konkretnego klienta w postaci jego imienia i nazwiska przechowywać będzie identyfikator tego klienta pozyskany z pola CustomerID będącego kluczem podstawowym tabeli Customers. Czynimy tak, bo skoro klucz ten pozwala nam jednoznacznie wskazywać konkretnych klientów w tabeli Customers, to przy jego użyciu będziemy mogli łatwo zaadresować tego konkretnego klienta również w tabeli Orders. Aby było to możliwe, obie kolumny CustomerID z obu tych tabel muszą zostać odpowiednio powiązane, co odbędzie się następująco:

Kolumna CustomerID w tabeli Customers pozostaje niezmiennie kluczem podstawowym w tej tabeli, ale jednocześnie odpowiednik tej kolumny tworzymy w tabeli Orders, gdzie stanie się ona tzw. kluczem obcym (ang. Foreign key) w tabeli Orders. To właśnie poprzez to powiązanie danych, czy też to połączenie obu tych tabel na kolumnach CustomerID, system bazy danych jest w stanie zrozumieć związek pomiędzy danymi z obu tych tabel (w tym przykładzie związek pomiędzy produktami a klientami realizowany przez poszczególne zamówienia) oraz odpowiednio reagować i chronić nas przed potencjalnymi próbami i problemami związanymi z naruszaniem integralności tych danych. Tak więc dzięki odpowiednim zabiegom normalizacji tabel nasze dane rozczłonkowane zostają na odrębne relacje (tabele), co nie tylko przyspiesza do nich dostęp i przede wszystkim zapobiega niepotrzebnemu dublowaniu tych informacji w bazie danych, ale jednocześnie dzięki odpowiednim złączeniom tabel nadaje im odpowiednie znaczenie.

Na tym etapie złączenie to, realizowane pomiędzy kolumną CustomerID w tabeli Orders i kolumną CustomerID w tabeli Customers (swoją drogą ich nazwy nie muszą być takie same), istnieje tylko dla nas, czyli my wiemy, że kolumny te są ze sobą powiązane, jednak system SQL Server już nie. Aby więc ten związek w pełni sformalizować, musimy utworzyć odpowiednie połączenie pomiędzy tymi kolumnami, wskazując przy tym wspomniane tabele jako źródła danych dla klucza podstawowego i obcego. Oto, jak możemy tego dokonać.

Łączenie tabel

Tkwiąc jeszcze w projekcie tabeli Orders (widok Design), klikamy przycisk o nazwie Relationships (Związki) ulokowany na głównym pasku narzędzi, dostępnym tuż nad schematem opracowywanej tabeli. Otworzy się nowe okno dialogowe o nazwie Foreign Key Relationships (Związki klucza obcego), w ramach którego będziemy mogli zdefiniować nowy związek (lub związki) pomiędzy wybranymi tabelami. Aby utworzyć taki związek, należy kliknąć przycisk Add (Dodaj) dostępny w dolnej części sekcji Selected Relationship (Wybrany związek), co spowoduje utworzenie nowego związku o domyślnej nazwie FK_Orders_Orders. Jak widać związek ten jest na razie związkiem pomiędzy tabelą Orders a tabelą Orders, tak więc tabela ta niejako odnosi się póki co sama do siebie. Jest to zachowanie domyślne, bowiem system nie wie przecież, z którą tabelą i na jakiej kolumnie zamierzamy powiązać tabelę Orders. Co więcej, jak czytamy w komunikacie widocznym w wyświetlonym oknie dialogowym, aby nowy związek mógł zostać pomyślnie utworzony, musimy najpierw odpowiednio zmodyfikować właściwości zgrupowane w ramach węzła Tables And Columns Specification. Rozwijamy więc ten węzeł na liście własności tworzonego związku, po czym jako tabele oraz klucze podstawowe i obce wskazujemy odpowiednie obiekty bazy danych. Możemy tego dokonać również poprzez kliknięcie przycisku wielokropka (...) przypisanego sekcji Tables And Columns Specification, po kliknięciu którego wyświetli się okno dialogowe Tables and Columns, w którym będziemy mogli w przystępny sposób określić nazwę tworzonego związku, a także wskazać tabele klucza podstawowego i klucza obcego, wraz z podaniem dla tych tabel konkretnych kolumn, które pełnią funkcję tych kluczy.

W naszym przykładzie tabelą klucza podstawowego będzie tabela Customers, a przechowywanym w niej kluczem podstawowym będzie kolumna CustomerID. Tabela ta została wybrana na tabelę klucza podstawowego, ponieważ to z niej pozyskiwać będziemy informacje, które identyfikowane są właśnie kluczem podstawowym CustomerID. Tym samym jako tabelę klucza obcego wybieramy tabelę Orders, w której wartości klucza podstawowego z tabeli Customers przechowywane będą w ramach kolumny klucza obcego CustomerID. Z lewej strony wskazujemy więc dane dotyczące tabeli zawierającej klucz podstawowy, czyli dane pochodzące z innej tabeli, które chcemy wykorzystać w tabeli bieżącej. Z prawej strony wskazujemy z kolei dane z tabeli zawierającej klucz obcy. Jak już wiemy, klucz obcy znajdować się będzie w bieżącej tabeli (Orders) - a więc tej, dla której tworzymy to powiązanie. Podsumowując, jeśli z poziomu bieżącej tabeli chcemy odnieść się do rekordów zawartych w innej tabeli, to rekordy te przechowywane będą pod kluczem obcym w bieżącej tabeli, zaś z perspektywy tej drugiej tabeli będą one identyfikowane kluczem podstawowym.

Po kliknięciu przycisku OK nazwa naszego związku pomiędzy relacjami Orders i Customers powinna brzmieć FK_Orders_Customers, zaś własności w grupie Tables And Columns Specification powinny wyglądać następująco:

Foreign Key Base Table     - Orders
Foreign Key Columns        - CustomerID
Primary/Uniqie Key Base    - Customers
Primary/Unique Key Columns - CustomerID 

Po zakończeniu tworzenia powiązania pomiędzy obiema tabelami klikamy przycisk Close i zapisujemy wprowadzone zmiany w bazie poprzez kliknięcie przycisku Save.

Podsumowanie

Co w ten sposób zyskaliśmy? Jeśli zajrzymy do tabeli Customers, w ramach jej podwęzła Keys (Klucze) ujrzymy pozycję PK_Customers - nic w tym dziwnego, w końcu tabela ta zawiera kolumnę CustomerID będącą kluczem podstawowym tej tabeli. Jeśli zajrzymy do tabeli Orders, również znajdziemy w niej obiekt klucza podstawowego, ale w tym wypadku będzie się tam jeszcze znajdował obiekt klucza obcego o nazwie FK_Orders_Customers, który utworzony został w ramach zdefiniowanego przez nas powiązania pomiędzy tabelami Orders i Customers. Tym samym powiązanie to nie jest już dłużej tylko naszym własnym założeniem, ale istnieje ono formalnie w systemie bazy danych.

Od tej pory silnik bazy danych wie, że tabele te są ze sobą powiązane z wykorzystaniem relacji klucz obcy-klucz podstawowy, a tym samym może sprawować na tych tabelach kontrolę w obszarze integralności danych. Teraz naszą tabelę zamówień Orders możemy już zacząć wypełniać danymi, poprzez wprowadzanie odpowiedniego identyfikatora zamówienia (lub jego automatyczne wyliczanie), podawanie identyfikatora klienta istniejącego w tabeli Customers oraz podawanie wprost nazwy jakiegoś produktu. Skupmy się jednak na samym związku pomiędzy tabelami Orders i Customers, zdefiniowanym w ramach będącej kluczem obcym kolumny CustomerID. Skoro kolumna ta jest kluczem obcym w tabeli Orders będącym z związku z kluczem podstawowym w tabeli Customers, może ona odtąd reprezentować tylko wartości znajdujące się w kolumnie Customers. Jeśli więc podczas wprowadzania danych do kolumny Orders wstawimy w tym miejscu identyfikator klienta, który nie istnieje w tabeli Customers, system nie zezwoli nam na wprowadzenie takiego rekordu do bazy, a zamiast tego zgłosi nam błąd, że instrukcja wstawiania do bazy danych INSERT napotkała konflikt związany z ograniczeniem klucza obcego FK_Orders_Customers.