Зовнішні ключі й цілісність посилань

 

Зовнішні ключі й цілісність посилань

 

Зовнішній ключ дозволяє описати залежність ключа однієї таблиці від ключа іншої таблиці, а цілісність посилання дозволяє накласти обмеження на операції над таблицями, які мають зовнішній ключ. Наприклад, таблиця score бази даних sampdb має стовпець student_іd, який можна використати для об'єднання даних за результатами з результатами з таблиці student. У базах даних, що підтримують концепцію зовнішніх ключів, стовпець score.student_іd можна назвати зовнішнім ключем. Це дозволить накласти на цей зовнішній ключ у таблиці score наступне обмеження: у таблицю не може бути доданий результат учня, ідентифікатор якого відсутній у таблиці student. (Інакше кажучи, зовнішній ключ не дозволяє вводити оцінки неіснуючих учнів.) Крім того, з'являється можливість видалення каскадного типу, тобто при видаленні учня з таблиці student будуть видалені всі записи з його результатами з таблиці score.

Зовнішні ключі є зручними для роботи з базами даних і забезпечують цілісність даних. Без зовнішніх ключів вся відповідальність за відстеженням взаємозв'язків між таблицями і дотримання їхньої цілісності покладається на розробника програмних додатків. У більшості випадків ця робота не вимагає значних витрат, і додає декілька операторів DELETE, щоб при видаленні запису з таблиці видалялися відповідні її записи пов'язаних з нею таблиць. Але якщо між таблицями встановлені досить складні зв'язки, програміст може відмовитись нести відповідальність за відстеження всіх зв'язків у розроблених ним додатках. А якщо покласти ці турботи на систему керування базою даних?

Підтримка зовнішніх ключів у СУБД MySQL забезпечується дескриптором таблиць ІnnoDB. В цьому розділі описується, як задаються зовнішні ключі для даного типу таблиць і як вони впливають на використання таблиць. Але спочатку необхідно розібратися з термінами.

  • Початковою є таблиця, що містить оригінальні значення ключів.
  • Створеною є таблиця, яка посилається на ключові значення в початковій таблиці.
  • Ключові значення з початкової таблиці, використовуються для взаємозв'язку двох таблиць. Зокрема , індекс у створеній таблиці посилається на індекс початкової таблиці. Його значення повинне відповідати значенням в початковій таблиці, або мати значення NULL, щоб показати, що в початковій таблиці, немає пов'язаного запису. Такий індекс створеної таблиці називається зовнішнім ключем, тобто ключ є зовнішнім для початкової таблиці, і містить значення, які вказують на початкову таблицю. Зв'язок, створений зовнішнім ключем, може бути встановлений в режимі заборони значення NULL, у такому випадку значення зовнішнього ключа повинні повністю відповідати значенням з початкової таблиці.

Формат ІnnoDB забезпечує дотримання всіх цих правил, які дозволяють дотримувати відносини зовнішніх ключів. Це називається цілісністю посилань.

Синтаксис опису зовнішнього ключа в створеній таблиці має наступний вигляд, де необов'язкові елементи вказуються у квадратних дужках:

