Створення перевірного обмеження (CHECK)

Обмеження CHECK дає можливість задати діапазон допустимих значень стовпця чи визначити його на основі логічного виразу. Обме­ження CHECK завжди повертає логічне значення True / False. Перевір­ний вираз формується, як на основі даних визначеного стовпця, так і даних інших стовпців цієї таблиці; посилатися на стовпці в інших таб­лицях обмеження не може (!!!). SQL Server додає в базу лише ті стріч­ки з даними, для яких обмеження CHECK повертає значення True.

Обмеження CHECK формують на основі тих ж пра­вил, які ви­значені для логічних операцій конструкції WHERE мови SQL. Деякі приклади цих критеріїв наведені у таблиці 2.4.

Таблиця 2.4. Приклади застосування обмеження CHECK

Логічний вираз	Призначення
Number >= 0	Регламентація використання лише до­датних значень
Number BETWEEN 1 AND 12	Забезпечення значень лише у визначе­ному діапазонові
Number <= ID	Регламентація значень відносно зна­чень іншого стовпця
Name IN ('Петро', 'Вова', 'Василь')	Регламентація допусти­мих значень
Birthday < getdate()	Регламентація дат днів народжень ли­ше до поточної дати, що повертається системною функцією getdate()
Email LIKE ' %@%.[a-z][a-z][a-z]'	Забезпечення правильного вводу ад­ресу електронної пошти

У загальному, синтаксис логічних виразів є схожим до аналогіч­них виразів алгоритмічних мов та складається із логічних, арифметич­них, порозрядних, стрічкових операторів, операторів порівняння, сис­темних функцій, числових та символьних даних. Символьні дані по­винні бути взятими в одинарні лапки ('). Окрім цього також вико­ристо­вуються у виразах додаткові логічні оператори, наприклад, LIKE, BETWEEN тощо. У таблиці 2.5 та 3.1 дано опис для цих елементів.

Таблиця 2.5. Логічні оператори Microsoft SQL Server 2005

Оператор	Опис
AND	Об’єднує дві умови та повертає значення True, якщо обидві мають значення True. Приклад: Number > 12 AND Number < 25 значення стовпця Number повинні бути більші за 12 та менші за 25
OR	Об’єднує дві умови та повертає значення True, якщо хоча б одна з них має значення True. Приклад: Number < 12 OR Number > 25 значення стовпця Number повинні бути більші за 12 або менші за 25
NOT	Інвертує будь-який логічний вираз. Приклад: NOT(Number > 12 AND Number < 25) значення стовпця Number НЕ повинні бути більші за 12 та менші за 25
IS [NOT] NULL	Визначає чи значення у стовпці є null (пусте значення). Приклад: Number IS NOT NULL значення стовпця Number не повинні бути пустими, тобто null
[NOT] IN	Визначає, чи задане значення співпадає з одним зі значень у списку. Перелік значень наводиться у круглих дужках через кому. Приклад: Number IN (1,3,5) значення стовпця Number повинне бути одним зі значень: 1, 3 або 5 SurName NOT IN ('Дзелендзяк', 'Павельчак', 'Самотий') значення стовпця SurName НЕ повинні містити наступні прізвища: Дзелендзяк, Павельчак, Самотий NOT SurName IN ('Дзелендзяк', 'Павельчак', 'Самотий') умова аналогічна попередній
[NOT] BETWEEN	Задає діапазон допустимих значень. Для розділення почат­кових і кінцевих значень використовується оператор AND. Приклад: Number NOT BETWEEN 1 AND 12 значення стовпця Number НЕ повинно входити у діапазон від 1 до 12, це ана­логічно умові NOT (Number > 1 AND Number < 12)
[NOT] LIKE	Визначає, чи вказана символьна стрічка співпадає із зада­ним шаблоном. Шаблон може включати як звичайні сим­воли, так і символи-шаблони (табл. 2.6). Під час порівнян­ня із шаблоном необхідно, щоб його звичайні символи точно співпадали зі символами, що вказані у стрічці. Символи-шаблони можуть співпадати з довільними еле­ментами символьної стрічки.

Таблиця 2.6. Символи-шаблони логічного оператора LIKE

Символ-шаблон	Опис
%	Замінює довільну стрічку довжиною від нуля та більше символів. Приклад: SurName LIKE '%енко' поле SurName може містити довіль­не прізвище, яке закінчується на енко, наприклад, Шевченко, Петренко, Павленко Note LIKE '%студент%' поле Note може бути довільним, однак воно повинне містити слово студент
_ (підкреслення)	Замінює довільний одиничний символ. Приклад: Word LIKE '_ама' поле Word може містити довільне слово з 4-х букв із закінченням на ама, наприклад, мама, рама, гама
[ перелік ]	Замінює довільний одиничний символ, вказаний у діапазоні, наприклад [a-m] (любий символ від 'a' до 'm' включно), чи наборі, наприклад, [adfm] (будь-який з перелічених символів 'a', 'd', 'f' чи 'm'). Приклад: SurName LIKE '[А-П]%енко' поле SurName може містити довіль­не прізвище, яке закінчується на енко та починається на будь-яку букву у проміжку від А до П, наприклад, Арченко, Павленко, Марченко, але не Шевченко (!!!) Word LIKE '[гмр]ама' поле Word може містити слово із 4-х букв, що починається на одну із букв г, м чи р та закінчуєть­ся на ама, наприклад, мама, рама, гама, але не дама PostalCode LIKE '[0-9][0-9][0-9][0-9][0-9]' поле PostalCode повинно містити лише п’ятизначний поштовий індекс PostalCode LIKE '[a-zA-Z][a-zA-Z][a-zA-Z0-9][0-358]' поле PostalCode повинно містити лише чотиризначний код, фор­мат якого складається з 2-х довільних латинських букв, одної довільної латинської літери чи числа, та обов’язково закін­чується цифрою 0, 1, 2, 3, 5 чи 8
[^ перелік ]	Замінює довільний одиничний символ, що не входить у вка­заний діапазон чи набір. Приклад: Nick LIKE '[^0-9АБ]%' поле Nick не може містити псевдо, що починається цифрою або буквою А чи Б
ВАЖЛИВО! При роботі оператора LIKE з даними у форматі UNICODE (типи даних nchar і nvarchar) враховуються кінцеві (доповнюючі) пробіли. Тому потріб­но це мати на увазі при формування шаблону, наприклад, для поля Name типу nchar(20) умова Name LIKE '[0-9][0-9]' ніколи не буде задовольнятися, ос­кільки після 2-х цифр поле містить ще 18 пробілів, що враховуються. Для виправлення некоректної ситуації слід задати вужче поле шириною у 2 сим­воли (nchar(2)), або змінити на тип даних ANSI char(20), для якого допов­нюючі пробіли не враховуються. Символи-шаблони(%,_,[,],^)у якості символів. Є два способи іден­тифікації символу-шаблону, як звичайного символу. Перший полягає у тому, що символ-шаблон необхідно помістити у квадратні дужки, наприклад, Nick LIKE '%F[-]15%' задовольняє довільне входження стрічки 'F-15' у поле. Другий спосіб полягає у викорис­танні екрануючих символів та ключового слова ESCAPE (екрануючим може бути довільний символ), наприклад, Nick LIKE'%F!-15%' ESCAPE '!' результат аналогічний попередньому. Якщо у шаблоні LIKE після екрануючого символу немає жодного символу, то шаблон недопустимий, і операція поверне значення False. Якщо символ після екрануючого символу не є символом-шаблоном, тоді екрануючий символ іг­норується і розглядається, як звичайний.

а)

