Integralność bazy MySQL

Integralność bazy danych MySQL — co to jest?

Integralność bazy danych oznacza poprawność, spójność i logiczną zgodność danych przechowywanych w bazie. Dane powinny być kompletne, nieuszkodzone i zgodne z regułami narzuconymi przez strukturę bazy oraz logikę biznesową.

W kontekście MySQL (i innych systemów relacyjnych) integralność realizuje się przez ograniczenia (constraints), klucze (główne, obce, unikalne) i transakcje zgodne z zasadami ACID. To kluczowy temat na egzaminie z kwalifikacji INF.03 (Tworzenie i administrowanie stronami i aplikacjami internetowymi oraz bazami danych) — pojawia się w wielu wariantach pytań.

Trzy rodzaje integralności

W teorii relacyjnych baz danych wyróżnia się trzy główne typy integralności:

1. Integralność encji (jednostek)

Każdy wiersz w tabeli musi być unikalny i jednoznacznie identyfikowalny. Realizuje się ją przez klucz główny (PRIMARY KEY), który nie może być pusty (NULL) ani duplikowany.

CREATE TABLE klienci (
    id INT PRIMARY KEY,
    imie VARCHAR(50)
);

Każdy klient ma unikalne id — to gwarancja integralności encji.

2. Integralność referencyjna (więzy integralności)

Wartości kluczy obcych w jednej tabeli muszą odpowiadać istniejącym wartościom kluczy głównych w tabeli powiązanej (lub być NULL).

To znaczy: nie można dodać zamówienia odwołującego się do nieistniejącego klienta. Realizuje się przez FOREIGN KEY:

CREATE TABLE zamowienia (
    id INT PRIMARY KEY,
    klient_id INT,
    FOREIGN KEY (klient_id) REFERENCES klienci(id)
);

Integralność referencyjna uniemożliwia powstawanie „osieroconych" rekordów — np. zamówień bez istniejącego klienta. Więcej w haśle integralność referencyjna.

3. Integralność dziedziny (domeny)

Wartości w kolumnach muszą być zgodne z typem danych i dodatkowymi ograniczeniami:

• NOT NULL — pole nie może być puste,
• UNIQUE — wartość niepowtarzalna w kolumnie,
• CHECK — warunek logiczny (np. wiek >= 0),
• DEFAULT — wartość domyślna.

CREATE TABLE produkty (
    nazwa VARCHAR(100) NOT NULL,
    kod VARCHAR(20) UNIQUE,
    cena DECIMAL(10,2) CHECK (cena >= 0)
);

Klucz główny (PRIMARY KEY)

Klucz główny to pole (lub zestaw pól) jednoznacznie identyfikujące każdy wiersz w tabeli. W instrukcji CREATE TABLE użycie klauzuli PRIMARY KEY przy deklaracji pola sprawia, że dane pole staje się:

• unikalne (nie może mieć duplikatów),
• nie-NULL (musi zawsze mieć wartość),
• automatycznie indeksowane (przyspieszone wyszukiwanie),
• podstawą do tworzenia relacji z innymi tabelami.

CREATE TABLE uzytkownicy (
    id INT PRIMARY KEY AUTO_INCREMENT,
    login VARCHAR(50)
);

Klucz główny jest niezbędny w każdej tabeli, żeby baza działała poprawnie i spójnie. Bez niego nie ma jednoznacznej identyfikacji wierszy. Więcej w haśle klucz główny SQL.

Atrybut UNIQUE

Klauzula UNIQUE w CREATE TABLE sprawia, że w kolumnie nie mogą występować powtórzenia wartości — każda musi być niepowtarzalna w obrębie tabeli.

Różnice z PRIMARY KEY:

Typowe użycie: UNIQUE na polach typu email, PESEL, kod produktu — wartości, które muszą być unikalne, ale nie są kluczem głównym.

Klucz obcy (FOREIGN KEY)

Klucz obcy to pole (lub pola) odwołujące się do klucza głównego innej tabeli. Realizuje relacje między tabelami i wymusza integralność referencyjną.

