1. Wprowadzenie

W poprzedniej lekcji nauczyliśmy się podstaw dotyczących polecenia CREATE TABLE, typów danych i podstawowych ograniczeń. W tej lekcji pogłębimy temat, koncentrując się na kluczach obcych (FOREIGN KEY) oraz dodatkowych możliwościach związanych z tworzeniem tabel.

Po tej lekcji będziesz potrafił:

• Tworzyć relacje między tabelami za pomocą kluczy obcych
• Definiować zasady usuwania i aktualizacji powiązanych danych
• Używać domyślnych wartości dla kolumn
• Tworzyć tabele tymczasowe i jeśli nie istniejące
• Używać CHECK do walidacji danych

2. Tworzenie kluczy obcych i relacji między tabelami

Klucz obcy – FOREIGN KEY

Klucz obcy to kolumna lub zbiór kolumn, które wskazują na klucz główny innej tabeli. Zapewnia to integralność danych, ponieważ wartości w kolumnie klucza obcego muszą istnieć w tabeli nadrzędnej.

Przykład tworzenia tabeli z kluczem obcym:

CREATE TABLE klienci (
    id INT PRIMARY KEY AUTO_INCREMENT,
    imie VARCHAR(50) NOT NULL,
    nazwisko VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

Teraz tworzymy tabelę zamowienia, która zawiera klucz obcy id_klienta, wskazujący na id w tabeli klienci:

CREATE TABLE zamowienia (
    id_zamowienia INT PRIMARY KEY AUTO_INCREMENT,
    id_klienta INT NOT NULL,
    produkt VARCHAR(100) NOT NULL,
    kwota DECIMAL(10,2) NOT NULL,
    data_zamowienia DATE NOT NULL,
    FOREIGN KEY (id_klienta) REFERENCES klienci(id)
);

Dzięki temu każde zamówienie będzie powiązane z istniejącym klientem.

ON DELETE i ON UPDATE – zachowanie przy zmianach

Co się stanie, gdy spróbujemy usunąć klienta, który ma już przypisane zamówienia? Domyślnie MySQL nie pozwoli na usunięcie takiego rekordu. Możemy jednak określić, co ma się stać z danymi zależnymi, używając ON DELETE i ON UPDATE.

• ON DELETE CASCADE – jeśli usuniemy klienta, wszystkie jego zamówienia również zostaną usunięte
• ON DELETE SET NULL – jeśli usuniemy klienta, pole id_klienta w tabeli zamowienia zostanie ustawione na NULL
• ON DELETE RESTRICT – nie pozwala usunąć klienta, jeśli istnieją jego zamówienia

Przykład:

CREATE TABLE zamowienia (
    id_zamowienia INT PRIMARY KEY AUTO_INCREMENT,
    id_klienta INT,
    produkt VARCHAR(100) NOT NULL,
    kwota DECIMAL(10,2) NOT NULL,
    data_zamowienia DATE NOT NULL,
    FOREIGN KEY (id_klienta) REFERENCES klienci(id) ON DELETE CASCADE
);

Teraz, gdy usuniemy klienta, wszystkie jego zamówienia także zostaną usunięte.

CHECK – walidacja wartości w kolumnach

MySQL obsługuje ograniczenie CHECK, które pozwala sprawdzić, czy wartości spełniają określone warunki.

Przykład:

CREATE TABLE produkty (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nazwa VARCHAR(100) NOT NULL,
    cena DECIMAL(10,2) NOT NULL CHECK (cena > 0),
    ilosc INT NOT NULL CHECK (ilosc >= 0)
);

Dzięki temu nie możemy dodać produktu z ujemną ceną lub ujemną ilością.

DEFAULT – domyślne wartości

Możemy ustawić domyślne wartości dla kolumn.

CREATE TABLE pracownicy (
    id INT PRIMARY KEY AUTO_INCREMENT,
    imie VARCHAR(50) NOT NULL,
    stanowisko VARCHAR(50) NOT NULL DEFAULT 'Nowy pracownik',
    data_zatrudnienia DATE DEFAULT CURRENT_DATE
);

Jeśli nie podamy wartości dla stanowisko, automatycznie zostanie ustawione na "Nowy pracownik", a data zatrudnienia będzie równa bieżącej dacie.

IF NOT EXISTS – unikanie błędów przy ponownym tworzeniu tabeli

Jeśli nie jesteśmy pewni, czy tabela już istnieje, możemy użyć IF NOT EXISTS, aby uniknąć błędu:

CREATE TABLE IF NOT EXISTS klienci (
    id INT PRIMARY KEY AUTO_INCREMENT,
    imie VARCHAR(50) NOT NULL,
    nazwisko VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

Tabele tymczasowe – TEMPORARY

Czasami potrzebujemy tabeli tylko na czas jednej sesji. Taka tabela znika po zamknięciu połączenia z bazą danych.

CREATE TEMPORARY TABLE tymczasowa (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nazwa VARCHAR(50) NOT NULL
);

3. Ćwiczenia

Ćwiczenie 1: Tworzenie tabeli z kluczem obcym
Utwórz tabelę klienci z kolumnami:

• id (klucz główny, INT, AUTO_INCREMENT)
• imie (VARCHAR(50))
• nazwisko (VARCHAR(50))
• email (VARCHAR(100), musi być unikalny)

Następnie utwórz tabelę zamowienia, w której:

• id_zamowienia (klucz główny, INT, AUTO_INCREMENT)
• id_klienta (klucz obcy, INT)
• produkt (VARCHAR(100))
• kwota (DECIMAL(10,2))

Połącz tabelę zamowienia z tabelą klienci kluczem obcym.

Ćwiczenie 2: ON DELETE i ON UPDATE
Stwórz tabelę pracownicy oraz tabelę projekty, gdzie id_pracownika w projekty będzie kluczem obcym do tabeli pracownicy. Ustaw:

• ON DELETE SET NULL
• ON UPDATE CASCADE

Sprawdź, co się stanie, gdy zmienisz ID pracownika lub go usuniesz.

Ćwiczenie 3: CHECK i DEFAULT
Utwórz tabelę produkty z kolumnami:

• id (klucz główny)
• nazwa (VARCHAR(100))
• cena (DECIMAL(10,2), musi być większa od 0)
• ilosc (INT, minimum 0)
• dostepnosc (BOOLEAN, domyślnie 1)

Ćwiczenie 4: IF NOT EXISTS
Spróbuj utworzyć tabelę magazyn tak, by nie powstał błąd, jeśli już istnieje.

Ćwiczenie 5: Tworzenie tabeli tymczasowej
Napisz kod SQL tworzący tymczasową tabelę raport, która zawiera kolumny:

• id (klucz główny)
• nazwa_produktu (VARCHAR(100))
• ilosc_sprzedana (INT)

Sprawdź, czy tabela nadal istnieje po ponownym połączeniu z MySQL.

Ćwiczenie 6: phpMyAdmin – klucze obce

1. W phpMyAdmin utwórz tabelę klienci z kluczem głównym id.
2. Stwórz tabelę zamowienia, a następnie dodaj klucz obcy id_klienta.
3. Skonfiguruj ON DELETE CASCADE.
4. Przetestuj działanie, usuwając klienta.

4. Podsumowanie

W tej lekcji nauczyłeś się zaawansowanych aspektów CREATE TABLE, w tym:

• Tworzenia kluczy obcych i relacji
• Ustawiania zachowania przy usuwaniu i aktualizacji danych
• Stosowania walidacji CHECK
• Definiowania wartości domyślnych DEFAULT
• Tworzenia tabel tymczasowych TEMPORARY

Teraz możesz projektować bazy danych w sposób bardziej elastyczny i zgodny z zasadami integralności danych.