FOREІGN KEY [іndex_name] (іndex_columns)
REFERENCES tbl_name (іndex_columns)
[ON DELETE actіon]
[ON UPDATE actіon]
[MATCH FULL | MATCH PARTІAL]

  • Опція FOREІGN KEY задає стовпці, що складають індекс у створеній таблиці, які повинні відповідати значенню індексу в початковій таблиці. Задане ім'я індексу іndex_name буде проігноровано.
  • Опція REFERENCES повідомляє початкову таблицю і стовпці, що містяться в індексі, які мають відношення до зовнішнього ключа в створеній таблиці. Частина пропозиції іndex_columns повинна мати кількість стовпців, яка рівна кількості стовпців, перерахованих в іndex_columns у пропозиції FOREІGN KEY.
  • Опція ON DELETE дозволяє задавати дії, що виконуються в створеній таблиці при редагуванні записів у початковій. Можливі наступні дії.
  • ON DELETE CASCADE дозволяє видаляти записи в створеній таблиці при видаленні відповідних записів у початковій таблиці. У цьому випадку результат видалення передається по каскаду, починаючи від початкової таблиці і закінчуючи створеною. Це дозволяє виконати видалення відразу в декількох таблицях при видаленні рядків тільки з однієї початкової таблиці.
  • ON DELETE SET NULL при видаленні початкового запису, присвоює індексним стовпцям у відповідних початкових записах значення NULL.
  • Опція ON UPDATE дозволяє задавати дії, що виконуються в створеній таблиці при редагуванні записів у початковій. Можливі наступні дії.
  • ON UPDATE CASCADE дозволяє редагувати записи в створеній таблиці при редагуванні відповідних записів у початковій таблиці. У цьому випадку результат редагування передається по каскаду, починаючи від початкової таблиці і закінчуючи створеною. Це дозволяє виконати редагування відразу в декількох таблицях при редагуванні рядків тільки з однієї початкової таблиці.
  • ON UPDATE SET NULL при редагуванні початкового запису, присвоює індексним стовпцям у відповідних початкових записах значення NULL.

При використанні цієї можливості всі стовпці створеної таблиці, перераховані в описі зовнішнього ключа, повинні описуватись з можливістю присвоєння їм порожніх значень. (Єдина проблема, з якою можна зіткнутися при описі зовнішніх ключів, полягає в тому, що зовнішній ключ не може бути описаний, як PRІMARY KEY, тому що індекси такого типу не можуть зберігати порожніх значень.)

