czwartek, 11 grudnia 2014

MySQL + InnoDB - Klucze obce

Serwer bazodanowy MySQL umożliwia korzystanie z wielu różnych silników tabel. Najbardziej popularne silniki tabel MySQL to MyIsam oraz InnoDB, choć silników obsługiwanych przez MySQL jest wiele, np. Blackhole, csv, Federated, Falcon, TokuDB, XtraDB, Connect. Niektóre z nich są mało popularne i służą jedynie do bardzo konkretnych zastosowań.

W niniejszym artykule zostaną opisane niektóre ciekawe cechy silnika InnoDB, sprawiające iż jest on pierwszego wyboru narzędziem w aplikacjach bazodanowych wymagających integralności danych oraz spójności i bezpieczeństwa transakcji.

W przeciwieństwie do tabel MyIsam, których mechanizmy zostały zaprojektowane z myślą o podstawowych aplikacjach internetowych i prostocie działania, tabele InnoDB, które przede wszystkim, w przeciwieństwie do MyIsam, są zgodne ze standardem http://en.wikipedia.org/wiki/ACID, zapewniają dużą wydajność, efektywność, wiarygodność transakcji oraz integralność danych dzięki fantastycznym mechanizmom transakcyjnym, kluczom obcym, zaawansowanemu mechanizmowi blokowania tabel na poziomie wiersza jak również mechanizmom automatycznego naprawiania tabel na podstawie logów serwera.

Tabele InnoDB są standardowo obsługiwane przez serwer MySQL. Od wersji serwera MySQL 5.5 silnik InnoDB jest domyślnym silnikiem tabeli. Korzystając jednak z poprzednich wersji serwera, w których domyślnym silnikiem tabeli jest MyIsam, aby utworzyć tabele InnoDB, należy określić ich silnik, jak w poniższym przykładzie, który przyda się również do omówienia kluczy obcych oraz mechanizmów transakcyjnych InnoDB:

mysql> create table klienci (
-> id smallint unsigned not null auto_increment primary key,
-> nazwa varchar(40) not null,
-> imie varchar(20) not null,
-> nazwisko varchar(20) not null,
-> adres varchar(20) not null,
-> kod_pocztowy varchar(6) not null,
-> miejscowosc varchar(40) not null,
-> kraj tinyint unsigned not null ) engine=InnoDb charset utf8;
Query OK, 0 rows affected (0.01 sec)


W niniejszym artykule omówię klucze obce, które zapewniają integralność danych przechowywanych w tabelach InnoDB.

Klucz obcy zapewnia spójność danych przechowywanych w polu tabeli, do której zostaje on przypisany, z wartością klucza znajdującego się w polu tabeli, do której klucz obcy się odnosi.

Aby umożliwić większą przejrzystość pojęcia, posłużę się prostymi przykładami.

Przedstawię teraz metodę dodawania kluczy obcych w przypadku istniejących już tabeli, w których dodawany klucz obcy ma zapewnić spójność danych.

Uprzednio utworzona tabela 'klienci' zawiera pole 'kraj':

`kraj` smallint(3) unsigned NOT NULL

Zdefiniowane w powyższy sposób pole, przyjmujące wartości numeryczne smallint, przewiduje istnienie osobnej tabeli, zawierającej klucze i nazwy państw:

mysql> create table kraje (
-> id tinyint unsigned not null auto_increment primary key,
-> nazwa varchar(30) not null,
-> kod char(2) ) engine=InnoDb charset utf8;
Query OK, 0 rows affected (0.01 sec)

Aby zapewnić spójność danych przechowywanych w tabelach 'klienci' i 'kraje' oraz umożliwić zapisanie w polu 'kraj' tabeli 'klienci' wyłącznie takich wartości, które istnieją również w polu 'id' tabeli 'kraje', należy do tabeli 'klienci' dodać klucz obcy odnoszący się do pola 'id' tabeli 'kraje', a można to uczynić w sposób następujący:

