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)
Pytanie!
OdpowiedzUsuńRozumiem, że instrukcja KEY `kraj` (`kraj`) tworzy się automatycznie po alter table... i jest to indeksowanie kolumny?