Обмеження баз даних у Firebird та InterBase
NOTICE: Цей документ є розділом з книги “The InterBase World”, яку написали Олексій Ковязін та Сергій Востріков.
Цей розділ присвячений обмеженням баз даних InterBase та Firebird. Обмеження бази даних - це правила, які визначають взаємозв’язки між таблицями та можуть перевіряти й змінювати дані в базі даних. Ці правила реалізуються як спеціальні об’єкти бази даних. Головна перевага використання обмежень полягає в можливості реалізувати перевірку даних та частину бізнес-логіки застосунку на рівні бази даних, тобто централізувати та спростити її, що робить розробку застосунків для баз даних легшою та надійнішою.
Початківці-розробники часто нехтують використанням обмежень бази даних, вважаючи, що вони заважають творчій роботі. Однак насправді така думка формується через недостатнє знання теорії та практики проєктування баз даних.
Водночас найдосвідченіші проєктувальники наважуються відмовитися від використання деяких типів обмежень, завдяки чому їхні застосунки виграють у швидкості. Досвід експертів-проєктувальників дозволяє їм дуже добре розуміти роботу сервера та точно передбачати його поведінку у складних випадках, тому початківцям у програмуванні для InterBase краще не вдаватися до подібних дій досвідчених колег.
У межах цієї книги ми не розглядаємо проєктування баз даних, тому для отримання додаткової інформації з цього питання зверніться до списку літератури в кінці книги. Тут ми лише розглянемо всі типи обмежень у базі даних InterBase та розглянемо приклади їх застосування.
Типи обмежень у базі даних
У базі даних InterBase існують такі типи обмежень:
- PRIMARY KEY;
- UNIQUE KEY;
- FOREIGN KEY
- можуть вмикати автоматичні тригери - ON UPDATE та ON DELETE;
- CHECK
У попередніх розділах ми згадували деякі з цих обмежень, оскільки це було необхідно для логічного викладу матеріалу, але тепер ми розглянемо їхній синтаксис, застосування та реалізацію детальніше. Обмеження бази даних бувають двох типів - засновані на одному полі та засновані на кількох полях таблиці. Синтаксис обох типів обмежень наведено нижче.
= [CONSTRAINT constraint]
[ …]
= {UNIQUE | PRIMARY KEY
| CHECK ( )
| REFERENCES other_table [( other_col [, other_col …])]
[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
}
Синтаксис обмежень, заснованих на кількох полях, такий:
= [CONSTRAINT constraint]
[< tconstraint> …]
= {{PRIMARY KEY | UNIQUE} ( col [, col …])
| FOREIGN KEY ( col [, col …]) REFERENCES other_table[( other_col [, other_col …])]
[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
| CHECK ( )}
Різниця в синтаксисі між обмеженнями, заснованими на одному полі та на кількох полях, очевидна - в останньому випадку ми можемо вказати кілька полів, що входять в обмеження. У випадку обмеження, заснованого на одному полі, усі описані опції стосуються лише поточного поля. Звісно, ці два типи обмежень мають різний спосіб застосування: обмеження, засновані на одному полі, просто додаються до визначення потрібного поля, а обмеження, засновані на кількох полях, вказуються після коми в загальному визначенні таблиці. Детальні приклади наведено в наступних частинах цього розділу.
Приклад типового обмеження
Власне, обмеження, засновані на одному полі, є окремим випадком обмежень, заснованих на кількох полях.
Приклад створення обмеження первинного ключа з використанням цих двох різних підходів наведено нижче. Створімо таблицю, що містить лише одне поле, і встановімо для неї обмеження первинного ключа.
Ось приклад первинного ключа з використанням синтаксису обмеження, заснованого на одному полі:
CREATE TABLE test1( ID_PK INTEGER CONSTRAINT pktest NOT NULL PRIMARY KEY); У цьому прикладі для поля ID_PK створюється первинний ключ з ім’ям pktest. У результаті ми маємо досить компактний опис в одному рядку. Для тієї ж мети ми можемо використати синтаксис обмежень, заснованих на кількох полях: CREATE TABLE test2( ID_PK INTEGER NOT NULL, CONSTRAINT pktst PRIMARY KEY (ID_PK));
Створення обмежень
Розгляньмо створення обмежень детальніше. Першим в описі загального синтаксису обмежень іде параметр [CONSTRAINT constraint]. Як бачите, цей параметр розміщено в квадратних дужках, тобто він є необов’язковим.
Використовуючи цей параметр, ви можете задати ім’я створюваному обмеженню як у випадку застосування синтаксису обмежень, заснованих на одному полі, так і у випадку обмежень, заснованих на кількох полях. Якщо ви не вказали ім’я для обмеження, InterBase автоматично згенерує його. Тим не менш, краще задавати ім’я створюваному обмеженню, щоб покращити читабельність схеми бази даних і спростити подальше керування обмеженнями.
Задавши ім’я обмеженню, слід визначити його тип. Розгляньмо різні типи обмежень у порядку, як вони вказані в описі загального синтаксису обмежень.
Первинні та унікальні ключі
Первинні ключі є одним із головних типів обмежень бази даних. Вони застосовуються для однозначної ідентифікації записів у таблиці. Припустімо, що ми зберігаємо список людей у базі даних. Цілком можливо, що будуть дві (або більше) людини з однаковими прізвищем, ім’ям та по батькові. Як ми можемо відрізнити одну людину від іншої (звісно, питання полягає у відрізненні однієї людини від іншої згідно з інформацією, що зберігається в базі даних)?
У цьому випадку “людина” представлена одним записом у таблиці, тому ми можемо поставити загальніше питання - як ми можемо відрізнити один запис у (будь-якій) таблиці від іншого запису в тій самій таблиці. Для цієї мети використовуються обмеження - первинні ключі. Первинний ключ являє собою одне або кілька полів у таблиці, комбінація яких є унікальною для кожного запису. У таблиці не існує повторюваних значень первинного ключа.
Унікальні ключі виконують ту саму функцію - вони також служать для однозначної ідентифікації записів у таблиці. Різниця між первинними та унікальними ключами полягає в тому, що в таблиці може бути лише один первинний ключ, а унікальних ключів може бути кілька. Зазначено, що і первинний, і унікальний ключ можуть використовуватися як база для посилань зовнішніх ключів (див. далі).
Формальний опис понять первинного та унікального ключів, а також інші важливі визначення можна знайти в додатку “Глосарій” у кінці книги. Синтаксис створення первинного та унікального ключа на основі одного поля такий:
< pkukconstraint > = [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE}
Приклади первинного та унікального ключів:
CREATE TABLE pkuk( pk NUMERIC(15,0) NOT NULL PRIMARY KEY, /*первинний ключ*/
uk1 VARCHAR(50) NOT NULL UNIQUE,/*унікальний ключ */
uk2 INTEGER NOT NULL UNIQUE /\* ще один унікальний ключ */);
Синтаксис створення первинного та унікального ключів на основі кількох полів:
= [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE} ( col [, col …])
Такий синтаксис дозволяє створювати ключі на основі комбінації полів. Ось приклади створення первинного та унікального ключів з кількох полів:
CREATE TABLE pkuk2( Number1 INTEGER NOT NULL, Name1 VARCHAR(50) NOT NULL, Kol INTEGER NOT NULL, Stoim NUMERIC(15,4) NOT NULL, CONSTRAINT pkt PRIMARY KEY (Number1, Name1), /*первинний ключ pkt на основі двох полів*/ CONSTRAINT ukt1 UNIQUE (kol, Stoim)); /*унікальний ключ ukt1 на основі двох полів*/
Зверніть увагу, що всі поля, включені в первинні та унікальні ключі, мають бути оголошені як NOT NULL, оскільки ці ключі не можуть мати невизначеного значення. Окрім створення обмеження первинного та унікального ключів під час створення таблиці, існує можливість додавати обмеження до вже існуючої таблиці. У цьому випадку використовується оператор DDL: ALTER TABLE. Синтаксис додавання обмежень первинного або унікального ключа до існуючої таблиці подібний до описаного вище:
ALTER TABLE tablename ADD [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE} ( col [, col …])
Розглянемо приклад створення первинного та унікального ключа за допомогою ALTER TABLE:
CREATE TABLE pkalter( ID1 INTEGER NOT NULL, ID2 INTEGER NOT NULL, UID VARCHAR(24));
Потім додаємо ключі. Спочатку первинний:
ALTER TABLE pkalter ADD CONSTRAINT pkal1 PRIMARY KEY (id1, id2);
Потім унікальний: ALTER TABLE pkalter ADD CONSTRAINT ukal UNIQUE (uid);
Слід зазначити, що лише власник цієї таблиці або системний адміністратор SYSDBA (детальніше про власників та користувача SYSDBA див. розділ “Безпека в InterBase: користувачі, їх функції та права” - частина 4) може виконувати додавання (а також видалення) первинних та унікальних ключів до таблиці.
Зовнішні ключі
Наступне обмеження, яке часто використовується в базах даних InterBase, - це обмеження зовнішнього ключа. Це дуже потужний інструмент для забезпечення посилальної цілісності в базі даних, який дозволяє не лише контролювати наявність правильних посилань у базі даних, але й автоматично керувати цими посиланнями!
Суть створення зовнішнього ключа полягає в наступному: якщо дві таблиці служать для зберігання взаємопов’язаної інформації, необхідно гарантувати, що цей взаємозв’язок завжди буде коректним. Наприклад, документ “накладна”, що містить загальну шапку (дата, номер накладної тощо) та набір детальних записів (опис товарів, кількість тощо).
Для зберігання такого документа в базі даних створюються дві таблиці - одна для зберігання шапок накладних, а друга - для зберігання вмісту накладної - записів про товари та їх кількість. Такі таблиці називаються головною та підлеглою, або таблицею-майстром та таблицею-деталлю.
Згідно зі здоровим глуздом, вміст накладної не може існувати без наявності її шапки. Іншими словами, ми не можемо вставити запис про товар, якщо не створили шапку накладної, і не можемо видалити запис шапки, якщо існують записи про товари. Для реалізації такої поведінки таблиця шапки та таблиця деталей з’єднуються за допомогою обмеження зовнішнього ключа.
Розглянемо сенс встановлення обмежень зовнішнього ключа на прикладі таблиць, що містять інформацію про накладні. Для цього створимо дві таблиці для зберігання накладної - таблицю TITLE для зберігання шапки та таблицю INVENTORY для зберігання інформації про товари, включені до накладної.
CREATE TABLE TITLE( ID_TITLE INTEGER NOT NULL Primary Key, DateNakl DATE, NumNakl INTEGER, NoteNakl VARCHAR(255));
Зверніть увагу, що ми одразу визначили первинний ключ у таблиці шапки на основі поля ID_TITLE. Решта полів таблиці TITLE містять тривіальну інформацію про шапку накладної - дату, номер, коментар.
Тепер визначимо таблицю для зберігання інформації про товари, включені до накладної:
CREATE TABLE INVENTORY( ID_INVENTORY INTEGER NOT NULL PRIMARY KEY, FK_TITLE INTEGER NOT NULL, ProductName VARCHAR (255), Kolvo DOUBLE PRECISION, Positio INTEGER);
Подивимося, які поля включені до таблиці INVENTORY. По-перше, це ID_INVENTORY - первинний ключ цієї таблиці. Далі йде цілочисельне поле FK_TITLE, яке служить посиланням на ідентифікатор ID_TITLE шапки в таблиці шапок накладних. Потім ідуть поля ProductName, Kolvo та Positio, що описують опис товару, його кількість та позицію в накладній. Поле FK_TITLE є найважливішим для нашого прикладу. Якщо ми хочемо вивести інформацію про товари певної накладної, ми повинні використати наступний запит, у якому параметр mas_ID_TITLE визначає ідентифікатор шапки:
SELECT * FROM INVENTORY I1 WHERE I1.FK_TITLE=?mas_ID_TITLE
Фактично, в описаній ситуації ніщо не заважає заповнити таблицю INVENTORY записами, що посилаються на неіснуючі записи в таблиці TITLE. Крім того, ніщо не заважає видалити шапку вже існуючої накладної, через що записи про товари можуть стати “безхазяйними”. Сервер не заборонятиме виконувати всі ці вставки та видалення. Таким чином, контроль за цілісністю даних у базі даних повністю покладається на клієнтський застосунок. Однак ви знаєте, що з однією базою даних можуть працювати кілька застосунків, розроблених, можливо, різними програмістами, що може призвести до різної інтерпретації даних та помилок. Отже, важливо встановити явне обмеження, що лише такі записи про товари, які мають коректне посилання на шапку накладної, можуть бути внесені до таблиці INVENTORY. Це, власне, і є обмеженням зовнішнього ключа, яке дозволяє вставляти лише ті значення, які існують в іншій таблиці, у поля, включені в обмеження.
Таке обмеження можна створити за допомогою зовнішнього ключа. Для даного прикладу ми повинні встановити обмеження зовнішнього ключа для поля FK_TITLE і зв’язати його з первинним ключем ID_TITLE у таблиці TITLE. Ми можемо додати зовнішній ключ до вже існуючої таблиці за допомогою наступної команди:
ALTER TABLE INVENTORY ADD CONSTRAINT fktitle1 FOREIGN KEY(FK_TITLE) REFERENCES TITLE(ID_TITLE)
Часто при додаванні зовнішнього ключа виникає помилка - object is in use. Справа в тому, що для створення зовнішнього ключа необхідно відкрити базу даних у монопольному режимі - щоб одночасно не було інших користувачів. Також не слід звертатися до таблиці, що змінюється - це може спричинити помилку object is in use.
Тут INVENTORY - це назва таблиці, для якої встановлюється обмеження зовнішнього ключа; fktitle1 - назва зовнішнього ключа; FK_TITLE - поля, що утворюють зовнішній ключ; TITLE - назва таблиці, що надає значення (основу посилання) для зовнішнього ключа; ID_TITLE - поля первинного або унікального ключа в таблиці TITLE, які служать основою посилання для зовнішнього ключа. Повний синтаксис обмеження зовнішнього ключа (з можливістю створення обмежень на основі кількох полів) наведено нижче:
= [CONSTRAINT constraint] FOREIGN KEY ( col [, col …]) REFERENCES other_table [( other_col [, other_col …])] [ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}] [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
Як бачите, визначення містять великий набір опцій. Спочатку розглянемо базове визначення зовнішнього ключа, яке найчастіше використовується в реальних базах даних, а потім проаналізуємо можливі опції.
Декларативна форма обмеження зовнішнього ключа найчастіше використовується, коли вказується набір полів (col [, col …]), які утворюватимуть обмеження; а також other_table, що містить список можливих значень для зовнішнього ключа в полях [(other_col [, other_col …])].
Ось приклад такого визначення при створенні таблиці:
CREATE TABLE Inventory2( … FK_TABLE INTEGER NOT NULL CONSTRAINT fkinv REFERENCES TITLE(ID_TITLE) …);
Зверніть увагу, що в цьому визначенні ключові слова FOREIGN KEY опущені, а також мається на увазі, що єдине поле FK_TITLE використовується як зовнішній ключ. Повніша форма створення зовнішнього ключа одночасно з таблицею наведена в наступному прикладі:
CREATE TABLE Inventory2( … FK_TABLE INTEGER NOT NULL, CONSTRAINT fkinv FOREIGN KEY (FK_TABLE) REFERENCES TITLE(ID_TITLE) …);
Використання NULL у полях зовнішнього ключа
У полях, на основі яких створюється зовнішній ключ, допускається застосування NULL-полів. Ця можливість додана для забезпечення взаємних посилань. Наприклад, якщо є дві таблиці, які посилаються одна на одну за допомогою зовнішніх ключів. Якщо ми не дозволимо порожнє посилання (тобто NULL) у цих зовнішніх ключах, буде неможливо додати жоден запис до зв’язаних таблиць: щоб додати запис до першої таблиці, потрібен запис у другій таблиці, і навпаки.
Використання NULL як порожнього посилання дозволяє створювати взаємні посилання двох таблиць, що перехресно посилаються одна на одну, а також зберігати ієрархічні структури в реляційних таблицях - при цьому кореневі вузли посилаються на “порожні” записи (тобто просто містять NULL).
Розширені можливості підтримки цілісності посилань за допомогою зовнішніх ключів
Зазвичай декларативного варіанту обмеження зовнішнього ключа цілком достатньо: сервер лише стежить за тим, щоб було неможливо вставити неправильні значення в таблицю із зовнішнім ключем, або - при спробі це зробити - з’являється помилка. Але InterBase дозволяє виконувати набір автоматичних операцій при зміні / видаленні зовнішнього ключа. Для цього використовується наступний набір опцій зовнішнього ключа:
[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}] [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
Ці опції дозволяють визначити різні операції при оновленні або видаленні значень зовнішнього ключа.
Наприклад, ми можемо встановити, що при видаленні первинного ключа в таблиці-майстрі всі записи з таким самим зовнішнім ключем у підпорядкованій таблиці мають бути видалені. У цьому випадку нам потрібно визначити зовнішній ключ наступним чином:
ALTER TABLE INVENTORY ADD CONSTRAINT fkautodel FOREIGN KEY (FK_TITLE) REFERENCES TITLE(ID_TITLE) ON DELETE CASCADE
Власне, для реалізації цих операцій існує системний тригер, який виконує певні дії. У таблиці 1.2 наведено опис операцій різних опцій (зверніть увагу, що опції NO ACTION|CASCADE|SET DEFAULT|SET NULL не можуть використовуватися в одному реченні ON XXX).
Таблиця 1.2
| Подія | Операція | |||
| NO ACTION | CASCADE | SET DEFAULT | SET NULL | |
| ON DELETE | При видаленні зовнішнього ключа нічого не робити - використовується за замовчуванням | При видаленні видалити всі пов’язані записи з підпорядкованої таблиці | При зміні встановити полю зовнішнього ключа значення за замовчуванням |
При зміні встановити полю зовнішнього ключа значення NULL |
| ON UPDATE | При зміні нічого не робити - використовується за замовчуванням | При зміні запису змінити всі пов’язані записи в підпорядкованій таблиці | При видаленні встановити полю зовнішнього ключа значення за замовчуванням |
При видаленні встановити полю зовнішнього ключа значення NULL |
Якщо ми нічого не вказуємо або вказуємо NO ACTION, ми повинні самостійно подбати про зміну зовнішнього ключа (у разі зміни первинного), а при видаленні первинного ключа ми повинні заздалегідь видалити записи з підпорядкованої таблиці. Будьте дуже уважні при використанні опції CASCADE: її необережне використання може призвести до видалення великої кількості пов’язаних записів.
Обмеження CHECK
Одне з найкорисніших обмежень у базі даних - це обмеження check. Його функція дуже проста - перевірити значення, що вставляється в таблицю, на відповідність будь-якій умові та, залежно від виконання цієї умови, вставити дані або ні. Його синтаксис досить простий:
= [CONSTRAINT constraint] CHECK ( )}
Тут constraint - це ім’я обмеження; - це умова пошуку, в якій вставлене / оновлене значення може бути параметром. Якщо умова пошуку виконується, дозволяється вставити / оновити це значення, якщо ні - з’являється помилка. Найпростіший приклад check:
create table checktst( ID integer CHECK(ID>0));
Ця перевірка визначає, чи є вставлене / оновлене значення поля ID більшим за нуль, і залежно від результату дозволяє вставити / оновити нове значення або повідомити про помилку (див. розділ «Розширені можливості мови збережених процедур InterBase» (частина 1)).
Існують також складніші варіанти перевірок. Повний синтаксис умови пошуку наступний:
= {
{ | ()}
| [NOT] BETWEEN AND
| [NOT] LIKE [ESCAPE ]
| [NOT] IN ( [ , …] | )
| IS [NOT] NULL
| {[NOT] {= | < | >} | >= | <=}
{ALL | SOME | ANY} ()
| EXISTS ( )
| SINGULAR ( )
| [NOT] CONTAINING
| [NOT] STARTING [WITH]
| ()
| NOT
| OR
| AND }
Таким чином, CHECK надає великий набір опцій для перевірки вставлених / оновлених значень. Слід пам’ятати про наступні обмеження при використанні CHECK:
- Дані для CHECK беруться лише з поточного запису. Не слід брати дані для виразу в CHECK з інших записів тієї ж таблиці - вони можуть бути змінені іншими користувачами
- Поле може мати лише одне обмеження CHECK
- Якщо для визначення поля використовується домен, що має обмеження CHECK, його не можна перевизначити на рівні конкретного поля в таблиці. Слід зазначити, що CHECK реалізуються системними тригерами, тому потрібно бути обережнішими при використанні дуже довгих умов, які можуть значно сповільнити процеси вставки та оновлення записів.
Видалення обмежень
Дуже часто ми видаляємо різні обмеження з найрізноманітніших причин. Щоб видалити обмеження, слід використовувати оператор ALTER TABLE наступного вигляду: ALTER TABLE tablename DROP CONSTRAINT constraintname
constraintname - це ім’я обмеження, яке потрібно видалити. Якщо при створенні обмеження було вказано певне ім’я, слід використовувати його, а якщо ні, нам доведеться відкрити будь-який інструмент адміністрування InterBase, знайти всі обмеження, пов’язані з ним, і з’ясувати, яке системне ім’я InterBase згенерував для потрібного обмеження.
Слід зазначити, що видаляти обмеження можуть лише власник таблиці або системний адміністратор SYSDBA.