Обмеження рівня атрибутів та кортежів відношення

Тема 8.1. Обмеження в SQL

1. Обмеження первинних та зовнішніх ключів відношень

2. Обмеження рівня атрибутів та кортежів; відношення.

3. Модифікація обмежень.

Обмеження первинних та зовнішніх ключів відношень

Засоби в SQL, які забезпечують цілісність бази даних, носять назву активних елементів. Активний елемент – це вираз або команда, які, будучі об’явленими і збереженими в базі даних, виконуються по мірі необхідності у визначені моменти часу у зв’язку із подіями стосовно змін у стані бази даних, при яких деяка логічна умова отримує значення true. До активних елементів відносяться команди, шо забезпечують цілісність бази даних при створенні первинних і зовнішніх ключів відношень, при внесенні компонента до певного атрибута, створення кортежу або відношення, а також при модифікації бази даних командами DELETE, ADD, UPDATE, INSERT.

Об’ява первинного ключа. Відношення може мати тільки один первинний ключ (primary key). У рамках виразу CREATE TABLE існує два способи об’яви первинного ключа, які відображують кількість атрибутів (один або декілька), що утворюють первинний ключ :

· Додати до схеми відношення до елементів, що визначають ключовий атрибут, службові слова PRIMARY KEY.

· Додати до списку елементів схеми відношення речення PRIMARY KEY із списком ключових атрибутів, заключних у круглі дужки. Спосіб може використовуватися також для списку, що складається з одного атрибуту

Результат об’яви підмножини S атрибутів у якості ключа відношення R слід трактувати так:

1) два любих кортежі R не можуть співпадати у значеннях усіх ключових атрибутів одночасно; люба спроба вставки нового кортежу або оновлення існуючого кортежу, приводячи до порушення цього правила, буде відкинута системою, як недопустима операція;

2) компоненти ключових атрибутів S не можуть містити значення NULL.

Приклад 8.1. Об’явити первинний ключ відношення MovieStar за допомогою двох способів.

1) CREATE TABLE MovieStar (

2) name CHAR(30) PRIMARY KEY,

3) address VARCHAR (255),

4) gender CHAR (1),

5) birthday DATE

);

Рис.8.1. Надання атрибуту name статусу первинного ключа (спосіб 1).

1) CREATE TABLE MovieStar (

2) name CHAR(30),

3) address VARCHAR (255),

4) gender CHAR (1),

5) birthday DATE,

6) PRIMARY KEY (name)

);

Рис.8.1. Надання атрибуту name статусу первинного ключа (спосіб 2).

Ще один спосіб об’яви ключа відношення зв’язаний з використанням службового слова UNIQUE, яке має наступні особливості:

1) Відношення може мати декілька ключів UNIQUE, але тільки один первинний ключ PRIMARY KEY

2) У той час, коли компонентам атрибутів ключа PRIMARY KEY заборонено присвоювати значення NULL, це дозволяється для компонентів ключа UNIQUE. Крім того, правило, що забороняє спів падання вмісту компонентів ключових атрибутів декількох кортежів, у ключі UNIQUE може бути порушено, якщо компонентам присвоєні значення NULL.

Розглянемо, яким чином система слідкує за виконанням обмеження ключів. Це відбувається при спробі модифікації ключа відношення R командами UPDATE та INSERT шляхом перевірки умови обмеження ключа.

Як правило, СУБД пропонує створити індекс для первинного ключа відношення або для його атрибуту, що помічений як ключ UNIQUE

Приклад

Створити для відношення Movie індекс для атрибуту year з ім’ям YearIndex за допомогою виразу:

CREATE TABLE INDEX YearIndex ON Movie (year);

Створення індексу YearIndex містить і об’яву обмеження унікальності значення атрибуту year. Перевірка унікальності відбувається, коли виконується команда вставки кортежу або оновлення значень ключових атрибутів, шляхом використання індексу для швидкого перегляду у відношенні кортежу з тими же значеннями ключових атрибутів. Якщо такий кортеж існує, то система забороняє модифікацію відношення.

