Klucz obcy w SQL

Klucz obcy (FOREIGN KEY) to ograniczenie w bazie danych, które tworzy powiązanie między kolumną w jednej tabeli a kluczem głównym lub unikalnym w innej tabeli. Służy do pilnowania poprawności relacji między tabelami.

Klucze obce są podstawowym mechanizmem, dzięki któremu relacyjne bazy danych mogą łączyć ze sobą informacje rozłożone na wiele tabel - bez utraty spójności danych.

Klucz główny a klucz obcy

W bazie danych spotyka się dwa najważniejsze rodzaje kluczy:

• Klucz główny (PRIMARY KEY) - jednoznacznie identyfikuje wiersz w tabeli. W każdej tabeli powinien być dokładnie jeden klucz główny. Wartości w kolumnie klucza głównego muszą być unikalne i nie mogą być puste (NULL).
• Klucz obcy (FOREIGN KEY) - odwołuje się do klucza głównego (lub unikalnego) w innej tabeli. Wartości w kolumnie klucza obcego muszą istnieć w tabeli docelowej.

Mówiąc obrazowo: klucz główny to "dowód tożsamości" wiersza, a klucz obcy to "wskaźnik" do tego dowodu w innej tabeli.

Składnia klucza obcego

Typowy zapis w CREATE TABLE:

FOREIGN KEY (kolumna_w_tej_tabeli)
REFERENCES tabela_docelowa(kolumna_docelowa)

Oznacza to, że wartości wpisywane do kolumna_w_tej_tabeli muszą istnieć w kolumna_docelowa tabeli wskazanej po słowie REFERENCES.

Jak czytać fragment kodu z kluczem obcym

Przykład:

FOREIGN KEY (imie) REFERENCES obiekty(imiona)

Czyta się to tak:

• klucz obcy jest ustawiony na kolumnie imie w bieżącej tabeli (tej, którą tworzymy),
• odwołuje się do tabeli obiekty,
• wskazuje w niej kolumnę imiona.

W pytaniach egzaminacyjnych trzeba szczególnie uważać na nawiasy:

• kolumna w pierwszym nawiasie (po FOREIGN KEY) - kolumna, na której zdefiniowano klucz obcy,
• kolumna w drugim nawiasie (po nazwie tabeli w REFERENCES) - kolumna, do której następuje odwołanie.

Pełny przykład

CREATE TABLE klienci (
    id INT PRIMARY KEY,
    imie VARCHAR(50),
    nazwisko VARCHAR(50)
);

CREATE TABLE zamowienia (
    id INT PRIMARY KEY,
    klient_id INT,
    data_zamowienia DATE,
    FOREIGN KEY (klient_id) REFERENCES klienci(id)
);

W tym kodzie:

• tabela klienci ma klucz główny id,
• tabela zamowienia ma własny klucz główny id oraz kolumnę klient_id,
• klient_id jest kluczem obcym wskazującym na klienci(id),
• nie można wstawić zamówienia z klient_id, który nie istnieje w tabeli klienci.

Integralność referencyjna

Integralność referencyjna to zasada mówiąca, że każda wartość klucza obcego musi odpowiadać istniejącej wartości klucza głównego w tabeli docelowej (lub być pusta, jeśli kolumna na to zezwala).

W praktyce oznacza to:

• nie można dodać zamówienia do klienta, który nie istnieje,
• nie można usunąć klienta, jeśli ma on aktywne zamówienia (chyba że zdefiniowano akcję kaskadową),
• nie można zmienić id klienta, jeśli odwołują się do niego inne tabele.

To jeden z głównych powodów, dla których stosuje się klucze obce - chronią bazę przed niespójnymi (osieroconymi) rekordami.

Inne rodzaje integralności

W bazach danych wyróżnia się kilka typów integralności:

W pytaniach egzaminacyjnych najczęściej pojawia się integralność referencyjna.

Akcje kaskadowe ON DELETE / ON UPDATE

