Посилальна цілісність
Посилальна цілісність (англ. referential integrity) — один з різновидів обмеження узгодженості бази даних, який полягає у відсутності в будь-якому з її відношень посилань на дані, що відсутні в іншому відношенні[1].
artist_id) у таблиці альбомів, яке посилається на відсутнього артиста. Тобто, наявне значення зовнішнього ключа, яке не має відповідного первинного ключа у відповідній таблиці. Так сталось, тому, що артист Aerosmith зі значенням artist_id рівним 4, чомусь був видалений з таблиці артистів. Проте, альбом Eat the Rich посилається на цього виконавця. З посилальною цілісністю це було б неможливо.Визначення
Зв'язки між даними, що зберігаються в різних відношеннях, у реляційній базі даних встановлюються за допомогою використання зовнішніх ключів — для встановлення зв'язку між кортежем відношення A з визначеним кортежем відношення B до передбачених для цього атрибутів кортежу відношення A записується значення первинного ключа (а в загальному випадку значення потенційного ключа) цільового кортежу відношення B. Таким чином, завжди є можливість виконати дві операції:
- визначити, з яким кортежем відношення B зв'язано визначений кортеж відношення A;
- знайти всі кортежі відношення A, що мають зв'язки з визначеним кортежем відношення B.
Завдяки наявності зв'язків у реляційній базі даних можна зберігати факти без надлишкового дублювання, тобто у нормалізованому вигляді. Посилальну цілісність може бути проілюстровано наступним чином:
- Дано два відношення A і B, зв'язані зовнішнім ключем. Первинний ключ відношення B — атрибут
B.key. Зовнішній ключ відношення A, що посилається на B — атрибутA.b. Посилальна цілісність для цих відношень A і B має місце тоді, коли виконується умова: для кожного кортежу відношення A існує відповідний кортеж відношення B, тобто кортеж, у якогоB.key = A.b.
База даних має властивість посилальної цілісності, коли для довільної пари зв'язаних зовнішнім ключем відношень у ній умова посилальної цілісності виконується.
Якщо вищенаведена умова не виконується, то кажуть, що в базі даних порушено посилальну цілісність. Така база даних не може нормально експлуатуватися, оскільки в ній розірвані логічні зв'язки між залежними один від одного фактами. Безпосереднім результатом порушення посилальної цілісності є те, що коректним запитом не завжди вдається отримати коректний результат.
Приклад
Розгляньмо деяку базу даних, що складається з таких двох таблиць:
|
|
Так, у цьому прикладі реляційна база даних, що складається з таблиць Address і Street, забезпечує зберігання адрес. При цьому основна таблиця Address містить безпосередньо номер будинку та квартири, а замість назви вулиці у поле Street має зовнішній ключ, який посилається на таблицю Street — довідник вулиць. Очевидно, що повноцінну адресу має бути представлено двома зв'язаними записами в обох названих таблицях, що технічно висловлюється в умові: для будь-якого запису таблиці Address в таблиці Street повинен існувати відповідний запис, тобто запис із Street.Key = Address.Street. Щоб отримати список повних адрес із таблиць такої структури, коли в них виконується посилальна цілісність, достатньо застосувати до даних таблиць SQL-запит
select *
from Address, Street
where Address.Street = Street.Key
В даному прикладі, однак, посилальну цілісність порушено. Два записи таблиці Address (Key = 887 і Key = 994) мають у полі Street так звані «висячі» посилання — значення, яким не відповідають записи в таблиці Street (ці посилання показано червоним кольором). Через це результат вищенаведеного запиту не міститиме цих двох записів — для них умова запиту не виконається. І ще один запис не буде вибрано вищенаведеним запитом — запис таблиці Address з Key = 85. Це варіант навмисного (та, в деяких випадках, легального) порушення посилальної цілісності — в полі зовнішнього ключа записано NULL (показано блакитним кольором). Аби отримати список усіх адрес, навіть тих, в яких не вказано вулицю, необхідно використати відкрите з'єднання, яке в одному з варіантів синтаксису записується так:
select *
from Address left outer join Street
on Address.Street = Street.Key
Якщо ж вимагається отримати список, до якого не входять записи з «висячими» посиланнями, то доведеться ускладнити запит:
select *
from Address left outer join Street
on (Address.Street = Street.Key) or (Address.Street is null)
Підтримування посилальної цілісності в БД
Причини порушень
Правильно спроектована та підтримувана база даних не дає можливості порушення посилальної цілісності. Тим не менше, такі порушення можуть з'явитися протягом експлуатації бази через цілий ряд причин. Деякі з них:
- Некоректна робота прикладного програмного забезпечення
- Зрозуміло, що в разі помилки у програмі, що виконує модифікацію бази даних, базу може бути модифіковано неприпустимим чином, внаслідок чого утворюються «висячі» посилання. Програма може припускатися помилок наступних видів:
- Неповний запис об'єктів
- Дані об'єкта розміщуються в записах декількох таблиць, а програма не записує котрусь із них.
- Некоректне редагування посилання
- Значення зовнішнього ключа змінюється на таке, якому не відповідає жоден запис у зв'язаній таблиці.
- Редагування первинного ключа без каскадного оновлення
- У таблиці, на яку є посилання, редагується первинний ключ, але при цьому зовнішні ключі у зв'язаних із нею таблицях лишаються без змін.
- Вилучення запису без каскадного оновлення
- З таблиці вилучається запис, на який наявні посилання за зовнішніми ключами інших таблиць, при цьому у зв'язаних записах зовнішні ключі не змінюються. Внаслідок чого всі записи інших таблиць, які посилаються на нього, стають некоректними.
- Збої в роботі системного програмного забезпечення та обладнання
- Навіть коли прикладне програмне забезпечення працює абсолютно правильно, можливе порушення посилальної цілісності. Наприклад, якщо при додаванні об'єкта до бази потрібно додати декілька зв'язаних записів у декілька таблиць, то очевидно, що посилальну цілісність буде порушено протягом додавання даних (коли частину зв'язаних записів уже додано, а частину — ще ні), і відновиться вона лише після завершення операції. Якщо під час виконання операції її буде перервано (через переповнення диску, збою живлення, чи через будь-які інші причини), то частину записів буде додано до бази даних, а частину — ні. Частина доданих записів залишиться з некоректними посиланнями.
Порожні зовнішні ключі
Можлива ситуація, коли зовнішній ключ замість посилання на наявний запис у таблиці бази даних містить «відсутнє значення» NULL. Такий стан можна тлумачити як відсутність деякої частини об'єкта. Хоча з точки зору чистої теорії це недопустимо, на практиці іноді буває зручно дозволити використання порожніх зовнішніх ключів. Аби коректно працювати з групами зв'язаних таблиць, які дозволяють порожні зовнішні ключі, використовуються специфічна операція мови SQL — відкрите, або зовнішнє з'єднання (англ. outer join).
Посилальна цілісність на тригерах
Можливе підтримування посилальної цілісності бази даних з використанням механізму тригерів. У цьому разі для будь-якої потенційно небезпечної операції над таблицею створюється тригер, який виконує необхідні перевірки чи навіть змінює дані у зв'язаних таблицях, аби виключити втрату посилань.
Так, для забезпечення каскадних змін тригер може бути встановлено на операцію зміни запису в таблиці. Якщо виявиться, що під час редагування змінилося значення ключового поля, то тригер повинен виконати узгоджені зміни в усіх таблицях, зв'язаних із даною, замінивши старе значення зовнішніх ключів новим.
Для виключення втрати посилань від некоректного редагування зовнішнього ключа тригер повинен при кожній зміні відповідного поля перевіряти, чи наявний у зв'язаній таблиці запис із таким первинним ключем.
Для захисту від вилучення запису, на який наявні посилання, тригер на зв'язаній таблиці повинен у разі вилучення перевіряти наявність посилань і, залежно від необхідності, або забороняти вилучення, або обнуляти зовнішні ключі тим чи іншим чином.
Посилальна цілісність на зовнішніх ключах
СКБД може мати механізм автоматичного підтримування посилальної цілісності, заснований на явному описі посилань під час створення бази даних. Під час опису таблиць бази даних програміст явно описує, які поля таблиць є зовнішніми ключами і на які таблиці вони посилаються. Ця інформація зберігається у службових ділянках пам'яті бази даних. Будь-яка операція, що змінює дані у таблиці, викликає автоматичну перевірку посилальної цілісності. При цьому:
- У разі операції додавання чи редагування запису автоматично перевіряється, чи посилаються зовнішні ключі у цьому записі на наявні записи в оголошених під час опису зв'язаних таблицях. Якщо з'ясовується, що операція спричинить появу некоректних посилань, то вона не виконується, і система повертає помилку.
- У разі операції редагування запису перевіряється:
- якщо змінюється її первинний ключ і на даний запис наявні посилання, то операція редагування завершується з помилкою;
- якщо змінюється якийсь із зовнішніх ключів, які зберігаються у цьому записі, і після зміни зовнішній ключ посилатиметься на відсутній запис, то операція редагування завершується з помилкою.
- У разі операції вилучення запису перевіряється, чи немає на нього посилань. Якщо посилання наявні, то можливі три варіанти подальших дій (та, що фактично виконається, залежить від системи керування базами даних і від вибору програміста, який він повинен зробити під час опису зв'язку):
- Заборона — вилучення блокується та повертається помилка.
- Каскадне вилучення — в межах однієї транзакції відбувається вилучення даного запису та всіх записів, які посилаються на даний. Якщо на записи, що вилучаються, також є посилання і налаштування також вимагають вилучення, то каскадне вилучення продовжується далі. Таким чином, після вилучення даного запису в базі не лишається жодного запису, який прямо чи опосередковано посилається на нього. Якщо хоча б один із записів, який посилається на нього, вилучити не вдається (або для нього налаштовано заборону, або відбувається будь-яка інша помилка), то всі вилучення забороняються.
- Присвоєння NULL — в усі зовнішні ключі записів, які посилаються на даний, записується маркер NULL. Якщо хоча б для одного з записів, які посилаються на нього, це неможливо (наприклад, якщо поле зовнішнього ключа описано як NOT NULL), то вилучення забороняється.
Література
- Silberschatz, Abraham; Sudarshan, S. (2011). Database system concepts (вид. 6). New York: McGraw-Hill. ISBN 9780073523323. OCLC 436031093.
Зноски
- Silberschatz та Sudarshan, 2011, с. 11.