Зовнішні ключі.

SQL дозволяє об’явити один або декілька атрибутів відношення у якості зовнішнього ключа (foreign key), що посилається (references) на деякі атрибути другого (або того ж самого) відношення. Результат об’яви зовнішнього ключа слід трактувати так:

1. Атрибути, що адресуються зовнішнім ключем, повинні бути об’явлені як PRIMARY KEY або UNIQUE.

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

Зовнішній ключ дозволяє дві форми об’яви, а саме:

1. Якщо зовнішній ключ містить один атрибут, то в об’яві атрибуту повинно бути речення з посиланням на адресовану таблицю і, адресований атрибут цієї таблиці, має бути ключем (PRIMARY KEY або UNIQUE)
REFERENCES <таблиця> (<атрибут>),

2. Якщо зовнішній ключ має декілька ключових атрибутів, то його об’ява здійснюється окремим реченням у команді CREATE TABLE
FOREIGN KEY <атрибути> REFERENCES <таблиця> (<атрибути>)

Приклад 8.2. Необхідно об’явити відношення Studio (name, address, presC#), що має первинний ключ name і зовнішній ключ presC#, який посилається на атрибут cert# відношення MovieExec (name, address, cert#, netWorth).

CREATE TABLE Studio (

name CHAR (30) PRIMARY KEY,

address VARCHAR (255),

presC# INT UNIQUE REFERENCES MovieExec (cert#)

);

Альтернативна форма об’яви:

CREATE TABLE Studio (

name CHAR (30) PRIMARY KEY,

address VARCHAR (255),

presC# INT UNIQUE,

FOREIGN KEY (presC#) REFERENCES MovieExec (cert#)

);

Рис.8.2. Об’ява зовнішніх ключів.

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

1. стратегія відміни операції модифікації (застосовується по умовчанню),

2. стратегія каскадної модифікації,

3. стратегія “присвоїти значення NULL”.

Варіанти зв’язків між батьківським і дочірнім відношеннями приведені на рис.8.2.1. Умови змін зовнішніх ключів у батьківських записах приведені у табл.8.1, а у дочірніх записах у – табл..8.2 стосовно стратегії відміни операції модифікації.

Рис.8.2.1. Типи зв’язків між відношеннями: О - обов’язковий; Н – не обов’язковий

Таблиця 8.1

Умови допустимості змін зовнішніх ключів у батьківських записах

Таблиця 8.2.

Умови допустимості змін зовнішніх ключів у дочірніх записах

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

Приклад 8.3. Для відношення MovieExec забезпечити використання стратегії 3 (“присвоїти значення NULL”) при застосуванні команди DELETE і стратегії 2 (каскадна модифікація) при застосуванні команди UPDATE.

1) CREATE TABLE Studio (

2) name CHAR (30) PRIMARY KEY,

3) address VARCHAR (255),

4) presC# INT UNIQUE REFERENCES MovieExec (cert#)

5) ON DELETE SET NULL

6) ON UPDATE CASCADE

);

Рис.8.3. Вибір стратегії для забезпечення обмеження посилальної цілісності.

Реально ситуація згідно прикладу 7.3 може виникнути, коли, наприклад, звільнився президент деякої студії. У цьому випадку слід видалити у відношенні MovieExec відповідний кортеж, а у відношенні Studio, маючи на увазі, що деякий час студія буде обходитися без президента, присвоїти NULL значенню компонента presC# кортежу, що містить дані цієї студії.

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

1. Групування декількох команд SQL (в даному випадку двох інструкцій вставки кортежів: одного – у відношення Studio, а другого – в MovieExec) у неподільну логічну одиницю роботи – транзакцію.

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

Такими командами є DEFERABLE і NOT DEFERABLE. Остання команда використовується по умовчанню і передбачає, що система зразу виконує усі необхідні перевірки, якщо вони передбачені. Перша команда (DEFERABLE) передбачає, що перевірки посилальної цілісності виконуються після завершення поточної транзакції. Команда має різновиди, що задаються двома службовими словами INITIALLY DEFERRED (перевірка умов відкладається, якщо вона не задана примусово) і INITIALLY IMMEDIATE (перевірка умов не відкладається, але вона може бути уведена в любий момент).

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