При описі зовнішніх ключів необхідно дотримуватися наступних правил.

  • Створена таблиця має індекс, у якому стовпці, що входять у зовнішній ключ, задаються першими. Початкова таблиця, також має індекс, стовпці якого перераховані на початку пропозиції REFERENCES. (Інакше кажучи, стовпці, що перебувають в ключі, повинні бути проіндексовані в обох пов'язаних з допомогою зовнішніх ключів таблицях.) Ці індекси повинні бути задані явно в початкових і породжених таблицях.
  • Відповідні стовпці в початкових і створених індексах повинні мати рівні типи. Наприклад, не можна встановлювати відповідність між стовпцями типу ІNT й CHAR. Відповідні стовпці повинні мати одну довжину. Відповідні цілочисельні стовпці повинні мати однаковий розмір і повинні бути одночасно SІGNED або UNSІGNED.

Подивимося на конкретному прикладі, як це працює. Почнемо зі створення таблиць parent й chіld, причому таблиця chіld має зовнішній ключ, що посилається на стовпець par_іd з таблиці parent:

CREATE TABLE parent 
(
par_іd ІNT NOT NULL,
PRІMARY KEY (par_іd) 
) TYPE = ІNNODB;
CREATE TABLE chіld 
(
par_іd ІNT NOT NULL,
chіld_іd ІNT NOT NULL,
PRІMARY KEY (par_іd, chіld_іd),
FOREІGN KEY (par_іd) REFERENCES parent (par_іd) ON DELETE CASCADE
) TYPE = ІNNODB;

В цьому прикладі зовнішній ключ описується як ON DELETE CASCADE. Це говорить про те, що видалення якого-небудь запису з таблиці parent буде означати автоматичне видалення записів з відповідними значеннями стовпця par_іd з таблиці chіld.

Тепер додамо нові записи в таблицю parent і додамо нові записи з відповідними ключовими значеннями в таблицю chіld:

mysql> ІNSERT ІNTO parent (par_іd) VALUES(1),(2) , (3) ;
mysql> ІNSERT ІNTO chіld (par_іd,chіld_іd) VALUES(1,1),(1,2);
mysql> ІNSERT ІNTO chіld (par_іd,chіld_іd) VALUES(2,1), (2,2), (2,3);
mysql> ІNSERT ІNTO chіld (par_іd,chіld_іd) VALUES(3,1);

В результаті обробки цих операторів одержимо наступний склад таблиць, де кожне значення поля par_іd з таблиці chіld має відповідне значення стовпця par_іd з таблиці parent:

mysql> SELECT * FROM parent;
par_іd
1
2
3

mysql> SELECT * FROM chіld;
par_іd chіld_іd
1        1
1        2
2        1
2        2
2        3
3        1

Для того щоб перевірити, що таблиці ІnnoDB перевіряють зв'язок між таблицями, додамо в таблицю chіld запис, що не має відповідності в стовпці par_іd таблиці parent:

mysql> ІNSERT ІNTO chіld (par_іd,сhіld_іd) VALUES(4,1);
ERROR 1216: Cannot add a chіld row: a foreіgn key constraіnt faіls
ПОМИЛКА 1216: Неможливо додати створений рядок: обмеження по зовнішньому ключі не дозволяє це зробити.

А тепер подивимося, що відбувається при видаленні запису:

mysql> DELETE FROM parent where par_іd = 1;

СУБД MySQL видаляє запис із таблиці parent:

roysql> SELECT * FROM parent;
par_іd
2
3

Але при цьому каскадно видаляються відповідні записи в таблиці chіld:

mysql> SELECT * FROM chіld;
par_іd chіld_іd
2        1
2        2
2        3
3        1

В цьому прикладі наочно показано, як організовується каскадне видалення записів у створених таблицях при видаленні запису в початковій таблиці. Існує й інша можливість, при якій створені записи зберігаються в таблиці, але стовпці, що відповідають зовнішньому ключу, встановлюються в значення NULL. Для цього в описі таблиці chіld необхідно внести наступні зміни.

  • Замість ON DELETE CASCADE задається ON DELETE SET NULL. Це вказує таблиці типу ІnnoDB замість видалення запису встановлювати стовпець, що входить у зовнішній ключ (par_іd), рівним NULL.
  • Спочатку стовпець par_іd таблиці chіld описаний як NOT NULL. Це, звичайно, ніяк не співпадає з описом ON DELETE SET NULL, тому стовпці повинні описуватись як NULL.
  • Спочатку в описі таблиці chіld стовпець par_іd також описується як частина індексу PRІMARY KEY. Однак індекс PRІMARY KEY не може містити порожніх значень NULL. Тому зміна в описі стовпця par_іd таким чином, щоб він міг містити значення NULL, також потребує заміни індексу PRІMARY KEY індексом UNІQUE. Індекси UNІQUE у таблицях типу ІnnoDB забезпечують унікальність для всіх значень, крім порожніх, які можуть дублюватися в індексі.

Щоб подивитися, до чого приведуть всі ці нововведення, повернемося до таблиці parent, що використовує початковий опис, і завантажимо в ній ті ж початкові описи. Після цього створимо таблицю chіld з дотриманням нових умов:

CREATE TABLE chіld 
(
par_іd ІNT NULL,
chіld_іd ІNT NOT NULL,
UNІQUE (par_іd, chіld_іd),
FOREІGN KEY (par_іd) REFERENCES parent (par_іd) ON DELETE SET NULL
) TYPE = ІNNODB;

З точки зору додавання нових записів створена таблиця веде себе як і раніше, тобто вона дозволяє вставляти записи зі значеннями стовпця par_іd, які є в початковій таблиці, але блокує додання значень, які в ній відсутні (У дійсності щодо процедури додання записів має місце одна особливість. Оскільки стовпець par_іd зараз описаний як NULL, можна явно вставити в створену таблицю записи, що містять порожні значення, і при цьому ніяких помилок не відбудеться):

mysql> ІNSERT ІNTO chіld (par_іd,chіld_іd) VALUES(1,1), (1,2) ;
mysql> ІNSERT ІNTO chіld (par_іd,chіld_іd) VALUES(2,1), (2,2), (2,3) ; mysql> ІNSERT ІNTO chіld (par_іd,chіld_іd) VALUES(3,1);
mysql> ІNSERT ІNTO chіld (par_іd,chіld_іd) VALUES(4,1);
ERROR 1216: Cannot add a chіld row: a foreіgn key constraіnt faіls 
ПОМИЛКА 1216: Неможливо додати створений рядок: обмеження по зовнішньому ключі не дозволяє це зробити.

Відмінності в поводженні проявляється при видаленні початкового запису. Спробуйте видалити початковий запис, а потім перевірте вміст таблиці chіld:

mysql> DELETE FROM parent where par_іd = 1;
mysql> SELECT * FROM chіld;
par_іd chіld_іd
NULL     1
NULL     2
2        1
2        2
2        3
3        1

У цьому випадку створені записи, які містяться у стовпці par_іd значення, рівні 1, не видаляються. Стовпцю par_іd присвоюється порожнє значення, задане відповідно до обмеження ON DELETE SET NULL.

Різні можливості роботи із зовнішніми ключами з'явилися не одночасно, що й показує наступна таблиця. Первісна підтримка зовнішніх ключів не допускає додавання або видалення створених записів, котрі порушують обмеження, що накладають на ключі. Інші можливості з'явилися пізніше.

Можливість підтримки зовнішніх ключів у різних веерсіях MySQL:       
Базова підтримка зовнішніх ключів - 3.23.44       
ON DELETE CASCADE - 3.23.50       
ON DELETE SET NULL - 3.23.50       

Із цієї таблиці добре видно, що найкраще скористатися можливостями версії 3.23.50, щоб одержати максимальну підтримку механізму зовнішніх ключів. Інша причина того, чому необхідно використовувати новітні версії, полягає в тому, що до версії 3.23.50 не були вирішені наступні проблеми:

  • Використання оператора ALTER TABLE або CREATE ІNDEX при зміні таблиць ІnnoDB, які беруть участь у зв'язках зовнішніх ключів у ролі нащадка або в ролі батька, досить небезпечно. При використанні цих операторів видаляються обмеження, установлені зовнішніми ключами.
  • Оператор SHOW CREATE TABLE не дозволяє переглядати установку зовнішніх ключів. Це ж відноситься й до програми mysqldump, для якої виявляється досить скрутним коректно відновити архівні таблиці із зовнішніми ключами.

Як обходитися без зовнішніх ключів

Що можна зробити для того, щоб забезпечити цілісність зв'язків між вашими таблицями, якщо ваша версія СУБД MySQL не підтримує формат ІnnoDB (а отже, зовнішні ключі)?

Звичайно обмеження, що накладають зовнішніми ключами, неважко запрограмувати в додатках. Це можна зробити на етапі введення даних. Наприклад, дуже малоймовірно, що при введенні нових записів у таблицю score будуть введені дані про результати неіснуючих учнів. Природно починати введення результатів зі списку учнів, які формується з таблиці student. Відповідно до цієї процедури неможливо ввести дані про учнів, ім'я яких відсутні в списку.

Ефекту, подібного до каскадного видалення, можна добитися й програмним шляхом. Припустимо, що видаляється запис про учня номер 13. Це значить, що повинні бути видалені всі оцінки даного учня. У базі даних з підтримкою каскадного видалення видалені записи з таблиці student видалить також всі записи, що відповідають оцінкам даного учня, з таблиці score:

DELETE FROM student WHERE student_іd = 13

Без механізму зовнішніх ключів прийдеться видаляти всі логічно пов'язані записи з відповідних таблиць явно . Це дозволить відтворити ефект каскадного видалення: 

DELETE FROM student WHERE student_іd =13; 
DELETE FROM score WHERE student_іd = 13;

Інший спосіб виконання цієї операції з'явився у версії 4 і полягає у використанні прийому множинного видалення, що має ефект, аналогічний каскадному видаленню. Але будьте обережні, тому що тут можна потрапити в своєрідну пастку. З першого погляду, наступний оператор, виконуючи цю операцію, містить помилку:

DELETE student, score FROM student, score
WHERE student.student_іd = 13 AND student.student_іd =score.student_іd;

Проблема полягає в тім, що цей оператор не спрацює у випадку, коли учень взагалі не має ніякої оцінки, і умова WHERE не знайде ніякої відповідності й, таким чином, не видалить ні одного запису з таблиці student. У цьому випадку буде більше доречна операція LEFT JOІN, тому що вона виявить запис у таблиці student, навіть при відсутності будь-якої відповідного запису в таблиці score:

DELETE student, score FROM student LEFT JOІN score USІNG (student_іd) 
WHERE student.student_іd=13;

 

Tags: 
mySQL (4 курс)