Често срещани заявки над база данни са операциите изтриване и промяна на съществяуващ запис. Какво се случва обаче, когато имаме външен ключ от една таблица към друга и стойността, към която някой сочи бъде изтрита.
Всичко зависи от ограничението, което е декларирано по време на създаването на външния ключ. От статията за ограничения знаете, че върху външния ключ съществуват следните ограничения:
- ON DELETE SET NULL
- ON DELETE RESTRICT
- ON DELETE CASCADE
Аналогично, може да декларирате и ON UPDATE CASCADE. В този случай говорим за каскадни операции. Ако например сменим id-то на един треньор в нашата база при първоначално създадените таблици, какво ще се случи?
UPDATE coaches SET id = 22 WHERE id = 1;
Или пък се опитаме да го изтрием:
DELETE FROM coaches WHERE id = 1;
Сървърът ще ни даде следната грешка:
Cannot delete or update a parent row: a foreign key constraint fails.
Това е така, тъй като, когато сме правили външен ключ в таблицата sportgroups, ние не сме указали изрично с кое ограничение да бъде създаден този ключ, който да сочи към колона id от таблица coaches. В този случай се използва ограничението по подразбиране от тип RESTRICT. Именно и затова ни е забранено да променяме или изтриваме id на треньор, който се реферира. Нека да променим постановката така: ще премахнем външния ключ в таблицата sportgroups, ще направим нов външен ключ върху същата колона coach_id и ще използваме CASCADE. За да видим как се казва ключът отиваме в изгледа за тази таблица и виждаме, че явно по време на създаването не сме дали смислени имена на нито един от ключовете и затова са използвани наименования по подразбиране. Нашият ключ е вторият.
ALTER TABLE sportgroups DROP FOREIGN KEY sportgroups_ibfk_2; ALTER TABLE sportgroups ADD CONSTRAINT FOREIGN KEY `coach_id_key` (coach_id) references coaches(id) ON DELETE SET NULL ON UPDATE CASCADE;
С горната заявка, при изтриване на треньор от базата, навсякъде в sportgroups, където се среща неговото id ще се записва NULL. А когато се прави обновяване на id-та, то те каскадно ще се обновят. Но няма смисъл да правим ON DELETE CASCADE – в този случай биха се изтрили всички спортни групи при даден треньор, ако той бъде изтрит.
Ето какво имаме в момента в sportgroups:
Ще сменим изпълним следната заявка:
DELETE FROM coaches WHERE id = 1;
Отново проверяваме sportgroups:
Нека изпълним следната заявка:
UPDATE coaches SET id = 22 WHERE id = 2;
И да видим какво се е случило в sportgroups:
Съвсем очаквано там, където id-то на треньора е било 2, е станало 22.
За да видите дали някой сочи към определена колона, може да изпозлвате следната заявка:
USE information_schema;
SELECT table_name
FROM referential_constraints
WHERE constraint_schema = 'database_name' AND
referenced_table_name = 'parent_table' AND
delete_rule = 'CASCADE';
Може да смените delete_rule с update_rule:
USE information_schema; SELECT table_name FROM referential_constraints WHERE constraint_schema = 'school_sport_clubs' AND referenced_table_name = 'coaches' AND update_rule = 'CASCADE';
Резултатът е :
Нека да разгледаме случай, при който искаме да изтрием запис, а към него има референция (външен ключ) с ограничение ON DELETE RESTRICT. В такъв случай добра стратегия е first update then delete. Навсякъде, където съществува като запис референция към записа, който искаме да изтрием, правим промяна с друга референция. Уверяваме се, че вече никой не сочи към желания от нас ред и тогава го трием. Може да разиграете случая, при който се изтрива спортна група. Трябва да помислите за правилно преместване на всички студенти, трениращи в нея в друга спортна група, вероятно от същия тип спорт. Тази таблица обаче се реферира и от taxpayments. В този случай бихме изгубили данните за това за коя група са платили дадените ученици таксите си. Ако няма проблем да променим и там групата на друга група и платените такси за старата група да започнат да важат за новата, правим го и после трием групата. Но ако има – просто няма смисъл групата да бъде изтривана – това ще доведе до загуба на данни.
Даниел Джолев