CREATE TABLE zamowienia (
    id INT PRIMARY KEY,
    klient_id INT,
    FOREIGN KEY (klient_id) REFERENCES klienci(id)
);

Klucz obcy na wielu kolumnach (złożony)

Czasem klucz obcy obejmuje więcej niż jedną kolumnę. Składnia wtedy wygląda tak:

CREATE TABLE szczegoly_zamowienia (
    rok INT,
    numer INT,
    pozycja INT,
    FOREIGN KEY (rok, numer) REFERENCES zamowienia(rok, numer)
);

Aby utworzyć klucz obcy na wielu kolumnach podczas tworzenia tabeli, używa się FOREIGN KEY (col1, col2) REFERENCES …(col1, col2) — wymienia się kolumny po przecinku w nawiasach. Więcej w haśle klucz obcy wielokolumnowy oraz klucz obcy SQL.

Klauzula tworząca relacje

Powiązania między tabelami w MySQL ustala się klauzulą FOREIGN KEY (z REFERENCES). To podstawowa składnia do realizacji relacji jeden-do-wielu i wiele-do-wielu (przez tabelę pośrednią).

Relacja jeden-do-wielu

W typowej relacji jeden-do-wielu (1 : N):

• jedna strona to „jeden" (np. Klienci — każdy klient istnieje raz),
• druga strona to „wiele" (np. Zamówienia — jeden klient może mieć wiele zamówień).

Aby ją utworzyć:

1. W tabeli po stronie „jeden" (Klienci) — klucz główny (id).
2. W tabeli po stronie „wiele" (Zamówienia) — klucz obcy wskazujący na klucz główny strony „jeden" (klient_id REFERENCES klienci(id)).

Po stronie „wiele" zawsze trzeba zdefiniować klucz obcy. To jeden z najczęstszych wzorców pytań CKE.

ACID — gwarancja transakcji

ACID to akronim opisujący cztery właściwości transakcji zapewniające integralność bazy:

W MySQL ACID gwarantuje silnik InnoDB (domyślny). Starszy silnik MyISAM nie obsługuje transakcji ACID.

Integralność w MS Access

W programie Microsoft Access integralność realizuje się przez więzy integralności (ang. referential integrity) — opcja zaznaczana przy tworzeniu relacji między tabelami. Po włączeniu Access wymusza:

• aby klucze obce miały istniejące wartości w tabeli nadrzędnej,
• automatyczne kaskadowe aktualizacje (Cascade Update) lub usuwanie (Cascade Delete), jeśli włączone.

Aby zapewnić integralność danych w bazie Microsoft Access, należy zastosować więzy integralności. Więcej w haśle więzy integralności w Access.

Indeksy a wydajność

Aby zoptymalizować operacje na bazie danych (przyspieszyć wyszukiwanie i sortowanie), warto tworzyć indeksy na polach często wyszukiwanych lub sortowanych.

CREATE INDEX idx_nazwa ON produkty (nazwa);

Indeksy:

• przyspieszają zapytania SELECT, JOIN, ORDER BY,
• spowalniają operacje INSERT, UPDATE, DELETE (każda zmiana wymaga aktualizacji indeksu),
• są automatycznie tworzone dla PRIMARY KEY i UNIQUE,
• nie powinny być tworzone dla wszystkich kolumn — tylko tych intensywnie używanych do wyszukiwania.

Co może naruszyć integralność?

Do problemów z integralnością mogą prowadzić:

• awarie serwera lub nagłe wyłączenia zasilania,
• błędy aplikacji zapisującej dane,
• uszkodzenie plików tabel (np. po awarii dysku),
• niepoprawne operacje administracyjne (manualne edycje plików bazy),
• brak kontroli relacji (brak kluczy obcych),
• brak walidacji danych w aplikacji.

Sprawdzanie i naprawa tabel w MySQL

Stan tabeli sprawdza się poleceniem:

CHECK TABLE nazwa_tabeli;