п.1

б)

п.2

п.6

п.7

п.5

п.4

п.3

в)

Рис. 2.10. Створення перевірного обмеження CHECK

Створення обмеження CHECK:

1. У режимі конструктора робочої таблиці на пане­лі інстру­ментів натиснути по іконці Manage Check Constraints (керу­вання перевірними обмеженнями) (рис. 2.10а).

2. У діалоговому вікні Check Constraints (перевірні обмежен­ня), у лівій частині вікна (рис. 2.10б), натиснути кнопку Add (добавити).

3. Для новоствореного обмеження у полі Expression (вираз) вписати логічний вираз; для громіздких виразів доцільно скористатися текстовим вікном, що викликається при натис­ненні кнопки з трикрапкою, розташованої справа від поля.

4. У полі (Name) [(ім’я)] скорегувати назву обмеження в більш зрозумілий контекст.

5. У полі Enforce For Replication (примусова реплікація) вказати, чи діє обмеження, коли агент реплікації виконує вставлення чи змінення даних у цій таблиці.

6. У полі Enforce For INSERTs And UPDATEs (примусове ви­користання для запитів INSERT та UPDATE) вказати, чи діє обмеження при вставці або зміні даних у таблиці.

7. У полі Check Existing Data On Creation Or Re-Enabling (перевірення існуючих даних при створенні чи повторному підключенні) вказати, чи перевіряються вже існуючі дані (дані, що вже були у таблиці на момент створення обмежен­ня) на відповідність обмеженню CHECK.

8. Закрити діалогове вікно Check Constraints (перевірні обме­жен­ня) та зберегти зміни в таблиці.

Синтаксична структура обмежень переважно розрахована на той випадок, коли вони створюються одночасно зі створенням таблиць. Але іноді виникають обставини, коли для вже існуючої таблиці з дани­ми необхідно створити обмеження CHECK, яке б не дозволяло, на­приклад, введення від’ємних значень, але вже існуючі стрічки з да­ними при цьому слід залишити. І тому, щоб ввести в дію нове обмеження, але виключити його застосування до вже існуючих даних, необхідно задати значення false у полі Check Existing Data On Creation Or Re-Enabling (п.7).