Przy definiowaniu klucza obcego można określić, co ma się stać z rekordami zależnymi, gdy rekord w tabeli głównej zostanie zmieniony lub usunięty:

FOREIGN KEY (klient_id) REFERENCES klienci(id)
ON DELETE CASCADE
ON UPDATE CASCADE

Najczęstsze akcje:

• CASCADE - zmiana lub usunięcie kaskaduje się do tabeli zależnej (usuwa też zamówienia klienta),
• SET NULL - kolumna klucza obcego zostanie ustawiona na NULL,
• SET DEFAULT - kolumna otrzyma wartość domyślną,
• RESTRICT / NO ACTION - zablokuj operację, jeśli istnieją powiązane rekordy (domyślne zachowanie).

Relacje między tabelami

Klucze obce realizują relacje (związki) między tabelami. Wyróżnia się trzy podstawowe typy:

Relacja jeden do jednego (1:1)

Jeden rekord z tabeli A odpowiada dokładnie jednemu rekordowi w tabeli B. Stosowana rzadko, najczęściej do rozdzielenia rzadko używanych danych:

CREATE TABLE pracownicy (
    id INT PRIMARY KEY,
    imie VARCHAR(50)
);

CREATE TABLE dane_kontaktowe (
    pracownik_id INT PRIMARY KEY,
    telefon VARCHAR(15),
    FOREIGN KEY (pracownik_id) REFERENCES pracownicy(id)
);

Klucz obcy jest tu jednocześnie kluczem głównym - dlatego relacja jest 1:1.

Relacja jeden do wielu (1:N)

Najczęściej spotykana. Jeden rekord z tabeli A odpowiada wielu rekordom w tabeli B. Klient może mieć wiele zamówień, ale każde zamówienie należy do dokładnie jednego klienta.

Klucz obcy umieszcza się po stronie "wiele", czyli w tabeli, w której rekordów może być więcej:

CREATE TABLE klienci (
    id INT PRIMARY KEY,         -- strona "jeden"
    imie VARCHAR(50)
);

CREATE TABLE zamowienia (
    id INT PRIMARY KEY,
    klient_id INT,              -- strona "wiele" - tu jest klucz obcy
    FOREIGN KEY (klient_id) REFERENCES klienci(id)
);

Relacja wiele do wielu (N:M)

Wiele rekordów z tabeli A odpowiada wielu rekordom w tabeli B. Np. uczeń może mieć wielu nauczycieli, a nauczyciel wielu uczniów.

Relacja wiele do wielu wymaga tabeli pośredniej (łącznikowej), w której znajdują się dwa klucze obce - po jednym do każdej z tabel:

CREATE TABLE uczniowie (
    id INT PRIMARY KEY,
    imie VARCHAR(50)
);

CREATE TABLE nauczyciele (
    id INT PRIMARY KEY,
    imie VARCHAR(50)
);

CREATE TABLE uczen_nauczyciel (
    uczen_id INT,
    nauczyciel_id INT,
    PRIMARY KEY (uczen_id, nauczyciel_id),
    FOREIGN KEY (uczen_id) REFERENCES uczniowie(id),
    FOREIGN KEY (nauczyciel_id) REFERENCES nauczyciele(id)
);

Tabela uczen_nauczyciel rozbija relację N:M na dwie relacje 1:N - każdy z dwóch kluczy obcych łączy ją z jedną z tabel głównych.

Schemat graficzny relacji

   tabela KLIENCI                tabela ZAMOWIENIA
   +-----------+                 +-------------+
   | id (PK)   | <-------------- | klient_id   | (FK)
   | imie      |    relacja 1:N  | id (PK)     |
   | nazwisko  |                 | data        |
   +-----------+                 +-------------+
       jeden klient                 wiele zamówień

PK = Primary Key (klucz główny), FK = Foreign Key (klucz obcy).

Klucze obce a indeksy

Kolumny używane jako klucze obce są często wyszukiwane i sortowane - bo łączy się je z innymi tabelami (operacja JOIN). Dlatego warto na nich zakładać indeksy, które przyspieszają operacje wyszukiwania i łączenia.

