asyan.org
добавить свой файл
1 2
Лекція № 11

Управління транзакціями – 2 год

    1. Транзакції

      1. Транзакції та її властивості

      2. Порушення цілісності при виконанні транзакцій

      3. Обмеження цілісності та їх класифікація (за реалізацією, за часом, за областю)

    2. Реалізація декларативних обмежень БД засобами SQL

      1. Можливості SQL з задання декларативних обмежень

      2. Синтаксис обмежень операторів SQL

    3. Транзакції та паралелизм. Проблеми багатокористувацького доступу

      1. Основні проблеми паралелизму

      2. Робота транзакцій у суміші

      3. Конфлікти між транзакціями

      4. Способи розв'язання конфліктів між транзакціями. Серіалізація транзакцій.


1. Транзакції

1.1. Поняття транзакції. Транзакції та їх властивості

Транзакція - це неподільна, з погляду впливу на СУБД, послідовність операцій маніпулювання даними. Для користувача транзакція виконується за принципом "усі чи нічого", тобто або транзакція виконується цілком і переводить базу даних з одного цілісного стану в інший цілісний стан, або, якщо за якимись причинами, одна з дій транзакції нездійсненна, чи відбулося яке-небудь порушення роботи системи, база даних повертається у вихідний стан, що був до початку транзакції (відбувається відкат транзакції).

В однокористувальницьких системах транзакції - це логічні одиниці роботи, після виконання яких база даних залишається в цілісному стані. Транзакції також є одиницями відновлення даних після збоїв - відновлюючись, система ліквідує сліди транзакцій, що не встигли успішно завершитися в результаті програмного чи апаратного збою. Ці дві властивості транзакцій визначають атомарность (неподільність) транзакції. У багатокористувацьких системах, крім того, транзакції служать для забезпечення ізольованої роботи окремих користувачів - користувачам, що одночасно працюють з однією базою даних, здається, що вони працюють як би в однокористувальницькій системі і не заважають один одному.

Визначення 1. Транзакція - це послідовність операторів маніпулювання даними, що виконується як єдине ціле (все чи нічого) і переводить базу даних з одного цілісного стану в інший цілісний стан.

Транзакція має чотири важливі властивості, відомі як властивості АУІД:

  • (А) Атомарність. Транзакція виконується як атомарна операція - або виконується вся транзакція повністю, або вона повністю не виконується.

  • (У) Узгодженість. Транзакція переводить базу даних з одного узгодженого (цілісного) стану в інший узгоджений (цілісний) стан. Усередині транзакції узгодженість бази даних може порушуватися.

  • (І) Ізоляція. Транзакції різних користувачів не повинні заважати одна одній (наприклад, як якби вони виконувалися строго по черзі).

  • (Д) Довговічність. Якщо транзакція виконана, то результати її роботи повинні зберегтися в базі даних, навіть якщо в наступний момент відбудеться збій системи.

Транзакція звичайно починається автоматично з моменту приєднання користувача до СУБД і продовжується доти, поки не відбудеться одне з наступних подій:

  • Подано команду COMMIT WORK (зафіксувати транзакцію).

  • Подано команду ROLLBACK WORK (відкотити транзакцію).

  • Відбулося від'єднання користувача від СУБД.

  • Відбувся збій системи.

Команда COMMIT WORK завершує поточну транзакцію й автоматично починає нову транзакцію. При цьому гарантується, що результати роботи завершеної транзакції фіксуються, тобто зберігаються в базі даних.

Команда ROLLBACK WORK приводить до того, що всі зміни, зроблені поточною транзакцією, відкочуються, тобто скасовуються так, начебто їх узагалі не було. При цьому автоматично починається нова транзакція.

При від'єднанні користувача від СУБД відбувається автоматична фіксація транзакцій.

При збої системи відбуваються більш складні процеси. Коротко суть їх зводиться до того, що при наступному запуску системи відбувається аналіз транзакцій, які виконувалися до моменту збою транзакцій. Ті транзакції, для яких була подана команда COMMIT WORK, але результати роботи яких не були занесені в базу даних виконуються знову (накочуються). Ті транзакції, для яких не була подана команда COMMIT WORK, відкочуються.
^ 1.2. Порушення цілісності при виконанні транзакцій

