• Artykuł

          

Encyklopedia SQL - Klucze obce - FOREIGN KEY  

Udostępnij na: Facebook

Autor: Paweł Wilkosz

Opublikowano: 2012-02-03

W codziennej pracy z bazami danych najczęściej wykorzystuje się wiedzę składowaną w wielu tabelach, połączonych ze sobą za pomocą relacji FOREIGN KEY – PRIMARY KEY. Dlatego w tej części Encyklopedii SQL poznasz mechanizm łączenia tabel w relacyjnej bazie danych za pomocą kluczy: głównego i obcego.

Przed wykonaniem zadań zapoznaj się z następującym materiałem:

Po wykonaniu zadań będziesz wiedział:

  • co to jest klucz obcy,
  • w jaki sposób łączyć tabele w celu wyciągania interesujących informacji.

Klucz obcy - FOREIGN KEY

Najprostsza definicja klucza obcego, jaką można przytoczyć, specyfikuje dodatkową kolumnę lub zbiór kolumn w danej tabeli z wartościami, stanowiącymi klucz główny w innej. Posługując się przykładem, zaprezentowanym w poprzednim artykule (Klucze główne – PRIMARY KEY i IDENTITY), tabela „Zakupy” (Tabela 1.) agregowała w sobie dwa klucze obce: ID Klienta oraz ID Produktu, które w tabelach „Klient” oraz „Produkt” figurowały jako klucze główne. Na tej podstawie łatwo można było rozróżnić, który rekord, określający sprzedaż danego produktu, należał do Jana Kowalskiego, a który do Krzysztofa Nowaka.

Tabela 1. Tabela "Zakupy".

ID ID Klienta ID Produktu
1 1 1
2 2 1
3 1 2

Zaglądając wewnątrz bazy danych AdventureWorks, a dokładniej w tabelę HumanResources.Employee, również można odnaleźć utworzone relacje z innymi tabelami. Na przykład kolumna ContactID stanowi klucz obcy ze wskazaniem na tabelę Person.Contact (Rys. 1.).

**Rys.1. TabelaHumanResources.Employee bazy AdventureWorks.

W jaki sposób zatem wykorzystać fakt istnienia relacji klucz główny – klucz obcy do wydobywania danych z wielu tabel? W tym celu można posłużyć się zapytaniem JOIN (INNER, LEFT OUTER, RIGHT OUTER) z warunkiem złączeniowym ON. W najprosztszej formie przyjmuje ono następującą składnię:

SELECT [tab1].kolumna1, [tab2].kolumna2, … [tabN].kolumnaN
FROM tabela1 AS tab1
[INNER|LEFT OUTER| RIGHT OUTER] JOIN tabela2 AS tab2
ON warunek_złączeniowy

Objaśnienie poszczególnych części zapytania zostało zestawione w Tabeli 2.

Tabela 2. Objaśnienie poszczególnych części zapytania JOIN.

Słowo kluczowe Objaśnienie
AS Po słowie kluczowym AS można nadać alias danej tabeli. Ta opcja jest niezwykle przydatna, gdy w dwóch tabelach znajdują się kolumny o takiej samej nazwie.
INNER JOIN Złączenie wewnętrzne dwóch tabel.
OUTER JOIN (RIGHT, LEFT) Złączenie zewnętrzne dwóch tabel. LEFT, RIGHT OUTER JOIN zwraca wszystkie rekordy, należące do tabeli wymienionej przed słowem kluczowym OUTER JOIN, niezależnie od tego, czy istnieją dopasowania w tabeli wymienionej po nim. W przypadku braku dopasowania w zbiorze wynikowym pojawi się wartość NULL.
ON Warunek złączeniowy – najczęściej dopasowanie klucza głównego z obcym.

 

Aby lepiej zobrazować, w jaki sposób łączyć tabele w zapytaniach T-SQL, posłużę się następującym przykładem. Do programistów baz danych w AdventureWorks zgłosił się pracownik działu księgowości i rozliczeń. Chce wyciągnąć z bazy danych informacje o zmianie cen poszczególnych produktów, posortowanych wg ID danego produktu oraz, w ramach niego, posortowanych wg ceny rosnąco. Aby spełnić prośbę kolegów z księgowości, programiści musieli połączyć ze sobą dwie tabele: Production.Product oraz Production.ProductCostHistory za pomocą relacji klucz główny-klucz obcy, a następnie wykorzystać klauzulę, pozwalającą sortować zbiór wynikowy – ORDER BY. Finalne zapytanie przyjęło następującą postać:

SELECT pr.ProductID, pr.Name, prh.StandardCost
FROM Production.Product AS pr
INNER JOIN Production.ProductCostHistory AS prh
ON pr.ProductID = prh.ProductID
ORDER BY pr.ProductID, prh.StandardCost

i w rezultacie zrwacało wyniki zgodnie z Rys. 2.

Rys. 2. Zapytanie łączące tabele Product oraz ProductCostHistory.

Tworzenie klucza obcego w T-SQL

T-SQL pozwala na oznaczenie danej kolumny, bądź zbioru kolumn, jako klucza obcego. Jedną z metod jest zdefiniowanie tzw. funkcji CONSTRAINT podczas tworzenia tabeli. Składnia przykładowego zapytania została przedstawiona na poniższym listingu:

CREATE TABLE Nazwa_tabeli
(
    kolumna1 TYP,
  …
  kolumnaN TYP,
    
  CONSTRAINT nazwa_klucza_obcego
  FOREIGN KEY (nazwa_kolumny_z_tworzonej_tabeli)
  REFERENCES tabela_referencyjna (kolumna_tabeli_referencyjnej)
)

Jeżeli zapytanie zostanie wykonane bezbłędnie, w SQL Server Management Studio, w lokalizacji Object Explorer->Baza Danych->Nazwa Tabeli->Keys, utworzony klucz obcy powinien funkcjonować włącznie z podaną nazwą. Na Rys. 3. został przedstawiony przykład kluczy obcych dla tabeli HumanResources.Employee.

Rys. 3. Klucze obce w tabeli HumanResources.Employee bazy AdventureWorks.

Informacja

Zapamiętaj, że:

  1. Klucz obcy stanowi kolumnę, bądź zbiór kolumn, będących kluczem głównym w innej tabeli.
  2. Łączenie tabel może odbywać się poprzez zapytanie z wykorzystaniem INNER/OUTER JOIN.
  3. Klucze obce tworzy się w T-SQL, dodając odpowiednie wartości w klauzuli CONSTRAINT FOREIGN KEY.

Podsumowanie

Niniejsza publikacja opisuje zastosowanie klucza obcego do łączenia tabel w celu wyciągania bardziej szczegółowych informacji.

W kolejnej publikacji nauczysz się, w jaki sposób konstruować podzapytania.

Dodatkowo zobacz: