Úvodní stránka Vývoj PHP, SQL MySQL - cizí klíče

MySQL - cizí klíče

Dnes si ukážeme jak používat cizí klíče v databázovém systému MySQL. Minulý týden jsme v databázových systémech tuto problematiku opakovali a jak se ukázalo tak řada lidí ani pořádně neví k čemu cizí klíče slouží natož jak je správně používat!

V první řadě si vytvoříme 3 tabulky. Příklady budu ukazovat na problému s propojením článků a sekcí třeba na mém blogu.

CREATE TABLE `clanky` (

`id` INT( 5 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,

`title` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_czech_ci NOT NULL

) ENGINE = innodb CHARACTER SET utf8 COLLATE utf8_czech_ci;

CREATE TABLE `sekce` (

`id` INT( 5 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,

`title` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_czech_ci NOT NULL

) ENGINE = innodb CHARACTER SET utf8 COLLATE utf8_czech_ci;

CREATE TABLE `clanky_join_sekce` (

`id_clanku` int(5) NOT NULL,

`id_sekce` int(5) NOT NULL,

KEY `fk_id_clanku` (`id_clanku`),

KEY `fk_id_sekce` (`id_sekce`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

Nyní na tabulce clanky_join_sekce vytvoříme potřebné cizí klíče pro políčka id_sekce a id_clanku.

ALTER TABLE `clanky_join_sekce`

ADD CONSTRAINT `fk_id_sekce`

FOREIGN KEY(`id_sekce`) REFERENCES sekce(id)

ON UPDATE CASCADE ON DELETE RESTRICT;

ALTER TABLE `clanky_join_sekce`

ADD CONSTRAINT `fk_id_clanku`

FOREIGN KEY(`id_clanku`) REFERENCES clanky(id)

ON UPDATE CASCADE ON DELETE CASCADE;

Samotný význam cizích klíčů nemá za úkol usnadnit práci vývojářům při manipulaci s daty, ale udržet data ve správné formě. Vlastnosti pro UPDATE a DELETE pro cizí klíče jsou následující:

• CASCADE – provede změny ve sloupci podle změny v referenčním sloupci
• RESTRICT – znemožní upravit či smazat data
• SET NULL – při nastaveni referenčního sloupce na NULL či smazání, nastaví se sloupec cizího klíče na NULL (zde samozřejmě je třeba, aby cizí klíč měl možnost obsahovat NULL)

Nyní naplníme tabulky jednoduchými daty a ukážeme si význam vlastnosti ON UPDATE a ON DELETE při manipulacemi s daty.

INSERT INTO `clanky` ( `id` , `title` ) VALUES (NULL , 'První článek');

INSERT INTO `clanky` ( `id` , `title` ) VALUES (NULL , 'Druhý článek');

INSERT INTO `clanky` ( `id` , `title` ) VALUES (NULL , 'Třetí článek');



INSERT INTO `sekce` ( `id` , `title` ) VALUES (NULL , 'Sekce 1');

INSERT INTO `sekce` ( `id` , `title` ) VALUES (NULL , 'Sekce 2');

INSERT INTO `sekce` ( `id` , `title` ) VALUES (NULL , 'Sekce 3');

Naplníme propojení mezi články a sekcemi v tabulce clanky_join_sekce.

INSERT INTO `clanky_join_sekce` ( `id_clanku` , `id_sekce` ) VALUES ('1', '1');

INSERT INTO `clanky_join_sekce` ( `id_clanku` , `id_sekce` ) VALUES ('1', '2');

INSERT INTO `clanky_join_sekce` ( `id_clanku` , `id_sekce` ) VALUES ('2', '2');

Nyní máme naplněnou strukturu základními daty. Teďka si ukážeme k čemu vůbec cizí klíče slouží!? Zkuste si vložit do tabulky clanky_join_sekce id_clanku = 5 a id_sekce = 3. Databázový systém Vám vrátí chybu ve znění: Nepovedlo se vložit nebo změnit řádek, cizí klíč selhal! Toto vzniklo kvůli tomu, že jste vkládali id_clanku, které není v tabulce clanky! To je jedno ze základních výhod cizích klíčů.

Další výhodou je to, že když budete používat pro UPDATE a DELETE vlastnost CASCADE tak se Vám při změně klíče v referenční tabulce změní i cizí klíč v ostatních tabulkách.

Příklad: UPDATE `clanky` SET `id` = '4' WHERE `id` = 1;
Když si vypíšete tabulku clanky_join_sekce uvidíte následující

id_clanku	id_sekce
4	1
4	2
2	3

Další nesporná výhoda je ta, že pokud budu chtít smazat nějakou sekci, která má uvedené své ID v tabulce clanky_join_sekce, tak mi opět databázový systém zahlásí chybu, protože jsme pro akci ON DELETE nastavily RESTRICT.

Toto je podle mě velká výhoda, vemte si že byste měli objednávku a v ní uživatele, teďka byste smazali uživatele a v životě už byste se nedozvěděli pro koho ta objednávka byla určená!

Tato vlastnost se dá obejít v případě nastavení ON DELETE na CASCADE (to by se ale i objednávka smazala) nebo nastavením na SET NULL (ovšem musí být možné aby se v tabulce, kde se nacházel cizí klíč mohla vyskytnout hodnota NULL).

Jak vidíte cizí klíče jsou nespornou výhodou při návrhu tabulek. Správný SQL developer cizí klíče používá a snaží se je cpát všude tam kam jdou.