Wielu programistów korzystających w swoich projektach z tandemu złożonego z PHP i MySQL poświęca swój czas na programową obsługę integralności danych znajdujących się w bazie. Jest oczywiście całkiem duże grono osób, które zupełnie nie zaprzątają sobie tym głowy. Śmietnik w bazie danych oczywiście nie zaszkodzi prostym małym projektom i może najwyżej zaszkodzić reputacji programisty tworzącemu tego typu serwisy. W przypadku rozbudowanych aplikacji w których baza danych składa się z dziesiątek tabel rozrastających się w trakcie życia projektu do rozmiarów liczonych w gigabajty, porządek jest już rzeczą niebagatelną i relatywnie wpływającą na szybkość działania witryny. Właśnie w takich przypadkach warto skorzystać z mechanizmów jakie oferują relacyjne bazy danych.

Kilka lat temu w MySQL domyślnym typem tabel był MyISAM i chociaż InnnoDB było wtedy już dostępne to jednak niewiele osób korzystało z tego rozwiązania. Szkoda, bo mimo pewnych wad, niewątpliwą zaletą InnoDB jest możliwość zrzucenia utrzymywania integralności danych na MySQL’a. Poświęcając odpowiednią ilość czasu na wykonanie prawidłowego projektu relacyjnej bazy danych opartej na tabelach typu InnoDB, nie będziemy tracić czasu na kodowanie odpowiednich metod utrzymujących integralność danych w bazie.

Jedną z podstawowych zalet mechanizmu składowania InnoDB, której nie posiada MyISAM to możliwość definiowania kluczy obcych w tabelach. W przypadku MyISAM klucze obce mogły być jedynie indeksowane, co oczywiście wpływało na na szybkość wykonywania zapytań jednak nie miało przełożenia na utrzymywanie integralności danych. Osobiście uważam, że w obecnej chwili poza wyjątkowymi sytuacjami praktycznie nie ma uzasadnienia by w swoich projektach wciąż kurczowo trzymać się MyISAM. Część osób w tym momencie powie, że InnoDB nie ma wyszukiwania pełno-tekstowego (Full-text search indexes) … no cóż od wersji 5.6.4 już ma, więc jeśli dysponujesz taką lub wyższą wersją MySQL, to wtedy pada ostatni bastion by nie używać InnoDB.

Warto zauważyć, że w przypadku istniejących tabel, zmiana mechanizmu składowania nie wymaga ponownego tworzenia tabel. Zmianę można dokonać wykonując poniższe zapytanie:

ALTER TABLE `nazwa_tabeli` ENGINE = InnoDB

Metodę definiowania kluczy obcych opiszę na podstawie przykładów opartych na poniższym schemacie ERD prostej bazy danych:

Baza składa się z dwóch tabel w których przechowywane są dane oraz jednej tabeli asocjacyjne, która je wiąże ze sobą. Po utworzeniu tabel i nadaniu odpowiednich indeksów na klucze można przejść do definiowania kluczy obcych.

ALTER TABLE `uzytkownik_typ` ADD FOREIGN KEY ( `id_uzytkownik` ) REFERENCES `uzytkownik` (`id_uzytkownik`) ON DELETE CASCADE ON UPDATE CASCADE ;

ALTER TABLE `uzytkownik_typ` ADD FOREIGN KEY ( `id_typ` ) REFERENCES `typ` (`id_typ`) ON DELETE CASCADE ON UPDATE CASCADE ;

I już! Dzięki tym prostym zabiegom uzyskaliśmy pełną integralność danych w bazie, oraz kaskadowe usuwanie rekordów. Oznacza to, że każdorazowe usunięcie rekordu z tabeli `typ` lub też `uzytkownik` spowoduje usunięcie wszystkich wystąpień kasowanego klucza obcego w tabeli `uzytkownik_typ`. Przykład:

DELETE FROM `uzytkownik` WHERE `uzytkownik`.`id_uzytkownik` = 1

Wykonanie powyższego zapytania spowoduje usunięcie z tabeli `uzytkownicy` rekordu o identyfikatorze 1. Mechanizmy bazy danych, dążące do utrzymania integralności danych w tabelach wykonaja jednak również usunięcie wszystkich typów przypisanych do kasowanego użytkownika. Usnięte zostaną wszystkie rekordy z tabeli `uzytkownik_typ` w których wartość klucza obcego `id_uzytkownik` są równe 1.

Analogiczna sytuacja będzie występować w przypadku zmiany wartości klucza głównego w tabeli, `uzytkownik` lub `typ`. Każda z takich zmian będzie kaskadowo przenoszona na tabelę asocjacyjną `uzytkownik_typ`.

Jedna uwaga odnośnie modyfikacji istniejących tabel. Sama zmiana mechanizmów składowania na InnoDB nie wymaga zachowania integralności, jednak zdefiniowanie kluczy obcych wymaga doprowadzenia tabel do porządku. Sytuacja gdy tabela będzie  zwiera rekordy w których występują klucze obce, których nie ma w macierzystej, spowoduje zablokowanie możliwości ich zdefiniowania. W takich przypadkach przed definiowaniem kluczy obcych, dane w tabeli muszą być w pełni uporządkowane względem pozostałych tabel. Musisz samodzielnie przeprowadzić integrację tabel.

Znaczniki: bazy danych, InnoDB, MyISAM, MySQL, PHP, programowanie