2.4.4. Створення реляційних зв’язків за допомогою зов­нішніх ключів (FOREIGN KEY).

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

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

Такий спосіб обмеження дає можливість будувати різноманітні відношення між даними у базі даних:

· відношення «один до багатьох»;

· відношення «багато до багатьох»;

· зворотні (рекурсивні) відношення.

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

предок

нащадок1

нащадок2

а)

Many

Many

б)

Рис. 2.11. Представлення відношення «один до багатьох»

На рис. 2.11 наведено реалізацію 2-х зв’язків «один до бага­тьох». У цьому прикладі таблиця-предок містить записи з перелі­ком груп 4-го курсу кафедри КСА. Таблиця-нащадок1 містить записи з прізвищами старостів груп, а таблиця-нащадок2 – записи з прізвищами студентів усього потоку. Для таблиць встановленні первинні ключі для таких полів: GroupName, Leader, Student. Для таблиці-предка зада­но унікальний ключ по полю ID, а для таблиць-нащадків визначе­ні, відповідно, зовнішні ключі по власних полях ID, відносно цього уні­кального ключа. Зазначимо, що зв’язок предок-нащадок1 відповідає відношенню «один до одного», бо в одній групі може бути лише один староста. Інший зв’язок, предок-нащадок2, повноцінно реалізує відно­шення «один до багатьох».

Відношення «один до одного» представляє собою фактично до­повнення чи продовження основної таблиці. Такий підхід дає можли­вість перенести конфіденційну інформації в окрему таблицю, доступ до якої SQL Server надає користувачам згідно наданих їм прав.

Розрізняють два класи відношень – обов’язкові та необо­в’язкові.

Обов’язкове відношення вимагає міграції поля таблиці-предка у екземпляр нащадка. При реалізації цього відношення мігруючий (зовнішній) ключ не повинен бути null (!!!). Тобто кожне поле табли­ці-нащадка повинне обов’язково належати певному полю табли­ці-предка, як на рис. 2.11а.

Необов’язкове відношення передбачає, що нащадок не завжди повинен мати значення для мігруючого (зовнішнього) ключа. У цьому випадку поле зовнішнього ключа може приймати значення null. На рис. 2.12 показано, що таблиця-нащадок містить прізвища, які не від­носяться до жодної з груп 4-го курсу.

предок

нащадок

Рис. 2.12. Необов’язкове відношення «один до багатьох»

Відношення «багато до багатьох». У відношеннях цього типу обидві сторони пов’язані з значною кількістю (більше одного) елемен­тів протилежної сторони. Наприклад, є ряд студентів, що мають де­кілька академзаборгованостей з різних дисциплін (рис. 2.13а). Тобто, кожен зазначений студент має відношення до декількох назв предме­тів. З іншого боку, кожна навчальна дисципліна має відношення до де­кількох студентів. І в такий спосіб, між полями двох таблиць утворю­ються зв’язки типу «багато до багатьох».

Необхідно зазначити, що фактично зреалізувати відношення «багато до багатьох» не є можливим, і тому для логічної розв’язки цього відношення використовують асоціативну таблицю, яку іноді ще називають стикувальною. У результаті чого здійснюється заміна від­ношення «багато до багатьох» двома відношеннями «один до бага­тьох» (рис. 2.13б).

а)

студент

заборгованість

Many

Many

б)

Рис. 2.13. Представлення відношення «багато до багатьох»

Зворотне (рекурсивне) відношення. Це відношення моделюєть­ся не відносно іншої таб­лиці з даними, а в межах тієї самої. Його ще іноді називають ієрархічним, тому що воно дає можливість моделю­вати деревовидні структури даних (рис. 2.14).

Начальник

Заступник_1

Заступник_2

Головний спеціаліст_1

Головний спеціаліст_2

Головний спеціаліст_3

Головний спеціаліст_4

Рис. 2.14. Ієрархічна структура підрозділу

а)

б)

Рис. 2.15. Представлення зворотного відношення

Зворотне відношення по своїй суті є звичайним відношенням «один до багатьох», але лише в межах тієї самої таблиці. При цьому, значення первинного (унікального) ключа мігрує у поле зовнішнього ключа. Для таблиці (рис. 2.15), що реалізує ієрархію певного підрозді­лу (рис. 2.14), є визна­чені такі ключі: первинний – для поля Post, уні­кальний – для поля ID, зовнішній – для поля MasterID; поле ID є авто­інкрементним; зворотне відношення тут реалізує зв'язок ID-MasterID.

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

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

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

а)

п.1

b

a

п.7

б)

п.6

п.5

п.4

п.3

п.2

продовження рис. 2.16 на наступній сторінці →

← початок рис. 2.16 на попередній сторінці

b

a

c

в)

Рис. 2.16. Створення зовнішнього ключа таблиці

Створення зовнішнього КЛЮЧА:

1. У панелі Object Explorer (оглядач об’єктів) для вибраної таблиці робочої бази даних розкрити вузли як на рис. 2.16а, та в контекстно­му меню вузла Keys (ключі) вибрати коман­ду New Foreign Key… (створити зовнішній ключ), після чого з’явиться діалогове вікно Foreign Key Relationships (відно­шення зовнішнього ключа). Далі слід задати значення для параметрів зовнішнього ключа.

2. Задати механізм зв’язків для зовнішнього ключа в контексті відношення «один до багатьох». Для цього у полі Tables And Columns Specification (специфікація таблиць і стовп­ців) натиснути кнопку з трикрапкою, розташовану справа від поля (рис. 2.16б). В результаті з’явиться діалогове вікно Tables and Columns (таблиці та стовпці) (рис. 2.16в). Далі слід вибрати стовпці для зовнішнього та первинного (уні­кального) ключів:

a. з випадаючого списку вибрати назву таблиці, де розмі­щений первинний (унікальний) ключ;

b. з випадаючого списку вибрати стов­пець (стовпці) з пер­винним (унікальним) ключем;

c. з випадаючого списку вибрати стов­пець (стовпці) для зовнішнього ключа;

Натиснути кнопку OK.

3. У полі Check Existing Data On Creation Or Re-Enabling (перевірення існуючих даних при створенні чи повторному включенні) вказати, чи буде виконуватися дане перевірення для вже існуючих даних.

4. У полі (Name) [(ім’я)] скорегувати назву зовнішнього ключа у більш зрозумілий контекст.

5. Значення поля Enforce For Replication (примусова репліка­ція) вказує, чи використовується дане обмеження, коли агент реплікації виконує у таблиці вставлення, зміну чи ви­далення.

6. Значення поля Enforce Foreign Key Constraint (примусове обмеження зовнішнього ключа) дає можливість відмінити дію зовнішнього ключа щодо забезпечення цілісності да­них; для його відміни слід встановити зна­чення поля у false.

7. У категорії INSERT And UPDATE Specification (специфі­кація INSERT і UPDATE) задаються правила для видалення та зміни зв’язку цього відношення *:

a. у полі Delete Rule (видалити правило) вибрати механізм дії SQL Server при спробі користувача видалити поле із записом предка, що мігрує у екземпляр нащадка;

b. у полі Update Rule (обновити правило) вибрати ме­ханізм дії SQL Server при спробі користувача обновити поле із записом предка, що мігрує у екземп­ляр нащадка.

8. Зберегти зміни в таблиці.

* Особливістю зовнішніх ключів, на відміну від інших, є те, що вони двонаправлені: забезпечують, як правильність вводу зна­чень у поля екземпляра нащадка у відповідності до предка, так і забезпечу­ють перевірку зміни значення при діях над записами у таблиці-пред­ка (що запобігає появі у таблиці-нащадка «висячих стрічок», які втра­тили зв’язок із предком). За замовчуванням SQL Server «захищає» від видалення (чи зміни значення ключа) ті стрічки таблиці-предка, з яки­ми вони мають зв’язки у таблиці-нащадка. У цьому випадку для вида­лення такого запису, спершу необхідно видалити усі записи, що відпо­відають йому у таблиці-нащадку. Тому у SQL Server передбачений відповідний механізм, що відповідає за автоматичне виконання подіб­них операцій видалення чи обновлення, та має назву процесу каскад­ного виконання дій. Його значення особливо суттєве при операціях видалення, що проходять через декілька рівнів залежностей: одна стрічка залежить від другої, друга від третьої і т.д. При операції обновлення значення ключа предка, автоматично виконується обнов­лення значень і для усіх зв’язаних з ним записів екземпляра нащадка.

Відповідно, поля Delete Rule (видалити правило)та Update Rule (обновити правило) можуть приймати такі значення:

· No Action (без дій) – формує повідомлення про помилку, щодо неможливості операції видалення чи обновлення для стрічок зі значенням ключа, що мігрує у екземпляр нащадка; для операції видалення чи обновлення система виконує відкіт;

· Cascade (каскадом) – видаляє (обновлює) всі стрічки, що беруть участь у зв’язках зовнішнього ключа;

· Set Null (задати Null) – задає значення null, якщо всі стовп­ці зовнішніх ключів в таблиці можуть бути встановленні в null;

· Set Default (задати за замовчуванням) – встановлює значення за замовчуванням, що визначене для цього стовпця, при умові, що всі стовпці зовнішнього ключа в таблиці мають значення за замов­чуванням.

Лабораторна робота №5