Jeśli tabela jest uszkodzona, można spróbować ją naprawić:

REPAIR TABLE nazwa_tabeli;

⚠️ Polecenie REPAIR TABLE działa dla silnika MyISAM. Dla InnoDB stosuje się inne metody (np. odzyskanie z dziennika InnoDB lub przywrócenie z kopii zapasowej). Więcej w haśle REPAIR TABLE MySQL.

Co NIE naprawia uszkodzonej bazy

W pytaniach typu „które działanie NIE przyczyni się do naprawy uszkodzonej bazy" typowymi błędnymi odpowiedziami (czyli czynnościami, które nie pomagają) są m.in.:

• dodanie nowego indeksu do uszkodzonej tabeli,
• utworzenie nowego użytkownika,
• uruchomienie zapytania SELECT na uszkodzonej tabeli,
• dodanie kolumny do tabeli z błędami.

Naprawą jest natomiast: REPAIR TABLE, przywrócenie z backupu, użycie narzędzi typu mysqlcheck, odzyskanie z dzienników InnoDB.

Integralność a kopia zapasowa (backup)

Czynnością zalecaną przed wykonaniem kopii bezpieczeństwa danych w bazie MySQL jest sprawdzenie integralności bazy oraz ewentualna jej naprawa.

Powód: backup uszkodzonej lub niespójnej bazy może okazać się bezużyteczny przy odtwarzaniu — kopia zachowa błędne dane, a po przywróceniu problem się powtórzy.

Typowy schemat:

1. CHECK TABLE wszystkich tabel,
2. REPAIR TABLE dla zgłaszających błędy,
3. Wykonanie kopii zapasowej (mysqldump lub xtrabackup),
4. Weryfikacja kopii (próbne przywrócenie).

Częste pomyłki — nie myl tego!

• PRIMARY KEY ≠ UNIQUE — PK jest tylko jeden, nie może być NULL i jest podstawą relacji. UNIQUE może być wiele, może być NULL.
• klucz główny ≠ klucz obcy — PK identyfikuje wiersz w tabeli, FK łączy go z innym wierszem w innej tabeli.
• klucz obcy umieszcza się po stronie „wiele" — w relacji 1:N to strona „wiele" ma kolumnę FK.
• integralność referencyjna ≠ integralność encji — referencyjna o relacjach między tabelami, encji o unikalności wierszy w jednej tabeli.
• ACID to nie kwas — to akronim własności transakcji: Atomicity, Consistency, Isolation, Durability.
• REPAIR TABLE nie zawsze zadziała — dla InnoDB nie ma takiej metody; trzeba użyć innych mechanizmów.
• indeks ≠ klucz — indeks to struktura przyspieszająca dostęp, klucz to ograniczenie integralności (klucz automatycznie tworzy indeks, indeks nie zawsze jest kluczem).
• „więzy integralności" w Accessie = FOREIGN KEY z ON UPDATE/DELETE w MySQL — to ten sam mechanizm w innym narzędziu.
• kopia zapasowa NIE naprawia integralności — to ratunek, ale nie metoda zapewnienia spójności bieżącej bazy.

Najważniejsze do zapamiętania

Integralność bazy danych to poprawność i spójność danych. Trzy typy: encji (PRIMARY KEY), referencyjna (FOREIGN KEY) i dziedziny (NOT NULL, UNIQUE, CHECK). Klucz główny PRIMARY KEY sprawia, że pole jest unikalne i nie-NULL. UNIQUE zapewnia unikalność, ale dopuszcza NULL. Relacje robi klauzula FOREIGN KEY ... REFERENCES — w relacji 1:N klucz obcy jest po stronie „wiele". ACID = Atomicity, Consistency, Isolation, Durability — własności transakcji. Przed backupem zawsze sprawdzaj integralność: CHECK TABLE → ewentualnie REPAIR TABLE → dopiero potem kopia. W Accessie integralność włącza się jako „więzy integralności" przy tworzeniu relacji.