W systemie MySQL klucz obcy automatycznie tworzy indeks na kolumnie - nie trzeba tego robić ręcznie. W innych systemach (np. PostgreSQL) indeks trzeba czasem stworzyć osobno.

Klucze obce w Microsoft Access

W programie Microsoft Access integralność danych zapewnia się przez utworzenie relacji między tabelami w widoku "Relacje" i zaznaczenie opcji "Wymuszaj więzy integralności" (Enforce Referential Integrity). To graficzny odpowiednik definiowania kluczy obcych przez FOREIGN KEY w SQL.

Fazy projektowania relacyjnej bazy danych

Klucze obce powstają w trakcie projektowania bazy. Typowa kolejność etapów:

1. Analiza wymagań - zebranie informacji, co baza ma przechowywać,
2. Projekt konceptualny - identyfikacja encji (np. klient, zamówienie) i związków między nimi (model ER),
3. Projekt logiczny - przekształcenie modelu w tabele, kolumny, klucze główne i obce,
4. Projekt fizyczny - wybór typów danych, indeksów, optymalizacja,
5. Implementacja - utworzenie tabel za pomocą CREATE TABLE, wstawianie danych, testowanie.

Najczęstsze pytania egzaminacyjne o klucz obcy

Co to jest klucz obcy?
Kolumna w jednej tabeli, która odwołuje się do klucza głównego w innej tabeli, pilnując poprawności powiązań.

Co oznacza zapis FOREIGN KEY (imie) REFERENCES obiekty(imiona)?
Klucz obcy jest na kolumnie imie w bieżącej tabeli i wskazuje na kolumnę imiona w tabeli obiekty.

Co to integralność referencyjna?
Zasada, że każdy klucz obcy musi wskazywać na istniejący rekord w tabeli docelowej.

W której tabeli umieszcza się klucz obcy w relacji 1:N?
W tabeli reprezentującej stronę "wiele" (np. zamówienia, jeśli klient ma wiele zamówień).

Jak utworzyć relację wiele do wielu?
Tworząc tabelę pośrednią (łącznikową), która zawiera dwa klucze obce - po jednym do każdej z głównych tabel.

Jaka klauzula określa powiązania między tabelami w MySQL?
Klauzula FOREIGN KEY ... REFERENCES ....

Co robi PRIMARY KEY w CREATE TABLE?
Sprawia, że dane pole staje się kluczem głównym - jego wartości muszą być unikalne i nie mogą być NULL.

Co musi mieć każda tabela, aby baza działała spójnie?
Klucz główny (PRIMARY KEY) - jednoznacznie identyfikuje każdy wiersz.

Jak zapewnić integralność danych w Microsoft Access?
Tworząc relację między tabelami i włączając opcję "Wymuszaj więzy integralności".

Czy na kluczu obcym warto zakładać indeks?
Tak - kolumny używane do łączenia tabel (JOIN) są często przeszukiwane, dlatego indeks znacznie przyspiesza operacje. W MySQL klucz obcy automatycznie tworzy indeks.

Co zapamiętać na egzamin?

• Klucz obcy (FOREIGN KEY) wskazuje na klucz główny (PRIMARY KEY) w innej tabeli.
• W zapisie FOREIGN KEY (kolumna_A) REFERENCES tabela_B(kolumna_B) - kolumna_A to klucz obcy, a kolumna_B to kolumna docelowa.
• Integralność referencyjna = klucz obcy musi wskazywać na istniejący rekord.
• W relacji 1:N klucz obcy jest po stronie "wiele".
• Relacja N:M wymaga tabeli pośredniej z dwoma kluczami obcymi.
• Każda tabela powinna mieć klucz główny (PRIMARY KEY).
• Wartości PRIMARY KEY muszą być unikalne i nie NULL.
• W Microsoft Access integralność zapewnia się przez "Wymuszaj więzy integralności".
• Klauzula MySQL określająca powiązania to FOREIGN KEY.
• Klucze obce warto indeksować dla szybszych operacji JOIN.