Будь-яка база даних придатна до використання тільки тоді, коли її стан відповідає стану предметної області. Такі стани називають цілісними. Очевидно, що при зміні даних БД повинна переходити від одного цілісного стану до іншого. Однак, у процесі відновлення даних можливі ситуації, коли стан цілісності порушується.

Для ілюстрації можливого порушення цілісності бази даних розглянемо наступний приклад:

Приклад 1. Нехай є система, у якій зберігаються дані про підрозділи і працюючих у них співробітниках. Список підрозділів зберігається в таблиці DEPART(Dep_Id, Dep_Name, Dept_Kol), де Dept_Id - ідентифікатор підрозділу, Dept_Name - найменування підрозділу, Dept_Kol - кількість співробітників у підрозділі. Список співробітників зберігається в таблиці PERSON(Pers_Id, Pers_Name, Dept_Id), де Pers_Id - ідентифікатор співробітника, Pers_Name - ім'я співробітника, Dept_Id - ідентифікатор підрозділу, у якому працює співробітник:

Dept_Id

Dept_Name

Dept_Kol

1

Кафедра математики

3

2

Кафедра програмування

2

^ Таблиця 1. DEPART

Pers_Id

Pers_Name

Dept_Id

1

Іванов

1

2

Петров

2

3

Сидорчук

1

4

Пушняк

2

5

Шарко

1

^ Таблиця 2. PERSON

Обмеження цілісності цієї бази даних полягає в тому, що поле Dept_Kol не може заповнюватися довільними значеннями - це поле повинне містити кількість співробітників, що реально числяться в підрозділі.

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

  • Крок 1. Уставити співробітника в таблицю PERSON: INSERT INTO PERSON (6, Мутаков, 1)

  • Крок 2. Збільшити значення поля Dept_Kol: UPDATE DEPART SET Dept=Dept+1 WHERE Dept_Id=1

Якщо після виконання першої операції і до виконання другий відбудеться збій системи, то реально буде виконані тільки перша операція і база даних залишиться в нецілісному стані.

Саме щоб уникнути таких ситуацій у СУБД уводиться поняття транзакції - атомарної дії над БД, що переводить її з одного цілісного стану в інший цілісний стан. Іншими словами, транзакція - це послідовність операцій, що повинні чи бути усі виконані, чи всі не виконані (усе чи нічого). Методом контролю за транзакціями є ведення журналу, у якому фіксуються всі зміни, чинені транзакцією в БД. Якщо під час обробки транзакції відбувається збій, транзакція відкочується - з журналу поновлюється стан БД на момент початку транзакції.
^ 1.3. Обмеження цілісності та їх класифікація

Властивість (У) - узгодженість транзакцій - визначається наявністю поняття узгодженості бази даних.

Визначення 2. Обмеження цілісності - це деяке твердження, що може бути правдивим чи хибним в залежності від стану бази даних.

Прикладами обмежень цілісності можуть служити наступні твердження:

Приклад 2. Вік співробітника не може бути менше 18 і більше 65 років.

Приклад 3. Кожен співробітник має унікальний табельний номер.

Приклад 4. Співробітник зобов'язаний числитися в одному відділі.

Приклад 5. Сума накладної зобов'язана дорівнювати сумі добутків цін товарів на кількість товарів для всіх товарів, що входять у накладну.

Як видно з цих прикладів, деякі з обмежень цілісності є обмеженнями реляційної моделі даних (дів лекцію по обмеженнях цілісності реляційних БД). Приклад 3 - обмеження, що реалізує цілісність сутності. Приклад 4 - обмеження, що реалізує посилальну цілісність. Інші обмеження є досить довільними твердженнями (приклади 2 і 5). Будь-яке обмеження цілісності є семантичним поняттям, тобто з'являється як наслідок визначених властивостей об'єктів предметної області і/чи їхніх взаємозв'язків.

Визначення 3. База даних знаходиться в узгодженому (цілісному) стані, якщо виконані (задоволені) всі обмеження цілісності, визначені для бази даних.