mysql> alter table klienci add foreign key(`kraj`) references kraje(`id`);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0

Powyżej przedstawiona metoda dodania klucza obcego jest skuteczna wyłącznie w okolicznościach gdy istniejąca już tabela 'klienci' jest pusta, czyli nie zawiera danych, lub gdy tabela 'klienci' zawiera już dane lecz w jej polu 'kraj' istnieją wyłącznie wartości obecne również w polu 'id' tabeli 'kraje'. Powyższa metoda dodania klucza obcego nie może się natomiast udać w przypadku istnienia w polu 'kraj' tabeli 'klienci' wartości nie istniejących w polu 'id' tabeli 'kraje'. W takim przypadku, podczas próby dodania wyżej przedstawionego klucza obcego, pojawia się następujące powiadomienie o błędzie:

mysql> alter table klienci add foreign key(`kraj`) references kraje(`id`);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`softlink/#sql-290_21a1`, CONSTRAINT `#sql-290_21a1_ibfk_1` FOREIGN KEY (`
kraj`) REFERENCES `kraje` (`id`))

Powyższy komunikat o błędzie jest spowodowany próbą dodania klucza obcego podczas obecności w polu 'kraj' tabeli 'klienci' wartości nie obecnych w polu 'id' tabeli 'kraje', czyli w sytuacji, w której przewidywana przez klucz obcy spójność danych a priori nie może się spełnić.

Po dodaniu klucza obcego można sprawdzić jego obecność:

mysql> show create table klienci;

Efektem zatwierdzenia powyższego polecenia wyświetla się:

CREATE TABLE `klienci` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`nazwa` varchar(40) NOT NULL,
`imie` varchar(20) NOT NULL,
`nazwisko` varchar(20) NOT NULL,
`adres` varchar(20) NOT NULL,
`kod_pocztowy` varchar(6) NOT NULL,
`miejscowosc` varchar(40) NOT NULL,
`kraj` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `kraj` (`kraj`),
CONSTRAINT `klienci_ibfk_1` FOREIGN KEY (`kraj`) REFERENCES `kraje` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |


Powyższe wiersze potwierdzają obecność prawidłowo dodanego klucza obcego.
Po dodaniu powyżej opisanego klucza obcego, w polu 'kraj' tabeli 'klienci' mogą być zapisywane wyłącznie wartości, które są również obecne w polu 'id' tabeli 'kraje', a próba zlekceważenia tej reguły spowoduje błąd i wyświetlenie odpowiedniego powiadomienia o błędzie:


mysql> insert into klienci values('','JAN','Jan','Kowalski','ul. Fajna, 1','00-001','Przypadkowe miasto',1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`softlink/klienci`, CONSTRAINT `klienci_ibfk_1` FOREIGN KEY (`kraj`) REFER
ENCES `kraje` (`id`))

Natomiast:

mysql> insert into kraje(nazwa,kod) values('Polska','PL');
Query OK, 1 row affected (0.00 sec)

mysql> select * from kraje;
+----+--------+------+
| id | nazwa | kod |
+----+--------+------+
| 1 | Polska | PL |
+----+--------+------+
1 row in set (0.00 sec)

mysql> insert into klienci values('','JAN','Jan','Kowalski','ul. Fajna, 1','00-001','Przypadkowe miasto',1);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from klienci;
+----+-------+------+----------+--------------+--------------+--------------------+------+
| id | nazwa | imie | nazwisko | adres | kod_pocztowy | miejscowosc | kraj |
+----+-------+------+----------+--------------+--------------+--------------------+------+
| 1 | JAN | Jan | Kowalski | ul. Fajna, 1 | 00-001 | Przypadkowe miasto | 1 |
+----+-------+------+----------+--------------+--------------+--------------------+------+
1 row in set (0.00 sec)

Brak komentarzy:

Prześlij komentarz