INF.03.4

Klucze obce — FOREIGN KEY

Lekcja 16 / 36 Blok IV ⏱ 40 min

🎯 Cele lekcji

🔗

1. Składnia FOREIGN KEY

-- Sposób 1: Jako ograniczenie tabeli (zalecany)
CREATE TABLE produkty (
  id_produktu  INT  AUTO_INCREMENT PRIMARY KEY,
  nazwa        VARCHAR(200) NOT NULL,
  id_kategorii INT,

  -- Definicja FK na końcu tabeli
  CONSTRAINT fk_produkt_kat
    FOREIGN KEY (id_kategorii)
    REFERENCES kategorie(id_kategorii)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);

-- Sposób 2: Bez nazwy ograniczenia (MySQL nada automatycznie)
FOREIGN KEY (id_kategorii) REFERENCES kategorie(id_kategorii);
CONSTRAINT fk_nazwa — nadanie nazwy ograniczeniu FK jest opcjonalne, ale zalecane. Ułatwia późniejsze zarządzanie (usuwanie FK przez ALTER TABLE DROP FOREIGN KEY fk_nazwa).

Warunki poprawnego FOREIGN KEY:

WarunekOpis
Zgodność typówTyp kolumny FK musi być zgodny z typem PK w tabeli nadrzędnej (np. INT i INT)
Tabela nadrzędna musi istniećTabela z PK musi być utworzona przed tabelą z FK
Silnik InnoDBOba silniki tabel muszą być InnoDB (MyISAM ignoruje FK)
Indeks na kolumnie FKMySQL automatycznie tworzy indeks na kolumnie FK (przyspiesza JOIN)
⚡

2. ON DELETE / ON UPDATE — akcje referencyjne

AkcjaON DELETE — po usunięciu rodzicaON UPDATE — po zmianie PK rodzicaKiedy używać?
CASCADE Usuwa powiązane rekordy dziecka Aktualizuje FK w rekordach dziecka Usuwanie kaskadowe (np. zamówienie i jego pozycje)
SET NULL Ustawia FK na NULL (kolumna musi być nullable) Ustawia FK na NULL Produkt istnieje po usunięciu kategorii
RESTRICT Blokuje usunięcie rodzica gdy ma dzieci Blokuje zmianę PK gdy ma dzieci Ochrona przed przypadkowym usunięciem (domyślne)
NO ACTION Jak RESTRICT (sprawdza po transakcji) Jak RESTRICT Standard SQL, w MySQL = RESTRICT
SET DEFAULT Ustawia wartość domyślną (rzadko używane) Ustawia wartość domyślną Nieobsługiwane przez InnoDB!
Najczęstsze kombinacje:
— ON DELETE CASCADE ON UPDATE CASCADE — dla pozycji zamówień (gdy usuniemy zamówienie, usuń pozycje)
— ON DELETE SET NULL ON UPDATE CASCADE — dla produktów (gdy usuniemy kategorię, produkt zostaje bez kategorii)
— ON DELETE RESTRICT ON UPDATE CASCADE — ochrona przed usunięciem
🏗️

3. Przykład: 3 powiązane tabele

-- 1. Tabela nadrzędna — tworzymy PIERWSZA
CREATE TABLE kategorie (
  id_kategorii INT          AUTO_INCREMENT PRIMARY KEY,
  nazwa        VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB;

-- 2. Tabela podrzędna — tworzymy PO kategoriach
CREATE TABLE produkty (
  id_produktu  INT            AUTO_INCREMENT PRIMARY KEY,
  nazwa        VARCHAR(200)   NOT NULL,
  cena         DECIMAL(10,2)  NOT NULL DEFAULT 0.00,
  id_kategorii INT,
  CONSTRAINT fk_prod_kat
    FOREIGN KEY (id_kategorii) REFERENCES kategorie(id_kategorii)
    ON DELETE SET NULL
    ON UPDATE CASCADE
) ENGINE=InnoDB;

-- 3. Tabela pośrednia zamówień — tworzymy OSTATNIA
CREATE TABLE pozycje_zamowien (
  id_pozycji   INT            AUTO_INCREMENT PRIMARY KEY,
  id_zamowienia INT           NOT NULL,
  id_produktu  INT            NOT NULL,
  ilosc        SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  cena_jedn    DECIMAL(10,2)  NOT NULL,  -- cena w momencie zamówienia!
  CONSTRAINT fk_poz_zam
    FOREIGN KEY (id_zamowienia) REFERENCES zamowienia(id_zamowienia)
    ON DELETE CASCADE,
  CONSTRAINT fk_poz_prod
    FOREIGN KEY (id_produktu) REFERENCES produkty(id_produktu)
    ON DELETE RESTRICT
) ENGINE=InnoDB;
Cena w pozycji zamówienia: przechowujemy cena_jedn w tabeli pośredniej (nie FK do aktualnej ceny produktu). To celowa redundancja — historyczna cena z momentu zamówienia musi być niezmieniona.
✏️

Zadania interaktywne

Zadanie 1Dodaj FK do podanej tabeli

Poniżej tabela pracownicy. Uzupełnij brakujący fragment definiujący klucz obcy do tabeli dzialy:

CREATE TABLE pracownicy (
  id_pracownika INT AUTO_INCREMENT PRIMARY KEY,
  imie          VARCHAR(50) NOT NULL,
  id_dzialu     INT NOT NULL,
  -- UZUPEŁNIJ FK TUTAJ
);
Zadanie 2Quiz: ON DELETE CASCADE

Masz tabelę zamowienia i pozycje_zamowien z ON DELETE CASCADE na FK do zamówień. Co się stanie gdy usuniesz zamówienie o id=5?

  • Tylko zamówienie zostanie usunięte, pozycje zostaną z FK=NULL
  • Operacja zostanie zablokowana — nie można usunąć zamówienia z pozycjami
  • Zamówienie i WSZYSTKIE jego pozycje zostaną automatycznie usunięte
  • Pozycje zostaną przeniesione do archiwum
Zadanie 3Uzupełnij CREATE TABLE z FK

Uzupełnij brakujące elementy w definicji klucza obcego:

CONSTRAINT fk_prac_dz
   (id_dzialu)
   dzialy(id_dzialu)
  ON DELETE
  ON UPDATE

📌 Zapamiętaj