Посилальна цілісність — Вікіпедія
Посилальна цілісність (англ. referential integrity) — один з різновидів обмеження узгодженості бази даних, який полягає у відсутності в будь-якому з її відношень посилань на дані, що відсутні в іншому відношенні[1].
Визначення[ред. | ред. код]
Зв'язки між даними, що зберігаються в різних відношеннях, у реляційній базі даних встановлюються за допомогою використання зовнішніх ключів — для встановлення зв'язку між кортежем відношення 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), то вилучення забороняється.
Ця стаття може містити оригінальне дослідження. (лютий 2017) |
Ця стаття не містить посилань на джерела. (березень 2017) |
Це незавершена стаття про бази даних. Ви можете допомогти проєкту, виправивши або дописавши її. |
Література[ред. | ред. код]
- Silberschatz, Abraham; Sudarshan, S. (2011). Database system concepts (вид. 6). New York: McGraw-Hill. ISBN 9780073523323. OCLC 436031093.
Зноски[ред. | ред. код]
- ↑ Silberschatz та Sudarshan, 2011, с. 11.
|