INF.03 · Bazy danych i SQL

Więzy integralności: reguły, które pilnują danych za Ciebie

Gdy próbujesz założyć konto na zajęty adres e-mail, dostajesz błąd. Aplikacja może to sprawdzać, ale najpewniej pilnuje tego sama baza. Bez więzów baza pozwoliłaby dodać zamówienie nieistniejącego klienta albo produkt z ceną −50 zł. Więzy integralności to reguły zapisane w strukturze tabeli, które odrzucają dane bez sensu.

  • około 35 minut
  • PK · FK · UNIQUE · NOT NULL · CHECK · DEFAULT
  • bazy: szkola_relacje, sklep_muzyczny

Po tej lekcji: rozpoznasz więz po komunikacie błędu, dobierzesz właściwy więz do reguły biznesowej i napiszesz tabelę z kompletem ograniczeń.

1 · Przegląd

Każdy więz pilnuje czegoś innego

Więz to zasada przypisana do kolumny albo tabeli. Baza sprawdza ją przy każdym INSERT i UPDATE, a część także przy DELETE.

PRIMARY KEYklucz główny

Unikalny identyfikator rekordu. Nie może się powtórzyć ani być pusty.

Na przykład id_ucznia w tabeli Uczniowie.

FOREIGN KEYklucz obcy

Wartość musi istnieć w tabeli nadrzędnej. Chroni też przed usunięciem rekordu, który jest w użyciu.

Na przykład id_klasy w Uczniowie musi wskazywać istniejącą klasę.

UNIQUEbez powtórzeń

Wartość w kolumnie nie może się powtórzyć. W przeciwieństwie do klucza głównego może zawierać NULL.

Na przykład adres e-mail konta, numer legitymacji.

NOT NULLwartość obowiązkowa

Kolumna zawsze musi mieć wartość.

Na przykład nazwa produktu.

CHECKdowolny warunek

Sprawdza warunek logiczny i odrzuca rekordy, które go nie spełniają.

Na przykład cena > 0.

DEFAULTwartość domyślna

Ustawia wartość, gdy przy dodawaniu rekordu jej nie podasz.

Na przykład ilosc_na_stanie = 0.

2 · Spróbuj to złamać

Szkoła: co baza odrzuci, a co przyjmie?

Prawdziwa struktura szkoły z lekcji o relacjach: klasy, uczniowie i legitymacje (jedna legitymacja na ucznia). Wybieraj polecenia i patrz, który więz je zatrzymuje. Zauważ, że nazwa błędu zdradza, który więz zadziałał.

Ta interakcja wymaga włączonego JavaScriptu.

Usuwanie ma swoją kolejność: najpierw rekordy zależne (legitymacja), potem nadrzędne (uczeń). Klucz obcy nie pozwala zostawić „osieroconych” rekordów.

3 · Reguły dla wartości

Produkty: cena dodatnia, kod unikalny, stan domyślny

Tabela Produkty ma komplet ograniczeń z przykładu poniżej. Zobacz, co robi CHECK, co robi DEFAULT i dlaczego dwa produkty bez kodu się nie gryzą mimo UNIQUE.

Ta interakcja wymaga włączonego JavaScriptu.

UNIQUE pozwala na wiele wartości NULL, bo NULL oznacza „brak wartości”, a nie konkretną wartość, która mogłaby się powtórzyć. Klucz główny na to nie pozwala.

4 · Jak to zapisać

CREATE TABLE ze wszystkimi więzami

Tak wygląda tabela, z którą przed chwilą pracowałeś w piaskownicy. Każdy więz jest zapisany tam, gdzie opisuje kolumnę.

produkty.sqlsql
CREATE TABLE Produkty (
    id_produktu INT AUTO_INCREMENT PRIMARY KEY,
    nazwa VARCHAR(100) NOT NULL,
    cena DECIMAL(10,2) NOT NULL CHECK (cena > 0),
    kod_produktu VARCHAR(20) UNIQUE,
    ilosc_na_stanie INT DEFAULT 0
);
  1. NOT NULL CHECK (cena > 0) cena jest obowiązkowa i musi być dodatnia.
  2. UNIQUE bez NOT NULL kod może się nie pojawić, ale jeśli jest, nie może się powtórzyć.
  3. DEFAULT 0 nowy produkt bez podanego stanu zacznie od zera.

5 · Zanim się pomylisz

Trzy pomyłki z więzami

Rozwiń każdą z nich.

„Dodam więzy później”
Tak nie
CREATE TABLE Produkty (
    id_produktu INT,
    nazwa VARCHAR(100),
    cena DECIMAL(10,2)
);

Baza przyjmie produkt bez nazwy, z ceną −50 zł i dwa produkty z tym samym numerem. W praktyce więzów „na później” nikt nie dodaje, a błędne dane już siedzą w bazie.

Tak
CREATE TABLE Produkty (
    id_produktu INT AUTO_INCREMENT PRIMARY KEY,
    nazwa VARCHAR(100) NOT NULL,
    cena DECIMAL(10,2) NOT NULL CHECK (cena > 0)
);

Więzy dopisz od razu przy tworzeniu tabeli.

NOT NULL tam, gdzie wartość może nie istnieć
Tak nie
CREATE TABLE Projekty (
    id_projektu INT PRIMARY KEY,
    data_zakonczenia DATE NOT NULL
);

Projekt, który jeszcze trwa, nie ma daty zakończenia, więc nie da się go zapisać.

Tak
CREATE TABLE Projekty (
    id_projektu INT PRIMARY KEY,
    data_zakonczenia DATE
);

NOT NULL stosuj tylko do danych, które zawsze muszą istnieć.

Klucz obcy do tabeli, której jeszcze nie ma
Tak nie
CREATE TABLE Zamowienia (
    id_zamowienia INT PRIMARY KEY,
    id_klienta INT,
    FOREIGN KEY (id_klienta) REFERENCES Klienci(id_klienta)
);
-- tabeli Klienci jeszcze nie utworzono

Błąd: baza nie może otworzyć tabeli, do której wskazuje klucz obcy.

Tak
CREATE TABLE Klienci (...);
CREATE TABLE Zamowienia (...);

Tabela nadrzędna musi istnieć przed tabelą z kluczem obcym.

6 · Sprawdź sam siebie

Czy baza jest dobrze zabezpieczona

Dobrze zaprojektowana baza na egzaminie ma komplet kluczy głównych i obcych oraz podstawowe ograniczenia.

Podsumowanie

Co warto zapamiętać

  1. Więzy odrzucają błędne dane już przy zapisie.
  2. PRIMARY KEY to unikalność bez NULL, a UNIQUE pozwala na NULL.
  3. FOREIGN KEY pilnuje istnienia rekordu nadrzędnego i chroni przed jego usunięciem.
  4. CHECK sprawdza warunek, DEFAULT podstawia wartość, gdy jej nie podano.
  5. Tabele tworzysz i usuwasz w kolejności zgodnej z kluczami obcymi.

Dalej na swoim koncie

Rozgrzewki, quiz i zadania są w biurku ucznia.

Teoria jest otwarta dla wszystkich. Żeby rozwiązywać zadania, zapisywać szkice i widzieć swój postęp, zaloguj się.

Zaloguj się →
Źródła i dalsza nauka