Узгодженість бази даних є формальною властивістю бази даних. База даних не розуміє "змісту" збережених даних. "Змістом" даних для СУБД є весь набір обмежень цілісності. Якщо всі обмеження виконані, то СУБД вважає, що дані коректні.

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

  1. Відмовлення виконати "незаконну" операцію.

  2. Виконання дій, що її компенсують.

Класифікація обмежень цілісності. Обмеження цілісності можна класифікувати кількома способами:

  • За способами реалізації.

  • За часом перевірки.

  • За областю дії.

Класифікація обмежень цілісності за способами реалізації. Кожна система має свої засоби підтримки обмежень цілісності. Розрізняють два способи реалізації:

  • Декларативна підтримка обмежень цілісності.

  • Процедурна підтримка обмежень цілісності.

Декларативна підтримка обмежень цілісності полягає у визначенні обмежень засобами мови визначення даних (DDL - Data Definition Language). Звичайно засоби декларативної підтримки цілісності (якщо вони є в СУБД) визначають обмеження на значення доменів і атрибутів, цілісність сутностей (потенційні ключі відношень) і посилальну цілісність (цілісність зовнішніх ключів). Декларативні обмеження цілісності можна використовувати при створенні і модифікації таблиць засобами мови DDL чи у вигляді окремих тверджень (ASSERTION).

Процедурна підтримка обмежень цілісності полягає у використанні тригерів і збережених процедур.

Не всі обмеження цілісності можна реалізувати декларативно. Прикладом такого обмеження може служити вимога, що стверджує, що поле Dept_Kol таблиці DEPART повинне містити кількість співробітників, що реально значаться в підрозділі. Для реалізації цього обмеження необхідно створити тригер, який запускається при вставці, модифікації і видаленні записів у таблиці PERSON, що коректно змінює значення поля Dept_Kol. Наприклад, при вставці в таблицю PERSON нового рядка, тригер збільшує на одиницю значення поля Dept_Kol, а при видаленні рядка - зменшує. При модифікації записів у таблиці PERSON можуть знадобитися і більш складні дії. Модифікація запису в таблиці PERSON може полягати в тім, що ми переводимо співробітника з одного відділу в інший, змінюючи значення в поле Dept_Id. При цьому необхідно в старому підрозділі зменшити кількість співробітників, а в новому - збільшити.

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

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

  • При декларуванні (оголошенні) обмеження текст обмеження зберігається у вигляді деякого об'єкта СУБД, а для реалізації обмеження використовуються убудовані в СУБД функції, і тоді цей код являє собою внутрішні функції ядра СУБД.

  • При декларуванні обмеження СУБД автоматично генерує тригери, що виконують необхідні дії по перевірці обмежень.

Класифікація обмежень цілісності за часом перевірки. За часом перевірки обмеження поділяються на:

  • Обмеження, що негайно перевіряються.

  • Обмеження з відкладеною перевіркою.

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

Обмеження з відкладеною перевіркою перевіряється в момент фіксації транзакції оператором COMMIT WORK. Усередині транзакції обмеження може не виконуватися. Якщо в момент фіксації транзакції виявляється порушення обмеження з відкладеною перевіркою, то транзакція відкочується.

Класифікація обмежень цілісності за областю дії. За областю дії обмеження поділяються на:

  • Обмеження домену

  • Обмеження атрибута

  • Обмеження кортежу

  • Обмеження відносини

  • Обмеження бази даних

Обмеження цілісності домену являють собою обмеження, що накладаються тільки на допустимі значення домену. Фактично, обмеження домену зобов'язані бути частиною визначення домену Наприклад, обмеженням домену "Вік співробітника" може бути умова "Вік співробітника не менш 18 і не більш 65".

^ Перевірка обмеження. Обмеження домену самі по собі не перевіряються. Якщо на певному домені заснований атрибут, то обмеження відповідного домену стає обмеженням цього атрибута.

Обмеження цілісності атрибута являють собою обмеження, що накладаються на припустимі значення атрибута внаслідок того, що атрибут заснований на певному домені. Обмеження атрибута в точності збігаються з обмеженнями відповідного домену. Відмінність обмежень атрибута від обмежень домену в тому, що обмеження атрибута перевіряються.

