Temat: Więzy propagacji w bazach danych MySQL

Cel lekcji:

Celem tej lekcji jest zrozumienie, czym są więzy propagacji w MySQL oraz jak działają w kontekście relacyjnych baz danych. Więzy propagacji to zasady, które określają, w jaki sposób zmiany w danych nadrzędnych wpływają na dane podrzędne. W MySQL można skonfigurować różne akcje, takie jak CASCADE, SET NULL, RESTRICT, i NO ACTION, które kontrolują sposób propagacji zmian.

---

1. Więzy propagacji: wprowadzenie do kluczy obcych

Opis: Klucze obce pozwalają łączyć rekordy między tabelami, zachowując integralność referencyjną. Więzy propagacji określają, co dzieje się z rekordami podrzędnymi, gdy rekord nadrzędny zostanie zaktualizowany lub usunięty. Domyślnie MySQL stosuje akcję RESTRICT, która uniemożliwia usunięcie lub aktualizację rekordu nadrzędnego, jeśli istnieją powiązane rekordy podrzędne.

Przykład:

CREATE TABLE Klienci (
    klient_id INT PRIMARY KEY,
    nazwa_klienta VARCHAR(100)
);

CREATE TABLE Zamowienia (
    zamowienie_id INT PRIMARY KEY,
    klient_id INT,
    FOREIGN KEY (klient_id) REFERENCES Klienci(klient_id) ON DELETE RESTRICT ON UPDATE RESTRICT
);

Ćwiczenie 1: Utwórz tabele Klienci i Zamowienia, gdzie klient_id w tabeli Zamowienia jest kluczem obcym odnoszącym się do klient_id w tabeli Klienci. Spróbuj usunąć klienta, który ma powiązane zamówienia, i zobacz, jak więzy RESTRICT uniemożliwiają usunięcie rekordu nadrzędnego.

---

2. Więzy propagacji: akcja CASCADE

Opis: Akcja CASCADE automatycznie propaguje zmiany z tabeli nadrzędnej do tabeli podrzędnej. Na przykład, jeśli rekord nadrzędny zostanie usunięty, wszystkie powiązane rekordy podrzędne również zostaną usunięte. CASCADE jest przydatny, gdy powiązane dane nie mają sensu bez rekordu nadrzędnego.

Przykład:

CREATE TABLE Projekty (
    projekt_id INT PRIMARY KEY,
    nazwa_projektu VARCHAR(100)
);

CREATE TABLE Zadania (
    zadanie_id INT PRIMARY KEY,
    projekt_id INT,
    opis VARCHAR(255),
    FOREIGN KEY (projekt_id) REFERENCES Projekty(projekt_id) ON DELETE CASCADE
);

Ćwiczenie 2: Utwórz tabele Projekty i Zadania, gdzie projekt_id w tabeli Zadania jest kluczem obcym odnoszącym się do projekt_id w tabeli Projekty. Dodaj rekordy do obu tabel, a następnie usuń projekt, który ma przypisane zadania. Zobacz, jak akcja CASCADE automatycznie usuwa powiązane zadania.

---

3. Więzy propagacji: akcja SET NULL

Opis: Akcja SET NULL ustawia wartość NULL w kolumnie klucza obcego w tabeli podrzędnej, jeśli powiązany rekord w tabeli nadrzędnej zostanie usunięty lub zaktualizowany. Dzięki temu powiązane rekordy nie są usuwane, ale tracą swoją relację z rekordem nadrzędnym.

Przykład:

CREATE TABLE Kategorie (
    kategoria_id INT PRIMARY KEY,
    nazwa_kategorii VARCHAR(100)
);

CREATE TABLE Produkty (
    produkt_id INT PRIMARY KEY,
    nazwa VARCHAR(100),
    kategoria_id INT,
    FOREIGN KEY (kategoria_id) REFERENCES Kategorie(kategoria_id) ON DELETE SET NULL
);

Ćwiczenie 3: Utwórz tabele Kategorie i Produkty, gdzie kategoria_id w tabeli Produkty jest kluczem obcym odnoszącym się do kategoria_id w tabeli Kategorie. Dodaj rekordy do obu tabel, a następnie usuń jedną kategorię. Zobacz, jak akcja SET NULL ustawia wartość NULL w kolumnie kategoria_id dla produktów, które były powiązane z usuniętą kategorią.

---

4. Więzy propagacji: akcja NO ACTION

Opis: Akcja NO ACTION oznacza brak działania. Jest podobna do RESTRICT, ale nie wymusza natychmiastowego sprawdzenia integralności, co oznacza, że jeśli rekord nadrzędny zostanie zmodyfikowany lub usunięty, MySQL pozwoli na wykonanie operacji, dopóki integralność danych nie zostanie naruszona. NO ACTION jest stosowany najczęściej, gdy logika integralności jest zarządzana przez aplikację.

Przykład:

CREATE TABLE Dostawcy (
    dostawca_id INT PRIMARY KEY,
    nazwa_dostawcy VARCHAR(100)
);

CREATE TABLE Produkty (
    produkt_id INT PRIMARY KEY,
    nazwa VARCHAR(100),
    dostawca_id INT,
    FOREIGN KEY (dostawca_id) REFERENCES Dostawcy(dostawca_id) ON DELETE NO ACTION
);

Ćwiczenie 4: Utwórz tabele Dostawcy i Produkty, gdzie dostawca_id w tabeli Produkty jest kluczem obcym odnoszącym się do dostawca_id w tabeli Dostawcy. Spróbuj usunąć dostawcę, który ma przypisane produkty. Obserwuj, jak NO ACTION uniemożliwia usunięcie dostawcy, chroniąc integralność danych.

---

3IG1 Drive

Podsumowanie

Więzy propagacji są kluczowym elementem zapewnienia integralności danych w relacyjnych bazach danych. W zależności od sytuacji możemy wybrać różne akcje (RESTRICT, CASCADE, SET NULL, NO ACTION) w zależności od tego, jaki poziom propagacji zmian jest odpowiedni dla struktury danych i wymagań biznesowych.