Розширення функціональності декларативних обмежень цілісності в СУБД Oracle8
(Oracle8's Integrity-Constraint Enhancements, by Steve Bobrowski (68fast.html))
Використовуйте удосконалення Oracle8 для забезпечення додаткової гнучкості і розширення функціональності обмежень цілісності.
Обмеження цілісності, встановлені на таблицях бази даних, гарантують узгодженість даних за правилами, що забезпечує точність і прийнятність інформації. Без декларативних обмежень цілісності встановлення навіть найпростіших правил для даних стало б складною або взагалі нездійсненною завданням.
СУБД Oracle7 підтримує різні типи обмежень цілісності, включаючи:
- первинні і унікальні ключі, для забезпечення цілісності сутності (всієї таблиці);
- обмеження Not Null і Check, для забезпечення цілісності доменів (областей визначення);
- зовнішні ключі для забезпечення посилальної цілісності (див. "Короткий огляд обмежень цілісності").
Крім того, Oracle7 включає кілька додаткових можливостей, наприклад, вмикати та вимикати обмеження цілісності за бажанням. Реалізація обмежень цілісності Oracle7 була розширена в Oracle8. У цій статті обговорюються використання обмежень цілісності Oracle7, а також три істотних їх поліпшення в Oracle8.
1. "відкладаються" обмеження цілісності
Oracle7 при виконанні кожного SQL-запиту, що змінює табличні дані, перевіряє, чи задовольняють дані всіх включених обмеженням цілісності. Безпосередня (Immediate - миттєва) перевірка обмежень цілісності ускладнює завдання реалізації деяких виконуваних додатками дій, наприклад, "каскадні оновлення". Каскадне оновлення відбувається тоді, коли оновлюються значення первинних ключів записів головної таблиці, для якої існують детальні записи. Для збереження взаємозв'язку між головною і детальними записами оновлення первинного ключа повинно спричинити оновлення полів зовнішнього ключа у всіх пов'язаних детальних записах. Так як оновлення значень первинних ключів в більшості додатків не проводиться, Oracle декларативно (тобто як частина визначення таблиці) не підтримує каскадне оновлення зовнішніх ключів. Але якщо можливість оновлення значень первинного ключа при наявності залежних детальних записів все ж потрібна для роботи програми, необхідно реалізувати функціональність каскадного оновлення за допомогою механізму тригерів бази даних, як частини програми. Інакше буде отримано повідомлення про помилку:
У Oracle7 реалізація функціональності каскадного оновлення може стати складним завданням тому, що сервер перевіряє дані на відповідність усім включеним обмеженням під час виконання запиту, і включені декларативні обмеження цілісності Oracle7 не допускають навіть тимчасової невідповідності даних. Обхідним шляхом могла б бути реалізація в додатку такого алгоритму:
- Відключення зовнішнього ключа детальної таблиці.
- Оновлення значень первинного ключа в головній таблиці.
- Оновлення відповідних значень зовнішнього ключа в детальної таблиці.
- Повторне включення зовнішнього ключа на детальної таблиці.
У лістингу 1 наведено приклад реалізації цього алгоритму для таблиць CUSTOMERS і ORDERS (ЗАМОВНИКИ і ЗАМОВЛЕННЯ). Хоча цей метод вирішує завдання, він складний в реалізації. Додаток має бути спроектовано так, щоб оцінювати, коли необхідно провести відключення обмежень цілісності, якщо транзакція виробляє їх оновлення. Додамо, що програма не має відключати зовнішні ключі у всіх тих випадках, коли оновлюється значення первинного ключа не має відповідних йому детальних записів. Застосування цього методу не тільки надає значну складність додатком, але це рішення просто не може бути застосовано в багатьох випадках, тому що детальна таблиця залишається незахищеною від введення неправильних даних на весь той час, коли зовнішній ключ залишається відключеним. Таким чином, безпосередня перевірка обмежень цілісності в Oracle7 може привести до появи складно вирішуваних проблем.
Для зниження складності в Oracle8 реалізована нова можливість, описана в стандарті ANSI / ISO SQL92, звана "відкладена перевірка обмежень цілісності". Відкладається обмеження цілісності можна налаштувати так, щоб воно перевірялося при завершенні транзакції, а не при виконанні кожного SQL-запиту. Відкладаються обмеження цілісності дозволяють створювати тимчасові невідповідності даних обмеженням на час виконання транзакції. Проте, при фіксації транзакції, дані повинні відповідати всім обмеженням, інакше Oracle8 зробить її відкат. Це нововведення спрощує реалізацію каскадних оновлень.
Для визначення відкладали обмеження цілісності використовуються нові ключові слова в командах створення і зміни визначення таблиці. Нова можливість Oracle8 доступна при визначенні обмежень цілісності, як на рівні стовпців (column-level), так і на рівні таблиць (table-level):
Необхідно ретельно оцінити кожну з альтернатив і вибрати для додатка підходяще поведінку. Наприклад, потрібно створити зовнішній ключ для детальної таблиці і є необхідність в оновленні відповідного первинного ключа. У цьому випадку створюється зовнішній ключ як відкладається обмеження цілісності з опцією INITIALLY IMMEDIATE (спочатку не відкладене).
Щоб явно визначити використання в транзакції одного або відразу декількох відкладаються обмежень, додаток повинен почати транзакцію з нової команди SQL SET CONSTRAINT (або SET CONSTRAINTS):
Синтаксис команди дозволяє вказати вид перевірки, як для декількох визначених обмежень, так і для всіх обмежень, які можуть бути в SQL-запитах даної транзакції. Якщо в транзакції відкладаються одне або декількох обмежень, можна також використовувати команду SET CONSTRAINTS ALL IMMEDIATE перед фіксацією поточної транзакції для попередньої перевірки бази даних на відповідність відкладеним обмеженням:
- Якщо команда виконалася успішно, то зміни зроблені транзакцією задовольняють всім відкладеним обмеженням і можна успішно завершити транзакцію.
- Якщо при виконанні команди сталася помилка, значить, зміни зроблені транзакцією, привели до появи даних, які задовольняють одному або декільком відкладеним обмеженням. Для успішного фіксування транзакції необхідно провести додаткові зміни в базі даних, щоб дані не суперечили відкладеним обмеженням, і виконати повторну перевірку за допомогою команди SET CONSTRAINTS ALL IMMEDIATE. Якщо цього не зробити, при завершенні транзакції Oracle зробить аварійний відкат зроблених нею змін.
Раніше позначена проблема каскадних оновлень, являє собою хороший приклад доречного використання відкладаються обмежень. Перш за все, необхідно визначити таблицю ORDERS з її зовнішнім ключем на таблицю CUSTOMERS, як відкладається обмеження цілісності, яке Oracle8 перевірятиме за замовчуванням:
При використанні відкладали обмеження цілісності алгоритм виконання каскадного оновлення представляється гранично простим:
Відкладається обмеження цілісності зовнішнього ключа на детальної таблиці не тільки спрощує каскадне оновлення, але дозволяє убезпечити дані (так як не потрібно відключати обмеження цілісності, і дані, що змінюються конкурентними транзакціями, будуть відразу перевірятися на задоволення умовами, що накладається відкладати обмеженнями).
2. неунікальні індекси для первинних і унікальних ключів
За замовчуванням, при створенні первинного або унікального ключа на таблиці, як Oracle7, так і Oracle8 створюють унікальний індекс для підтримки кожного такого обмеження цілісності. Наприклад, попередня команда CREATE TABLE створює не тільки таблицю ORDERS, а й індекс (PKEY_ ORDERS) для підтримки оголошеного первинного ключа.
Якщо не планується часте відключення з подальшим включенням первинного або унікального ключа, використання унікального індексу, що створюється за замовчуванням, представляється кращим рішенням. Однак це може послужити причиною труднощів, якщо буде здійснюватися регулярне відключення і включення обмежень цілісності (найбільш часто обмеження цілісності відключають перед завантаженням великих обсягів даних в таблицю, наприклад, в сховищах даних). Контроль обмежень цілісності при завантаженні великих обсягів даних істотно знижує продуктивність. Якщо його виключити, можна значно збільшити швидкість завантаження. А після завантаження для визначення записів, які задовольняють включаються обмеженням цілісності, можна використовувати можливість отримання звіту про винятки.
На жаль, при роботі з таблицями розміром в кілька гігабайт, відключення і повторне включення первинного або зовнішнього ключа може виявитися неприйнятним: як Oracle7, так і Oracle8 видаляють відповідний унікальний індекс, який сервер використовує для підтримки обмеження цілісності. Це означає, що при повторному включенні обмеження після завантаження даних, СУБД повинна буде перебудувати відповідний унікальний індекс, що може зайняти годинник для великих таблиць.
Крім того, на таблицю накладається монопольна блокування (exclusive lock) на весь час перевтілення індексу, що робить таблицю недоступною для конкурентного зміни даних і інших операцій.
Oracle8 дозволяє користувачеві самостійно створити неунікальний індекс для підтримки первинного або унікального ключа. Замість того щоб дозволити сервера автоматично створити індекс, ви можете створити його для відключеного обмеження цілісності самостійно. Потім, при повторному включенні обмеження, Oracle8 буде використовувати це індекс. Наступні команди демонструють використання цієї можливості Oracle8.
Тепер, при відключенні первинного ключа таблиці ORDERS, індекс PKEY_ORD_ID залишиться. Запити, які будуть виконуватися в той час, коли обмеження відключено, зможуть використовувати цей індекс. Далі, при повторному включенні первинного ключа, індекс вже буде існувати, і СУБД не доведеться його перебудовувати з нуля, що дозволить значно прискорити операцію.
3. Миттєве включення обмеження
Для прискорення включення відключеного обмеження цілісності, в СУБД Oracle8 розширена функціональність пропозиції CONSTRAINT команди ALTER TABLE:
За замовчуванням, або коли вказується ключове слово VALIDATE, Oracle8 перевіряє цілісність всіх даних таблиці перед включенням обмеження (збігається з поведінкою Oracle7).
Щоб перевірити цілісність всіх даних таблиці, Oracle8 накладає монопольну блокування (Exclusive Lock) на таблицю, яка не тільки не дозволяє конкурентну оновлення даних на весь час перевірки, але дозволяє включати лише по одному обмеження цілісності одночасно. При роботі з великими таблицями, повторне включення одного за іншим всіх обмежень цілісності після пакетного завантаження істотних обсягів даних може зайняти багато часу.
При включенні обмеження з ключовим словом NOVALIDATE, Oracle8 не перевіряє наявні в таблиці дані на відповідність обмеження цілісності. Таким чином, Oracle8 може відразу включити перевірку обмеження цілісності для подальших транзакцій. Після включення всіх обмежень на таблиці ви можете використовувати ту ж команду з ключовим словом VALIDATE для перекладу кожного обмеження в "перевірене" стан. Переклад з "неперевіреного" в "перевірене" стан не вимагає монопольної блокування таблиці, таким чином, таблиця продовжує підтримувати конкурентні транзакції. Крім того, в Oracle8 для прискорення перевірки даних на відповідність обмеження використовується внутрішня паралельна обробка. Наступна послідовність команд демонструє, як можна оптимально включити відключене обмеження цілісності:
висновок
Розширення функціональності декларативних обмежень цілісності Oracle8 дозволяють поліпшити якість даних і спростити розробку додатків баз даних.
- Ви можете створити відкладаються обмеження цілісності і, потім, відкласти їх перевірку до кінця транзакції за допомогою нової SQL-команди SET CONSTRAINTS. Можливість відкладати перевірку умов обмежень полегшує реалізацію таких вимог складних систем обробки даних, як каскадне оновлення.
- Ви можете підсилити первинні і унікальні ключі, створивши відповідні неунікальні індекси. Коли ви вимкніть таке обмеження, Oracle8 не вилучено індекс, прискорюючи, таким чином, подальше включення обмеження.
- Ви можете включити обмеження в «не перевіреному" стані для того, щоб відразу забезпечити перевірку обмеження для змінюваних даних і дозволити конкурентні поновлення даних таблиці. Надалі, ви можете включити обмеження цілісності в "перевіреному" стані, щоб гарантувати, що всі дані таблиці йому задовольняють.
Короткий огляд обмежень цілісності
Check: Цей тип обмежень цілісності призначений для завдання допустимого безлічі значень (домену) стовпця або декількох стовпців таблиці. Таким чином, можна задавати більш складні правила забезпечення цілісності. Наприклад, ви можете визначити обмеження цілісності на таблиці CUSTOMERS, яке дозволить вводити тільки допустимі абревіатури назв країн в стовпець STATE, або на таблиці ORDERS, яке дозволить вводити тільки допустимі коди в стовпець STATUS. Обмеження цілісності визначається за допомогою SQL-команди і являє собою логічне вираження, яке Oracle міг би обчислити для кожного запису таблиці, використовуючи лише значення полів цьому ж записі. Логічний вираз має бути відносно простим: воно не може містити запитів до таблиць або послідовностей і не може містити виклики функцій SQL: SYSDATE, UID, USER або USERENV. Коли логічне вираз для цього запису приймає значення True (ІСТИНА) або UNKNOWN (не визначено), запис вважається задовольняє обмеження.
Not Null: Значення Null, часто використовується невірно. За замовчуванням, будь стовпець таблиці може містити невизначені значення. На стовпчику таблиці може бути визначено обмеження для виключення невизначеності з домену стовпці, запобігаючи, таким чином, поява невизначених значень в стовпці.
Первинний ключ: Обмеження цілісності первинний ключ таблиці призначений для забезпечення цілісності сутності (всієї таблиці), гарантуючи відсутність кількох однакових записів в таблиці. Для цього, первинний ключ гарантує, що в таблиці немає дублюються значень в стовпці або групі стовпців, що становлять ключ. Для таблиці може бути визначено лише один первинний ключ.
Унікальний ключ: Унікальний ключ, який так само називається альтернативним ключем, запобігає появі дублюються значень в стовпцях, що становлять ключ. На таблиці може бути визначено скільки завгодно унікальних ключів, за умови, що кожен складається з взаємно не перетинаються груп стовпців.
Зовнішній ключ: Зовнішній ключ (інакше обмеження посилальної цілісності) забезпечує взаємозв'язок між стовпцями і таблицями в реляційної базі даних. Посилальна цілісність гарантує, що кожне значення зовнішнього ключа збігається зі значенням відповідного йому головного ключа. Зовнішній ключ являє собою стовпець детальної таблиці, який посилається на первинний або унікальний ключ в головній (майстер) таблиці. Типи даних ключів повинні збігатися.