Якщо логіка предметної області така, що на значення атрибута необхідно накласти додаткові обмеження, крім обмежень домену, то такі обмеження переходять у наступну категорію.

^ Перевірка обмеження. Обмеження атрибута є обмеженням, що негайно перевіряється. Дійсно, обмеження атрибута не залежить ні від яких інших об'єктів бази даних, крім домену, на якому заснований атрибут. Тому ніякі зміни в інших об'єктах не можуть уплинути на істинність обмеження.

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

Приклад 6. Атрибут "Вік співробітника" у таблиці "Спецпідрозділ", може мати додаткове обмеження "Вік співробітника не менш 25 і не більш 45", крім того, що цей атрибут уже має обмеження, обумовлене доменом - "Вік співробітника не менш 18 і не більш 65".

Наведене обмеження кортежу, по суті, є додатковим обмеженням на значення одного атрибута. У цьому випадку припустимі два рішення. Можна оголосити новий домен "Вік співробітника спецпідрозділу" і тоді обмеження кортежу стає обмеженням домену й атрибута, або розглядати це обмеження саме як обмеження кортежу. Обидва рішення мають свої позитивні і негативні сторони. Отут є деякі можливості для оптимізації. Формально, при зміні значення даного атрибута необхідно перевірити два обмеження - обмеження атрибута й обмеження кортежу. Але в даному випадку обмеження кортежу сильніше обмеження атрибута і досить перевірити тільки обмеження кортежу. Розумно побудована СУБД могла б виявляти такі випадки і зменшувати зайву роботу.

^ Перевірка обмеження. До моменту перевірки обмеження кортежу повинні бути перевірені обмеження цілісності атрибутів, що входять у цей кортеж.

Обмеження кортежу є обмеженням, що негайно перевіряється. Дійсно, обмеження кортежу не залежить ні від яких інших об'єктів бази даних, крім атрибутів, що входять до складу кортежу. Тому ніякі зміни в інших об'єктах не можуть уплинути на істинність обмеження.

Обмеження цілісності відносини становлять обмеження, що накладаються тільки на допустимі значення окремого відношення, і не є обмеженням цілісності кортежу. Вимога, що обмеження стосується окремого відношення, означає, що для його перевірки не потрібно інформації про інші відношення (у тому числі не потрібно посилань по зовнішньому ключеві на кортежі цього ж відношення).

Приклад 9. Обмеження цілісності сутності, що задається потенційним ключем відношення, є обмеженням відношення, тому що для його перевірки необхідно мати інформацію про всі кортежі відношення (більш точно, про всі зайняті у даний момент значення потенційного ключа).

Приклад 10. Обмеження цілісності, обумовлені наявністю функціональних, багатозначних залежностей і залежностей з'єднання, є обмеженнями відношення.

Приклад 11. Припустимо, що у відношенні PERSON (див. приклад 1) задане наступне обмеження - у кожному відділі повинне бути не менш двох співробітників. Це обмеження можна сформулювати так - кількість рядків з однаковим значенням Dept_Id повинне бути не менше 2.

Зауваження. Для того щоб ввести в дію (оголосити) це обмеження, необхідно, щоб у відношення вже були вставлені деякі кортежі.

Приклад 12. Обмеження цілісності, обумовлене вимогою, що деяка таблиця повинна бути не порожня, є обмеженнями відношення.

^ Перевірка обмеження. До моменту перевірки обмеження відношення повинні бути перевірені обмеження цілісності кортежів цього відношення.

Обмеження відношення може бути як обмеженням, що негайно перевіряється, так і обмеженням з відкладеною перевіркою.

Обмеження відношення, що є обмеженням потенційного ключа є обмеженням, що негайно перевіряється.

Обмеження, визначене наявністю функціональної залежності атрибутів також є обмеженням, що негайно перевіряється.

Обмеження ж, визначені багатозначною чи залежністю залежністю з'єднання є обмеженнями з відкладеною перевіркою. Дійсно, ці обмеження вимагають, щоб кортежі вставлялися і видалялися цілими групами. Це неможливо зробити, якщо виконувати перевірку після кожної одиночної вставки чи видалення кортежу.

