Danas nastavljamo sa započetom serijom tutorijala o retko korišćenim mogućnostima MySQL-a. U prošlom zapisu detaljno sam predstavio kako se u MySQL-u koriste transakcije. Danas ćemo se usresrediti na korišćenje “Spoljnih Ključeva i Referencijalnog Integriteta”.
Kao i kod transakcija, podrška za referencijalni integritet nije dostupna u standardnim MyISAM tabelama, već se moraju koristiti InnoDB tabele. Malo developera zna da je ova mogućnost dostupna još od verzije 3.23.44, koja se pojavila sada već davne 2001 godine. Za početak idemo malo sa teorijom, a posle ćemo preći na praktične primere.
U najkraćem, pod spoljnim ključem podrazumevamo polje u jednoj tabeli koje je u direktnoj vezi sa poljem (primarnim ključem) u drugoj tabeli. Pravilo “Referencijalnog Integriteta” kaže da spoljni ključ ne sme imati vrednost koja ne postoji u tabeli na koju referencira. Bez namere da mnogo teoretišem ovde (koga interesuje može da googla za više informacija), evo odmah i konkretnog primera.
Zamislite bazu mobilnih telefona, koja između ostalog sadrži tabelu sa proizvođačima i modelima telefona. Recimo nešto ovako:
Proizvodjaci --------------------- ID Proizvodjac 1. Nokia 2. Sony-Ericson 3. Samsung 4. Siemens Telefoni ------------------------------- ID. Model PID 1. 6600 1 2. 6630 1 3. 3650 1 4. p800 2 5. p900 2 ...
PID kolona u tabeli ‘Telefoni’ je u relaciji sa ID kolonom u tabeli ‘Proizvodjaci’, te kolona PID predstavlja tzv. “spoljni ključ” (foreign key). PID vrednosti u našem primeru odnose se na proizvođače smart phone-ova Nokia (6600, 6630 i 3650) kao i Sony-Ericson (p800, p900).
Da bi se održao takozvani “referencijalni integritet” baze, moramo voditi računa da vrednosti PID kolone tabele Telefoni sadrže samo one vrednosti koje postoje u ID koloni tabele Proizvodjaci. U slučaju brisanja proizvođača u prvoj tabeli, svi slogovi koji referenciraju na njih u tabeli telefona moraju takođe biti obrisani (tzv. cascade delete) ili setovani na NULL. Slično važi i za update i insert tj. ako update-ujemo ID u prvoj tabeli, spoljni ključ PID mora takođe biti updejtovan. Isto tako, prilikom inserta vrednosti u drugu tabelu moramo voditi računa da PID u drugoj već ima odgovarajući ID u prvoj tabeli. Ovo su stvari o kojima morate sami da brinete ako podrška za referencijalni integrtitet nije uključena u vaš RDBMS. Srećom, korišćenjem InnoDB tabela, brigu o njegovom održavanju prepuštamo samom MySQL-u.
Spoljni ključ se zadaje prilikom kreiranja same tabele. Sintaksa izgleda ovako:
[CONSTRAINT symbol]
FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
– CONSTRAINT symbol je opciono ime za ograničenje (constraint) za ovaj spoljni ključ.
– FOREIGN KEY označava kolonu koja predstavlja spoljni ključ
– REFERENCES tbl_name (index_col_name, …) je tabela i tačna kolona na koju spoljni ključ referencira
ON DELETE i ON UPDATE definišu akcije koje će biti preduzete od strane baze u slučaju da se obriše/updatuje neki slog u glavnoj tabeli. Moguće vrednosti su:
– RESTRICT brisanje slogova u glavnoj tabeli će biti odbijeno ako postoje spoljni ključevi koji refereniraju na njih
– CASCADE brisanje slogova u glavnoj tabeli će rezultovati brisanjem slogova koji referenciraju na njih u tabli sa spoljnim ključem
– SET NULL slično CASCADE-u osim što slogovi u child tabeli neće biti obrisani već će vrednost spoljnih ključeva biti setovana na NULL
Da bih demonstrirao ceo koncept, kreiraću bazu mobilnih telefona koju sam pominjao gore.
create table proizvodjaci(
id int unsigned primary key not null auto_increment,
proizvodjac varchar(255) not null unique
) engine = InnoDB;
create table telefoni(
id int unsigned primary key not null auto_increment,
model varchar(255) not null,
pid int unsigned not null,
foreign key(pid) references proizvodjaci(id)
on delete cascade
on update cascade
) engine = InnoDB;
insert into proizvodjaci(proizvodjac) values ('Nokia'),('Sony-Ericson'),('Samsung'),('Siemens');
insert into telefoni (model,pid) values ('6600',1),('6630',1),('3650',1),('p800',2),('p900',2);
U primeru gore koristili smo opciju cascade, što znači da će brisanje slogova u tabeli proizvodjači rezultovati brisanjem slogova koji referenciraju na njih u tabeli telefoni. A sada, hajde da probamo da “prevarimo” MySQL tako što ćemo ubaciti telefon sa nepostojećim ID-jem proizvođača:
mysql> select * from proizvodjaci;
+----+--------------+
| id | proizvodjac |
+----+--------------+
| 1 | Nokia |
| 3 | Samsung |
| 4 | Siemens |
| 2 | Sony-Ericson |
+----+--------------+
4 rows in set (0.00 sec)
mysql> select * from telefoni;
+----+-------+-----+
| id | model | pid |
+----+-------+-----+
| 1 | 6600 | 1 |
| 2 | 6630 | 1 |
| 3 | 3650 | 1 |
| 4 | p800 | 2 |
| 5 | p900 | 2 |
+----+-------+-----+
5 rows in set (0.02 sec)
mysql> insert into telefoni(model,pid) values ('nepostoji',5);
ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails
mysql>
Kao što vidite, nije nam uspelo. Pokušali smo da unesemo novi model telefona sa proizvođačem čiji ID je 5 koji ne postoji u tabeli proizvodjaci i MySQL nam to nije dozvolio, jer bi time narušili referencijalni integritet baze.
A sada da vidimo kako funkcioniše cascade delete/update .
mysql> update proizvodjaci set id=5 where proizvodjac='sony-ericson'; Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from proizvodjaci; +----+--------------+ | id | proizvodjac | +----+--------------+ | 1 | Nokia | | 3 | Samsung | | 4 | Siemens | | 5 | Sony-Ericson | +----+--------------+ 4 rows in set (0.00 sec) mysql> select * from telefoni; +----+-------+-----+ | id | model | pid | +----+-------+-----+ | 1 | 6600 | 1 | | 2 | 6630 | 1 | | 3 | 3650 | 1 | | 4 | p800 | 5 | | 5 | p900 | 5 | +----+-------+-----+ 5 rows in set (0.00 sec) mysql>
Dakle, update-ovali smo ID proizvođača sony-ericsson telefona na vrednost 5. Zahvaljujući setovanoj opciji on update cascade vrednosti pid polja su updateovane i u tabeli telefoni. Slično važi i prilikom brisanja. Ako obrišemo proizvođača Nokia telefona evo šta će se desiti:
mysql> select * from proizvodjaci; +----+--------------+ | id | proizvodjac | +----+--------------+ | 1 | Nokia | | 3 | Samsung | | 4 | Siemens | | 5 | Sony-Ericson | +----+--------------+ 4 rows in set (0.00 sec) mysql> select * from telefoni; +----+-------+-----+ | id | model | pid | +----+-------+-----+ | 1 | 6600 | 1 | | 2 | 6630 | 1 | | 3 | 3650 | 1 | | 4 | p800 | 5 | | 5 | p900 | 5 | +----+-------+-----+ 5 rows in set (0.00 sec) mysql> delete from proizvodjaci where proizvodjac='Nokia'; Query OK, 1 row affected (0.03 sec) mysql> select * from proizvodjaci; +----+--------------+ | id | proizvodjac | +----+--------------+ | 3 | Samsung | | 4 | Siemens | | 5 | Sony-Ericson | +----+--------------+ 3 rows in set (0.00 sec) mysql> select * from telefoni; +----+-------+-----+ | id | model | pid | +----+-------+-----+ | 4 | p800 | 5 | | 5 | p900 | 5 | +----+-------+-----+ 2 rows in set (0.00 sec) mysql>
Kao što vidite modeli marke Nokia su nestali i iz tabele telefoni.
Toliko za ovaj tutorijal. Smatram da je sasvim dovoljan kao uvod, a koga interesuje više, kao i uvek sve potrebne informacije možete naći u odgovarajućoj sekciji MySQL manuala..