SQL od podstaw · część 03

Relacje, klucze i diagram E/R

Połącz tabele tak, aby baza wiedziała, które rekordy do siebie należą. Naucz się odczytywać liczebność i obowiązkowość powiązań.

Część bazodanowa INF.03

Klucz kandydujący, główny i obcy

Klucz kandydujący to minimalny zestaw atrybutów jednoznacznie identyfikujący rekord. Minimalny znaczy, że usunięcie któregokolwiek atrybutu odbierze tę właściwość. Jeden klucz kandydujący wybieramy jako klucz główny (PRIMARY KEY). Pozostałe mogą być kluczami alternatywnymi.

Klucz naturalny wynika z dziedziny, np. numer seryjny urządzenia. Klucz sztuczny, np. techniczne id, dodajemy w projekcie. Żaden wybór nie jest automatycznie najlepszy. Numer identyfikacyjny może zmieniać format, a zwykłe imię nie jest unikalne. Przy sztucznym id nadal trzeba zabezpieczyć rzeczywistą unikalność danych, jeśli wymaga jej biznes.

Klucz złożony zawiera kilka kolumn. W tabeli pozycje para (zamowienie_id, produkt_id) może identyfikować produkt w zamówieniu, jeśli produkt wolno umieścić w nim tylko raz. Gdy ten sam produkt może pojawić się w kilku osobnych pozycjach, lepszy będzie numer pozycji albo odrębne id.

Klucz obcy odwołuje się do klucza głównego lub innego odpowiedniego klucza unikalnego tabeli nadrzędnej. Nie musi mieć takiej samej nazwy jak kolumna, na którą wskazuje. Typy i definicje kolumn muszą być zgodne z wymaganiami silnika.

Trzy podstawowe rodzaje powiązań

1:1 — jeden do jednego

Jeden rekord pasuje najwyżej do jednego po drugiej stronie. Przykład: pracownik i opcjonalny profil rozszerzony. Klucz obcy profilu powinien mieć UNIQUE albo być jednocześnie jego kluczem głównym.

1:N — jeden do wielu

Klient może mieć wiele zamówień, ale każde zamówienie należy do jednego klienta. Klucz obcy umieszczamy po stronie N: zamowienia.klient_id.

N:M — wiele do wielu

Zamówienie może zawierać wiele produktów, a produkt może wystąpić w wielu zamówieniach. W modelu relacyjnym tworzymy tabelę pośrednią pozycje. Zawiera dwa klucze obce oraz atrybuty powiązania, np. ilość i cenę z chwili sprzedaży.

Diagram przykładowego sklepu

Encja / tabelaAtrybuty kluczoweZnaczenie powiązania
klienciPK idJeden klient → zero lub wiele zamówień
zamowieniaPK id; FK klient_idKażde zamówienie → dokładnie jeden klient
pozycjePK (zamowienie_id, produkt_id); oba pola FKKażda pozycja → jedno zamówienie i jeden produkt
produktyPK idJeden produkt → zero lub wiele pozycji
klienci       zamowienia       pozycje          produkty
  [PK id]  1 ── 0..N [FK klient_id]
                 [PK id] 1 ── 0..N [FK zamowienie_id]
                                 [FK produkt_id] N ── 1 [PK id]

PK oznacza klucz główny, a FK — obcy. Różne notacje graficzne używają cyfr, kółek, kresek lub „kurzej stopki”. Zawsze sprawdź legendę. Diagram powinien wskazywać nie tylko maksimum (1/N), lecz także minimum (0/1).

Obowiązkowość i integralność referencyjna

Jeżeli zamówienie musi mieć klienta, klient_id otrzymuje NOT NULL oraz FOREIGN KEY. Sam FOREIGN KEY może dopuszczać NULL, jeśli kolumna nie ma NOT NULL — wtedy brak powiązania jest możliwy.

Załóżmy, że istnieją klienci o id 1, 2 i 3. Wstawienie zamówienia z klient_id = 99 narusza integralność referencyjną. Nie naprawiaj tego wyłączaniem ograniczeń. Ustal poprawnego klienta albo najpierw utwórz brakujący rekord, jeśli rzeczywiście powinien istnieć.

Ważna granica: klucz obcy w pozycjach nie gwarantuje, że każde zamówienie ma przynajmniej jedną pozycję. Taka reguła może wymagać logiki aplikacji lub dodatkowych mechanizmów. Diagram biznesowy i same ograniczenia FK nie zawsze opisują dokładnie to samo.

Co się stanie przy usunięciu rodzica?

Politykę ustalasz świadomie. NO ACTION/RESTRICT blokują operację naruszającą odwołania, ale szczegóły czasu sprawdzania zależą od systemu. CASCADE usuwa lub aktualizuje zależne rekordy. SET NULL pozostawia rekord zależny bez powiązania, jeśli kolumny dopuszczają NULL. Nie wybieraj CASCADE tylko dlatego, że operacja przestaje zgłaszać błąd.

Przykład wspólnej intencji w MySQL/MariaDB, SQL Server i Oracle:

FOREIGN KEY (klient_id) REFERENCES klienci(id)

To fragment definicji tabeli, a nie samodzielne polecenie. W Access podobne powiązanie można utworzyć w oknie Relacje i włączyć wymuszanie więzów integralności. Nie każda kombinacja akcji referencyjnych ma identyczną składnię i dostępność w każdym silniku. Szczegóły CREATE TABLE poznamy osobno.

Ćwiczenia projektowe

  1. Uczeń zapisuje się na wiele kursów, a kurs ma wielu uczniów. Zaproponuj tabele i klucze.
  2. Każdy pracownik może mieć najwyżej jeden identyfikator wejściowy, a identyfikator należy do najwyżej jednego pracownika. Jak wymusisz 1:1?
  3. Czy usunięcie klienta powinno automatycznie usuwać historię opłaconych zamówień? Uzasadnij decyzję projektową.
Rozwiązania
  1. uczniowie(id PK), kursy(id PK), zapisy(uczen_id FK, kurs_id FK). Jeśli powtórne zapisy nie są osobnymi zdarzeniami, para FK może tworzyć PK. Data zapisu jest atrybutem tabeli zapisy, a nie ucznia ani kursu.
  2. W tabeli identyfikatorów dodaj pracownik_id jako FK z UNIQUE. NOT NULL ustal zależnie od tego, czy nieprzypisany identyfikator jest dozwolony. Przy alternatywnym projekcie pracownik_id może być PK i FK jednocześnie.
  3. Zwykle historia powinna pozostać. Możliwe są blokada usunięcia, archiwizacja albo przemyślana anonimizacja. To decyzja biznesowa i prawna, nie automatyczna zaleta CASCADE. Nie modyfikujemy prawdziwych danych w tym ćwiczeniu.

Zakres INF.03.4: projekt relacyjny i diagram E/R. Podstawa ORE; MySQL — ograniczenia FOREIGN KEY. Dalej: normalizacja i anomalie.

Przewijanie do góry