Обмеження цілісності бази даних становлять обмеження, що накладаються на значення двох чи більше зв'язаних між собою відношень (у тому числі відношення може бути зв'язане саме із собою).

Приклад 13. Обмеження цілісності посилань, що задається зовнішнім ключем відношення, є обмеженням БД.

Приклад 14. Обмеження на таблиці DEPART і PERSON із приклада 1 є відношенням бази даних, тому що воно зв'язує дані, розміщені в різних таблицях.

^ Перевірка обмеження. До моменту перевірки обмеження бази даних повинні бути перевірені обмеження цілісності відношень.

Обмеження бази даних може бути як обмеженням, що негайно перевіряється, так і обмеженням з відкладеною перевіркою.

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

^ 2. Реалізація декларативних обмежень БД засобами SQL

2.1. Можливості SQL з задання декларативних обмежень

У СУБД різних постачальників початок транзакції може задаватися явно (наприклад, командою ^ BEGIN TRANSACTION), або передбачатися неявним (так визначено в стандарті SQL), тобто чергова транзакція відкривається автоматично відразу ж після вдалого чи невдалого завершення попередньої. Для завершення транзакції звичайно використовують команди SQL:

  • ^ COMMIT - успішно завершити транзакцію

  • ROLLBACK - відкотити транзакцію, тобто повернути БД у стан, у якому вона знаходилася на момент початку транзакції.

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

  • оператор COMMIT означає успішне завершення транзакції, усі зміни, внесені в базу даних робляться постійними

  • оператор ROLLBACK перериває транзакцію і скасовує усі внесені нею зміни

  • успішне завершення програми, що ініціювала транзакцію, означає успішне завершення транзакції (як використання COMMIT)

  • помилкове завершення програми перериває транзакцію (як ROLLBACK)

Приклад явно заданої транзакції:

BEGIN TRANSACTION; /* Почати транзакцію */

DELETE ...; /* Зміни */

UPDATE ...; /* даних */

if (виявлена_помилка) ROLLBACK;

else COMMIT; /* Завершити транзакцію */

Приклад неявно заданої транзакції:

СOMMIT; /* Закінчення попередньої транзакції */

DELETE ...; /* Зміни */

UPDATE ...; /* даних */

if (виявлена_помилка) ROLLBACK;

else COMMIT; /* Завершити транзакцію */

Стандарт SQL не передбачає процедурних обмежень цілісності, реалізованих за допомогою тригерів і збережених процедур. У стандарті SQL 92 відсутнє поняття "тригер", хоча тригери є у всіх промислових СУБД SQL-типу. Таким чином, реалізація обмежень засобами конкретної СУБД має більшу гнучкість, ніж з використанням винятково стандартних засобів SQL.

Стандарт SQL дозволяє задавати декларативні обмеження такими способами:

  • Як обмеження домену.

  • Як обмеження, що входять у визначення таблиці.

  • Як обмеження, що зберігаються в базі даних у вигляді незалежних тверджень (assertion).

Допускаються як обмеження, що перевіряються негайно, так і обмеження з відкладеною перевіркою. Режим перевірки відкладених обмежень можна в будь-який момент змінити так, щоб обмеження перевірялося:

  • Після виконання кожного оператора, що змінює вміст таблиці, якого стосується дане обмеження.

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

  • У будь-який проміжний момент, якщо користувач ініціює перевірку.

При визначенні обмеження вказується тип перевірки обмеження - чи є це обмеження таким, що не відкладається (^ NOT DEFERRED), чи може бути відкладене (DEFERRED). В другому випадку можна задати процедуру за замовчуванням: перевіряти негайно чи перевіряти по завершенню транзакції.