1) CREATE TABLE Studio (

2) name CHAR (30) PRIMARY KEY,

3) address VARCHAR (255),

4) presC# INT UNIQUE

5) REFERENCES MovieExec (cert#)

6) DEFERABLE INITIALLY DEFERRED

);

Рис.8.4. Використання інструкції відкладеної перевірки зовнішнього ключа.

Обмеження рівня атрибутів та кортежів відношення

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

1) як обмеження конкретного атрибута, задане у визначенні атрибута в контексті об’яви схеми відношення;

2) як обмеження рівня кортежу в цілому; таке обмеження оформлюється у вигляді окремого речення при об’яві відношення.

Найбільш поширений спосіб – це використання службового слова CHECK. Обмеження CHECK рівня атрибута перевіряється кожний раз, коли компонент кортежу, що відноситься до цього атрибута, отримує нове значення (при виконанні команд UPDATE або INSERT).

Приклад 8.5. Забезпечити, щоб сертифікаційні номери президентів кіностудій включали не менше шести цифр. Для рішення задачі достатньо, щоб у відношенні Studio, рядок об’яви атрибута presC# мав вигляд

presC# INT UNIQUE REFERENCES MovieExec (cert#)

CHECK (presC# >= 100000)

Приклад 8.6. Забезпечити, щоб атрибут gender у відношенні MovieStar приймав значення або ‘M’, або ‘F’. Для рішення задачі необхідно, щоб атрибут gender був об’явлен у схемі відношення MovieStar реченням:

gender CHAR (1) CHECK (gender IN (‘M’, ‘F’)),

Обмеження CHECK рівня кортежу регламентує умови включення кортежів у відношення R. Речення CHECK в об’яві CREATE TABLE розміщується після списку атрибутів, ключів і зовнішніх ключів. Речення містить умовний вираз, заключний у круглі дужки, який оформлюється згідно правил, що застосовуються до речення WHERE.

Приклад 8.6.2. В об’яві схеми відношення MovieStar передбачити обмеження, що в імені актора не може бути присутнім префікс “Ms.”, якщо актор є чоловіком

1) Create table MovieStar (

2) name CHAR (30) PRIMARY KEY,

3) address VARCHAR (255),

4) gender CHAR (1),

5) birthday DATE,

6) CHECK (gender = ‘F’ OR name NOT LIKE ‘Ms.%’)

);

Рис.8.5. Приклад обмеження CHECK рівня кортежу

Умова обмеження є TRUE для кожного кортежу, що містить інформацію про актрису або про чоловіка, чиє ім’я не починається з префікса “Ms.” У той же час, кортеж про актора – чоловіка, ім’я якого містить префікс “Ms.”, вважається недопустимим.

3. Модифікація обмежень.

Команди SQL дозволяють додати, змінити або видалити будь-яке обмеження у любий момент. Спосіб модифікації залежить від того, до якого “об’єкта відноситься обмеження – атрибута, таблиці або до схеми бази даних у цілому.

Щоб мати можливість вносити зміни або видаляти існуючі обмеження, останнім необхідно присвоювати ім’я. Для цього достатньо передувати об’яві обмеження службове слово CONSTRAIT і відповідне ім’я.

Приклад 8.7. Присвоїти ім’я NoAndro обмеженню рівня атрибута gender (приклад 7.6)

gender CHAR (1) CONSTRAIT NoAndro

CHECK (gender IN (‘M’, ‘F’)),

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

Приклад 8.8. Виключити (1), а потім додати (2) обмеження рівня атрибута gender (приклад 7.7) відношення MovieStar

1) ALTER TABLE MovieStar DROP CONSTRAIT NoAndro

2) ALTER TABLE MovieStar ADD CONSTRAIT NoAndro

CHECK (gender IN (‘M’, ‘F’)),

Поиск по сайту: