Основи DB2: Обмеження

1. Серія контенту:
2. Цей контент є частиною серії: Основи DB2
3. Малюнок 1. Фрагменти синтаксису оператора CREATE TABLE, що містять вирази для завдання обмежень
4. Малюнок 2. Фрагменти синтаксису оператора CREATE TABLE, що містять вирази для завдання обмежень (продовження)
5. Таблиця 1. Інформація про обмеження в каталозі бази даних
6. "Не можна бути нічим!" - обмеження NOT NULL
7. Малюнок 3. Представлення таблиць в Control Center
8. Малюнок 4. Вікно Alter Table в менеджері Control Center
9. Лістинг 1. Запит до каталогу БД для визначення null-стовпців таблиці
10. "Тільки для єдиних" - обмеження unique
11. Лістинг 2. Створення обмеження unique
12. імена обмежень
13. "Ми - номер один!" - обмеження primary key
14. Лістинг 3. Створення обмеження primary key
15. Малюнок 5. Вікно Alter Table
16. Малюнок 6. Вікно Define Primary Key
17. "Все відносно" - обмеження foreign key
18. Лістинг 4. Створення обмеження foreign key
19. Таблиця 2. Опції обмеження foreign key
20. Лістинг 5. Приклади правил оновлення та видалення в обмеженні foreign key
21. "Перевіряємо знову і знову" - обмеження table check
22. Лістинг 6. Створення обмеження table check
23. Малюнок 7. Створення обмеження check constraint у вікні Alter Table
24. Малюнок 8. Вікно Change Check Constraint дозволяє змінити існуюче обмеження
25. Малюнок 9. Якщо таблиця містить невідповідні значення, то при створенні обмеження ви отримаєте повідомлення...
26. Лістінг 7. Використання оператора SET INTEGRITY для відкладеної Перевірки обмежень
27. Висновок
28. Ресурси для скачування

основи DB2

Серія контенту:

Цей контент є частиною # з серії # статей: Основи DB2

https://www.ibm.com/developerworks/ru/library/?series_title_by=**auto**

Слідкуйте за виходом нових статей цієї серії.

Цей контент є частиною серії: Основи DB2

Слідкуйте за виходом нових статей цієї серії.

У цьому розділі описуються відмінності в структурі SQL-процедур DB2 і solidDB.

Обмеження в DB2 для платформ Linux, UNIX і Windows (DB2 LUW) використовуються для забезпечення виконання бізнес-правил для даних. У цій статті розглядаються такі типи обмежень:

• NOT NULL
• Unique
• Primary key
• Foreign key
• Table check

Існує ще один тип обмежень, який називається інформаційним обмеженням (informational constraint). На відміну від п'яти перерахованих вище типів, інформаційне обмеження не задається примусово через менеджер бази даних, але може бути використано компілятором SQL для підвищення продуктивності запитів. У цій статті будуть розглянуті тільки типи обмежень, перераховані в списку.

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

Малюнок 1. Фрагменти синтаксису оператора CREATE TABLE, що містять вирази для завдання обмежень

Простіше і зручніше за все управляти обмеженнями через центр управління DB2 Control Center.

Визначення обмежень пов'язані з базою даних, для якої вони створюються, і зберігаються в каталозі бази даних відповідно до таблицею 1 . Для отримання цієї інформації ви можете виконати запит до каталогу бази даних. Це можна зробити за допомогою інтерпретатора (не забудьте спочатку встановити з'єднання з базою даних) або ж більш зручним способом - через DB2 Control Center.

Створені вами обмеження обробляються так само, як і інші об'єкти бази даних. Вони мають імена, асоційовану схему (creator ID), і в деяких випадках можуть бути видалені.

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

У таблиці 1 міститься інформація про обмеження в каталозі бази даних. Для успішного виконання запитів до каталогу потрібно встановити з'єднання з базою даних.

Таблиця 1. Інформація про обмеження в каталозі бази даних

Представлення каталогу Стовпець уявлення Опис Приклад запиту SYSCAT.CHECKS Містить по одному запису для кожного обмеження table check db2 select constname, tabname, text from syscat.checks SYSCAT.COLCHECKS Містить по одному запису для кожного стовпця, на яке посилається обмеження table check db2 select constname , tabname, colname, usage from syscat.colchecks sYSCAT.COLUMNS nULLS Показує, чи може стовпець містити null-значення (Y) чи ні (N) db2 select tabname, colname, nulls from syscat.columns where tabschema = 'DELSVT' and nulls = 'N' SYSCAT.CONSTDEP Містить по одному запису для кожної залежності обмеження від іншого об'єкта db2 select constname, tabname, btype, bname from syscat. constdep SYSCAT.INDEXES Містить по одному запису для кожного індексу db2 select tabname, uniquerule, made_unique, system_required from syscat.indexes where tabschema = 'DELSVT' SYSCAT.KEYCOLUSE Містить по одному запису для кожного стовпця в ключі, визначеному обмеженнями unique, primary key або foreign key db2 select constname, tabname, colname, colseq from syscat.keycoluse sYSCAT.REFERENCES Містить по одному запису для кожного обмеження на посилання db2 select constname, tabname, refkeyname, reftabname, colcount, deleterule, updaterule from syscat.references SYSCAT.TABCONST Містить по одному запису для кожного обмеження unique (U), primary key (P), foreign key (F) або table check (K) db2 select constname, tabname, type from syscat.tabconst SYSCAT.TABLES PARENTS Количеств про батьківські таблиць даної таблиці (кількість обмежень на посилання, в яких ця таблиця є залежною) db2 "select tabname, parents from syscat.tables where parents> 0" SYSCAT.TABLES CHILDREN Кількість залежних таблиць для даної таблиці (кількість обмежень на посилання, в яких ця таблиця є батьківської) db2 "select tabname, children from syscat.tables where children> 0" sYSCAT.TABLES SELFREFS кількість самоссилающіхся обмежень на посилання для даної таблиці (кількість обмежень на посилання, в яких ця таблиця є як батьківської, так і залежною ) db2 "select tabname, selfrefs from syscat.tables where selfre fs> 0 "SYSCAT.TABLES KEYUNIQUE Кількість обмежень unique (НЕ primary key), визначених для даної таблиці db2" select tabname, keyunique from syscat.tables where keyunique> 0 "SYSCAT.TABLES CHECKCOUNT Кількість обмежень table check, визначених для даної таблиці db2 "select tabname, checkcount from syscat.tables where checkcount> 0"

"Не можна бути нічим!" - обмеження NOT NULL

Обмеження NOT NULL запобігає додавання в стовпчик null-значень. Завдяки цьому для кожного рядка таблиці в стовпці завжди присутній якесь дійсне значення. Наприклад, у визначенні таблиці EMPLOYEE бази даних SAMPLE використовується обмеження LASTNAME VARCHAR (15) NOT NULL, завдяки чому кожен рядок таблиці містить прізвище співробітника.

Щоб визначити, чи може стовпець таблиці містити null-значення, ви можете звернутися до мови визначення даних (Data Definition Language, DDL), який можна згенерувати за допомогою утиліти db2look. Також ви можете використовувати DB2 Control Center, як показано на малюнках 3 і 4.

Малюнок 3. Представлення таблиць в Control Center

DB2 Control Center надає вам зручний доступ до об'єктів бази даних - наприклад, до таблиць. На малюнку 3 показані призначені для користувача таблиці в базі даних SAMPLE. Перелік таблиць можна побачити на панелі вмісту, вибравши вузол Tables в дереві об'єктів. Якщо ви виберете таблицю STAFF, ви зможете відкрити вікно Alter Table і подивитися визначення таблиці, включаючи атрибути стовпців, як показано на малюнку 4.

Малюнок 4. Вікно Alter Table в менеджері Control Center

Крім того, ви можете виконати запит до каталогу бази даних, як показано в лістингу 1.

Лістинг 1. Запит до каталогу БД для визначення null-стовпців таблиці

db2 select tabname, colname, nulls from syscat.columns where tabschema = 'DELSVT' and nulls = 'N'

"Тільки для єдиних" - обмеження unique

Обмеження unique запобігає появі декількох однакових значень в певному стовпці таблиці. Також воно запобігає появі декількох однакових наборів значень в певному наборі стовпців. Стовпці, на які посилається обмеження unique, повинні бути визначені як NOT NULL. Обмеження unique може бути визначено в операторі CREATE TABLE за допомогою виразу UNIQUE ( малюнок 1 і малюнок 2 ) Або в операторі ALTER TABLE, як показано в лістингу 2.

У лістингу 2 показано, як створити обмеження unique. Таблиця ORG_TEMP ідентична таблиці ORG в базі даних SAMPLE за винятком того, що стовпець LOCATION в таблиці ORG_TEMP не може містити null-значень, і тому для нього можна задати обмеження unique.

Лістинг 2. Створення обмеження unique

db2 create table org_temp (deptnumb smallint not null, deptname varchar (14), manager smallint, division varchar (10), location varchar (13) not null) db2 alter table org_temp add unique (location) db2 insert into org_temp values ​​(10, 'Head Office', 160, 'Corporate', 'New York') DB20000I The SQL command completed successfully. db2 insert into org_temp values ​​(15, 'New England', 50, 'Eastern', 'New York') DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0803N One or more values ​​in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "DELSVT .ORG_TEMP "from having duplicate values ​​for the index key. SQLSTATE = 23505

імена обмежень

Якщо при створенні обмеження ви не вказуєте його ім'я, DB2 призначає йому ім'я, засноване на часі створення, наприклад, SQL100419222516560.

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

"Ми - номер один!" - обмеження primary key

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

Стовпці, на які посилається обмеження primary key, повинні бути визначені як NOT NULL. Обмеження primary key може бути визначено в операторі CREATE TABLE за допомогою виразу PRIMARY KEY ( малюнок 1 і малюнок 2 ) Або в операторі ALTER TABLE, як показано в лістингу 3.

У лістингу 3 показано, як створити обмеження primary key. Стовпець ID в таблиці STAFF не може містити null-значень, і тому для нього можна задати обмеження primary key.

Лістинг 3. Створення обмеження primary key

db2 alter table staff add primary key (id)

Ви також можете створити обмеження primary key для таблиці за допомогою DB2 Control Center, як показано на малюнках 5 і 6. У вікні Alter Table виберіть вкладку Keys і натисніть кнопку Add Primary.

Малюнок 5. Вікно Alter Table

В результаті відкриється вікно Define Primary Key, зображене на малюнку 6.

Малюнок 6. Вікно Define Primary Key

Вікно Define Primary Key дозволяє вам вибрати один або декілька стовпців зі списку доступних стовпців Available columns. Натисніть кнопку>, щоб перемістити потрібні стовпці зі списку Available column в список Selected columns. Зверніть увагу на те, що обрані стовпці не повинні бути null-стовпцями.

"Все відносно" - обмеження foreign key

Обмеження foreign key іноді називають обмеженням на посилання (referential constraint). Посилальна цілісність визначається як стан бази даних, в якому все значення всіх зовнішніх ключів дійсні. Що ж таке зовнішній ключ? Зовнішній ключ - це стовпець або набір стовпців в таблиці, значення яких повинні збігатися як мінімум з одним первинним або унікальним ключем рядки в її батьківській таблиці. Що це означає? Насправді все не так погано, як це звучить. Це просто означає, що якщо стовпець C2 в таблиці T2 містить значення, що збігаються зі значеннями стовпчика C1 іншої таблиці (T1), і C1 є стовпцем первинного індексу таблиці T1, то стовпець C2 в таблиці T2 є зовнішнім ключем. Таблиця, що містить батьківський ключ (первинний або унікальний ключ) називається батьківської таблицею, а таблиця, яка містить зовнішній ключ - залежною таблицею. Давайте розглянемо наступний приклад.

У таблиці PROJECT бази даних SAMPLE міститься стовпець під назвою RESPEMP. Значення цього стовпця відображають номера співробітників, відповідальних за кожен з проектів, перерахованих в таблиці. Стовпець RESPEMP не є null-стовпцем. Оскільки цей стовпець відповідає стовпцю EMPNO таблиці EMPLOYEE, і стовпець EMPNO тепер є первинним ключем для таблиці EMPLOYEE, стовпець RESPEMP може бути визначений як зовнішній ключ в таблиці PROJECT, як показано в лістингу 4. Це гарантує нам, що при видаленні даних з таблиці EMPLOYEE в таблиці PROJECT не залишиться неіснуючих відповідальних співробітників.

Обмеження foreign key може бути визначено в операторі CREATE TABLE за допомогою виразу FOREIGN KEY ( малюнок 1 і малюнок 2 ) Або в операторі ALTER TABLE, як показано в лістингу 4.

Лістинг 4. Створення обмеження foreign key

db2 alter table project add foreign key (respemp) references employee on delete cascade

У цьому обмеження вираження REFERENCES вказує на батьківську таблицю. Синтаксис для визначення обмеження foreign key містить правило, в якому ви можете вказати DB2, яким чином повинні виконуватися операції оновлення або видалення, виходячи з умов посилальної цілісності ( малюнок 1 ).

Операції вставки (insert) виконуються стандартним чином, і ви не можете управляти цим. Правило вставки (insert rule), вказане в обмеженні foreign key, означає, що вставляється значення зовнішнього ключа повинно збігатися з будь-яким значенням батьківського ключа батьківської таблиці. Це відповідає тому, про що вже було сказано. Якщо в таблицю PROJECT вставляється новий запис, то цей запис повинен містити посилання (через відношення батько-зовнішній ключ) на існуючий запис в таблиці EMPLOYEE.

Правило поновлення (update rule), вказане в обмеженні foreign key, означає, що нове значення зовнішнього ключа при оновленні має збігатися з будь-яким значенням батьківського ключа батьківської таблиці, і що все значення зовнішнього ключа повинні мати відповідні значення батьківського ключа на момент завершення операції поновлення батьківського ключа. Знову ж таки, це означає, що не може існувати залежних записів, які не мають батьківських записів.

Правило видалення (delete rule), вказане в обмеженні foreign key, застосовується в момент видалення рядка з батьківської таблиці і залежить від того, яка опція була вказана при визначенні обмеження на посилання (таблиця 2).

Таблиця 2. Опції обмеження foreign key

Якщо при створенні обмеження foreign key була вказана опція ... ... ми отримаємо наступний результат RESTRICT або NO ACTION Рядки не будуть видалені. SET NULL Кожен null-стовпець зовнішнього ключа прийме значення null. CASCADE Операція видалення поширюється на залежні таблиці батьківської таблиці. Ці залежності називаються пов'язаними з видалення (delete-connected) з батьківської таблицею.

У лістингу 5 розглянуті деякі приклади.

Лістинг 5. Приклади правил оновлення та видалення в обмеженні foreign key

db2 update employee set empno = '350' where empno = '000200' DB20000I The SQL command completed successfully. db2 update employee set empno = '360' where empno = '000220' DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0531N The parent key in a parent row of relationship "DELSVT.PROJECT.FK_PROJECT_2" can not be updated. SQLSTATE = 23504 db2 "select respemp from project where respemp < '000050' order by respemp" RESPEMP ------- 000010 000010 000020 000030 000030 5 record (s) selected. db2 delete from employee where empno = '000010' DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0532N A parent row can not be deleted because the relationship "DELSVT.PROJECT.FK_PROJECT_2" restricts the deletion. SQLSTATE = 23001 db2 "select empno from employee where empno < '000050' order by empno" EMPNO ------ 000010 000020 000030 3 record (s) selected.

Значення 000200 стовпчика EMPNO батьківської таблиці (EMPLOYEE) можна змінити, оскільки не існує значення 000200 стовпчика RESPEMP в залежною таблиці (PROJECT). Однак для значення 000220 стовпчика EMPNO існують збігаються значення зовнішнього ключа в таблиці PROJECT, і тому його не можна оновити. Правило видалення, в якому була вказана опція RESTRICT, призводить до того, що рядки, що містять значення 000010 первинного ключа, не можуть бути видалені з таблиці EMPLOYEE, якщо пов'язана з видалення таблиця PROJECT містить збігаються з ними значення зовнішнього ключа.

"Перевіряємо знову і знову" - обмеження table check

Обмеження table check накладає певні обмеження на дані при їх додаванні в таблицю. Наприклад, обмеження table check може забезпечити нам те, що кожен раз при додаванні або оновленні даних в таблиці EMPLOYEE довжина телефонного номера співробітника становитиме рівно чотири цифри. Обмеження table check може бути визначено в операторі CREATE TABLE за допомогою виразу CHECK ( малюнок 1 і малюнок 2 ) Або в операторі ALTER TABLE, як показано в лістингу 6.

Лістинг 6. Створення обмеження table check

db2 alter table employee add constraint phoneno_length check (length (rtrim (phoneno)) = 4)

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

Для створення обмеження table check ви також можете використовувати DB2 Control Center, як показано на малюнку 7.

Малюнок 7. Створення обмеження check constraint у вікні Alter Table

Для завдання нового обмеження натисніть кнопку Add, в результаті чого відкриється вікно Add Check Constraint. Щоб змінити існуюче обмеження, виберіть його зі списку і натисніть кнопку Change, як показано на малюнку 8.

Малюнок 8. Вікно Change Check Constraint дозволяє змінити існуюче обмеження

Ви не зможете створити обмеження table check, якщо існуючі рядки таблиці містять значення, що порушують правила цього обмеження. Це показано на малюнку 9. Ви зможете додати або змінити обмеження після того, як всі невідповідні записи будуть оновлені відповідно до необхідних правилами.

Малюнок 9. Якщо таблиця містить невідповідні значення, то при створенні обмеження ви отримаєте повідомлення про помилку

Відкладене перевірка обмежень

Для перекладу таблиці в стан відкладеної перевірки (check-pending state) можна використовувати оператор SET INTEGRITY. Це дозволяє створювати нові обмеження table check за допомогою оператора ALTER TABLE, не виконуючи при цьому перевірку існуючих даних таблиці.

За допомогою оператора SET INTEGRITY можна ввімкнути або вимкнути обмеження table check. Це може виявитися корисним, наприклад, при оптимізації продуктивності під час операцій завантаження великих обсягів даних в таблицю. У лістингу 7 наведено простий сценарій використання оператора SET INTEGRITY. У цьому прикладі телефонний номер співробітника з номером 000100 оновлюється до значення 123, після чого перевірка цілісності таблиці EMPLOYEE відключається. Потім для таблиці EMPLOYEE створюється обмеження, що вимагає, щоб телефонний номер складався з чотирьох цифр, і створюється таблиця EMPL_EXCEPT, ідентична таблиці EMPLOYEE. Далі включається перевірка цілісності, і рядки, що порушує обмеження table check, переміщаються в таблицю винятків (EMPL_EXCEPT). Запити, виконані для цих двох таблиць, показують нам, що не відповідають обмеження записи існують тільки в таблиці винятків.

Лістінг 7. Використання оператора SET INTEGRITY для відкладеної Перевірки обмежень

db2 update employee set phoneno = '123' where empno = '000100' db2 set integrity for employee off db2 alter table employee add constraint phoneno_length check (length (rtrim (phoneno)) = 4) db2 create table empl_except like employee db2 set integrity for employee immediate checked for exception in employee use empl_except SQL3602W Check data processing found constraint violations and moved them to exception tables. SQLSTATE = 01603 db2 select empno, lastname, workdept, phoneno from empl_except EMPNO LASTNAME WORKDEPT PHONENO ------ --------------- -------- --- ---- 000100 SPENSER E21 123 1 record (s) selected.

Висновок

У цій статті були розглянуті різні типи обмежень, які підтримуються СУБД DB2 для платформ Linux, UNIX і Windows - обмеження NOT NULL, unique, primary key, foreign key (обмеження на посилання) і table check. Обмеження в DB2 використовуються для виконання бізнес-правил і для забезпечення цілісності бази даних. Також ви дізналися, як використовувати для роботи з обмеженнями (і для виконання запитів до каталогу бази даних) командний рядок і DB2 Control Center.

Ресурси для скачування

Схожі тими

Підпішіть мене на ПОВІДОМЛЕННЯ до коментарів