Елементи процедурності все-таки присутні в стандарті SQL у вигляді так званих дій, що виконуються по посиланню (referential triggered actions). Ці дії визначають, що буде відбуватися при зміні значення батьківського ключа, на який посилається деякий зовнішній ключ. Ці дії можна задавати незалежно для операцій відновлення (ON UPDATE) чи для операцій видалення (ON DELETE) записів у батьківському відношенні. Стандартом SQL визначається 4 типи дій, що виконуються по посиланню:

  • CASCADE. Зміни значення батьківського ключа автоматично приводять до таких же змін зв'язаного з ним значення зовнішнього ключа. Видалення кортежу в батьківському відношенні приводить до видалення зв'язаних з ним кортежів у дочірньому відношенні.

  • SET NULL. Усі зовнішні ключі, що посилаються на обновлений чи вилучений батьківський ключ, дістають значення NULL.

  • ^ SET DEFAULT. Усі зовнішні ключі, що посилаються на обновлений чи вилучений батьківський ключ дістають значення, прийняті за замовчуванням для цих ключів.

  • ^ NO ACTION. Значення зовнішнього ключа не змінюються. Якщо операція приводить до порушення посилальної цілісності (з'являються "висячі" посилання), то така операція не виконується.

Як видно, дії, що виконуються по посиланню, фактично є убудованими в СУБД тригерами. Дії типу CASCADE, SET NULL і SET DEFAULT є компенсуючими операціями, що викликаються при спробі порушити посилальну цілісність.

^ 2.2. Синтаксис обмежень стандарту SQL

Типи обмежень у операторах DDL. Поняття обмеження використовується в багатьох операторах визначення даних (DDL).

Обмеження типу CHECK. Обмеження check::=
CHECK Предикат

Обмеження типу CHECK містить предикат, що може приймати значення TRUE, FALSE і UNKNOWN (NULL). Обмеження типу CHECK може бути використане як частина опису домену, таблиці, стовпця, таблиці чи окремого обмеження цілісності - ASSERTION. Обмеження вважається порушеним, якщо предикат обмеження приймає значення FALSE.

Обмеження таблиці й обмеження стовпця. Обмеження таблиці ::=
[CONSTRAINT Ім'я обмеження]
{
{^ PRIMARY KEY (Ім'я стовпця.,..)}
| {UNIQUE (Ім'я стовпця.,..)}
| {FOREIGN KEY (Ім'я стовпця.,..) REFERENCES Ім'я таблиці [(Ім'я стовпця.,..)] [Посилальна специфікація]}
| { Обмеження check }
}
[Атрибути обмеження]

Обмеження стовпця::=
[CONSTRAINT Ім'я обмеження]
{
{^ NOT NULL}
| {PRIMARY KEY}
| {UNIQUE}
| {REFERENCES Ім'я таблиці [(Ім'я стовпця)] [Посилальна специфікація]}
| { Обмеження check }
}
[Атрибути обмеження]

Обмеження таблиці й обмеження стовпця таблиці входять як частина опису відповідно таблиці чи стовпця таблиці. Обмеження таблиці може відноситися до кількох стовпців таблиці. Обмеження стовпця відноситься тільки до одного стовпця таблиці. Будь-яке обмеження стовпця можна описати як обмеження таблиці, але не навпаки.

Обмеження таблиці чи стовпця можуть мати найменування, за допомогою якого надалі можна скасовувати це чи обмеження змінювати час його перевірки.

Обмеження PRIMARY KEY. Обмеження PRIMARY KEY для таблиці чи стовпця означає, що група з одного чи декількох стовпців утворять потенційний ключ таблиці. Це означає, що комбінація значень у PRIMARY KEY повинна бути унікальною для кожного рядка таблиці. Дубльовані значення чи значення, що містять NULL, будуть відкинуті. Для однієї таблиці може бути визначене єдине обмеження PRIMARY KEY. У термінах стандарту SQL це називається первинним ключем таблиці.

Обмеження UNIQUE. Обмеження UNIQUE для таблиці чи стовпця означає, що група з одного чи декількох стовпців утворить потенційний ключ таблиці, у якому допускаються значення NULL. Це означає, що два рядки, що містять однакові і не рівні NULL-значення, вважаються порушуючими унікальність і не допускаються. Два рядки, що містять NULL-значення вважаються різними і допускаються. Для однієї таблиці може бути визначено кілька обмежень UNIQUE.

Обмеження FOREIGN KEY і REFERENCES. Обмеження FOREIGN KEY... REFERENCES... для таблиці й обмеження REFERENCES... для стовпця визначають зовнішній ключ таблиці. Обмеження REFERENCES... для стовпця визначає простий зовнішній ключ, тобто ключ, що складається з одного стовпчика. Обмеження FOREIGN KEY... REFERENCES... для таблиці може визначати як простий, так і складний зовнішній ключ, тобто ключ, що складається з декількох стовпчиків таблиці. Стовпець чи група стовпців таблиці, на яку посилається зовнішній ключ, повинна мати обмеження PRIMARY KEY чи UNIQUE. Стовпці, на які посилається зовнішній ключ, повинні мати той же тип даних, що і стовпці, що входять до складу зовнішнього ключа. Таблиця може мати посилання на себе. Обмеження зовнішнього ключа порушується, якщо значення, що є присутнім у зовнішньому ключі, не збігаються зі значеннями відповідного ключа батьківської таблиці для жодного рядка з батьківської таблиці. Операції, що приводять до порушення обмеження зовнішнього ключа, відкидаються.

Обмеження NOT NULL. Обмеження NOT NULL стовпця не допускає появи в стовпці NULL-значень.

Посилальна специфікація. Посилальна специфікація::=
[MATCH {FULL | PARTIAL}]
[^ ON UPDATE {CASCADE | SET NULL | SET DEFAULT | NO ACTION}]
[ON DELETE {CASCADE | SET NULL | SET DEFAULT | NO ACTION}]

Посилальна специфікація визначає характеристики зовнішнього ключа таблиці.

Речення MATCH {FULL | PARTIAL}. Речення MATCH FULL вимагає повного збігу значень зовнішнього і первинного ключів. Речення MATCH PARTIAL допускає частковий збіг значень зовнішнього і первинного ключів. Речення MATCH може бути також пропущеним. Для випадку MATCH PARTIAL у дочірній таблиці можуть з'явитися рядки, що мають значення зовнішнього ключа, що неунікально збігаються зі значеннями батьківського ключа.

Речення ON UPDATE і ON DELETE. Речення ON UPDATE і ON DELETE визначають дії, що виконуються по посиланню.

Атрибути обмеження. Атрибути обмеження::=
{DEFERRABLE [^ INITIALLY DEFERRED | INITIALLY IMMEDIATE]}
| {NOT DEFERRABLE}

Атрибути обмеження визначають, у який момент перевіряються обмеження. Обмеження може бути визначене як NOT DEFERRABLE ( щоневідкладається) чи DEFERRABLE ( щовідкладається). Якщо атрибути обмеження не зазначені, то за замовчуванням приймається NOT DEFERRABLE.

Оператори, що застосовують обмеження. Стандарт SQL описує наступні операторы, у яких може бути використані обмеження:

  • CREATE DOMAIN - створити домен

  • ALTER DOMAIN - змінити домен

  • DROP DOMAIN - видалити домен

  • CREATE TABLE - створити таблицю

  • ALTER TABLE - змінити таблицю

  • DROP TABLE - видалити таблицю

  • CREATE ASSERTION - створити твердження

  • DROP ASSERTION - видалити твердження

  • COMMIT WORK - зафіксувати транзакцію

  • SET CONSTRAINTS - установити момент перевірки обмежень

Синтаксис операторов описан в специальной литературе, например, ^ CREATE DOMAIN:

CREATE DOMAIN Ім'я домену [AS] Тип даних

[DEFAULT Значення за замовчуванням]
[Ім'я обмеження] Обмеження check [Атрибути обмеження]

Цей оператор задає домен, на основі якого можна визначати стовпці таблиць. Оскільки ім'я стовпця, що буде заснований на цьому домені заздалегідь невідоме, то в обмеженні CHECK домену для посилання на значення цього домену використовується ключове слово VALUE. У конкретній таблиці СУБД замінить слово VALUE на ім'я стовпця таблиці.

Оператор ^ ALTER DOMAIN змінює наявний домен. Стандарт забороняє вносити кілька змін однією командою ALTER DOMAIN. Якщо потрібно видалити обмеження CHECK і додати значення за замовчуванням, то доведеться виконати два операторів ALTER DOMAIN.

^ ALTER DOMAIN Ім'я домену
{SET DEFAULT Значення за замовчуванням}
| {DROP DEFAULT}
| {ADD [Ім'я обмеження] Обмеження check [Атрибути обмеження]}
| {^ DROP CONSTRAINT Ім'я обмеження}

Оператор DROP DOMAIN знищує наявних домен:

DROP DOMAIN Ім'я домену CASCADE | RESTRICT

Якщо зазначена опція RESTRICT, то домен не знищується, якщо є посилання на нього зі стовпців таблиць. Якщо зазначена опція CASCADE, то відбуваються наступні дії:

  • Тип даних домену передається стовпцям, заснованим на цьому домені.

  • Якщо стовпець не має значення за замовчуванням, а для домену значення за замовчуванням визначено, то воно стає значенням за замовчуванням для стовпця.

  • Всі обмеження домену стають обмеженнями стовпця.

Оператор ^ CREATE TABLE (синтаксис приведений не цілком - пропущені опції створення тимчасових таблиць) створює таблицю бази даних:

CREATE TABLE Ім'я таблиці
( {Визначення стовпця | [Обмеження таблиці]}.,..)

Визначення стовпця::=
Ім'я стовпця {Ім'я домену | Тип даних [Розмір]}
[Обмеження стовпця…]
[DEFAULTЗначення за замовчуванням]

У таблиці має бути не менш одного визначення стовпця. У таблиці може бути визначено кілька обмежень (у тому числі і жодного).

Кожен стовпець повинен мати ім'я і бути визначений на деякому типі даних чи на деякому домені. Обмеження домену стають обмеженнями стовпця. Крім того, стовпець може мати додаткові обмеження. Якщо домен має значення за замовчуванням і у визначенні стовпця визначене значення за замовчуванням, то значення для стовпця перекриває значення для домену.

Оператор ^ DROP TABLE дозволяє змінювати наявну таблицю:

DROP TABLE Ім'я таблиці CASCADE | RESTRICT

У таблиці можна видаляти чи додавати стовпці і/чи обмеження. Крім того, для стовпців можна змінювати значення за замовчуванням. Якщо зазначений параметр RESTRICT, то таблиця видаляється тільки якщо немає ніяких посилань на цю таблицю в інших обмеженнях чи представленнях. Якщо зазначений параметр CASCADE, то видаляються також і всі об'єкти, що посилаються на цю таблицю.

Оператор ^ CREATE ASSERTION дозволяє створювати твердження - тобто обмеження, що не є частиною визначення домену, чи стовпчики таблиці:

CREATE ASSERTION Ім'я твердження
Обмеження check

[^ Атрибути обмеження]

Предикат CHECK, що входить у визначення твердження, може бути досить складним і містити посилання на кілька таблиць.

Оператор ^ DROP ASSERTION дозволяє видаляти наявне твердження: DROP ASSERTION Ім'я твердження.

Оператор COMMIT WORK фіксує транзакцію. При цьому перевіряються усі відкладені до кінця транзакції обмеження. Якщо одне з обмежень не виконується, то транзакція відкочується.

Оператор ^ SET CONSTRAINT дозволяє під час виконання транзакції змінювати момент перевірки усіх (ALL) чи деяких обмежень:

SET CONSTRAINT {Ім'я обмеження.,.. | ALL}
{DEFERRED | IMMEDIATE}

Цей оператор діє тільки на обмеження, визначені як DEFERRABLE (потенційно відкладані). Якщо обмеження A знаходилося в стані IMMEDIATE (перевіряються негайно), то оператор SET CONSTRAINT A DEFERRED переводить його в стан DEFERRED (з відкладеною перевіркою) і тоді всі операції, що потенційно можуть порушити це обмеження, будуть виконуватися без перевірки. Перевірка буде зроблена наприкінці транзакції чи в момент подачі команди SET CONSTRAINT A IMMEDIATE.

Таким чином, база даних знаходиться в узгодженому стані, якщо для цього стану виконані всі обмеження цілісності. Стандарт мови SQL підтримує тільки декларативні обмеження цілісності, реалізовані як:

  • Обмеження домену.

  • Обмеження, що входять у визначення таблиці.

  • Обмеження, що зберігаються в базі даних у виді незалежних тверджень (assertion).

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

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




следующая страница >>