Таблиці. Первинні ключі та генератори
_NOTICE: Цей документ є розділом з книги “The InterBase World”, яку написали Олексій Ковязін та Серг Востріков.
InterBase - це реляційна СУБД. Це означає, що всі дані в InterBase зберігаються у вигляді таблиць. Таблиця, як вона реалізована з точки зору SQL, дуже схожа на звичайну таблицю, яку можна намалювати від руки на аркуші паперу або створити в програмі на кшталт Microsoft Excel. Таблиці в InterBase мають стовпці та рядки, в яких розміщуються дані. Таблиця обов’язково має ім’я, унікальне в межах однієї бази даних. Таблиці є основним сховищем інформації в базі даних, тому слід бути дуже обережним при створенні таблиць.
Існують правила, що описують, як створювати таблиці в реляційній базі даних, відображаючи дані реального світу і водночас дозволяючи організувати ефективне зберігання інформації в базі даних. Процес застосування цих правил для проєктування “правильної” бази даних називається нормалізацією. Ми свідомо взяли слово “правильної” в лапки, тому що “нормалізована база даних” і “оптимізована база даних” - не синоніми. Не обов’язково однозначно дотримуватися правил нормалізації - завжди вносьте поправку на специфіку конкретної задачі.
Нормалізація таблиць у базі даних детально розглядається в книзі [14. і тому ми не будемо намагатися охопити неосяжне і повернемося до нашої теми обговорення - до таблиць InterBase. Розглянемо синтаксис речення DDL (DDL - Data Definition Language, мова визначення даних, докладніше див. глосарій), яке дозволяє створювати таблиці:
CREATE TABLE table [EXTERNAL [FILE] “”] ( [, | …]);
Тут table - це ім’я створюваної таблиці, - опис стовпців (іноді ми говоритимемо - полів) створюваної таблиці. Опція table [EXTERNAL [FILE] “”] означає, що буде створена так звана зовнішня таблиця, яка зберігається не в спільному файлі бази даних, а в окремому файлі з ім’ям . Як бачите, все просто - ми визначаємо ім’я таблиці та стовпці, які вона містить. Тепер розглянемо детально, як визначати стовпці. Синтаксис створення стовпця описується наступним реченням DDL:
= col { datatype | COMPUTED [BY] (< expr>)} | domain}
[DEFAULT { literal | NULL | USER}]
[NOT NULL] [ ]
[COLLATE collation]
Це досить велике визначення, однак у визначенні стовпця обов’язковою є лише невелика частина наведених речень. Кожен стовпець у таблиці повинен мати ім’я, унікальне в межах таблиці, а також тип даних, визначений реченням datatype, або вираз для обчислення значення стовпця (для обчислюваних стовпців), або домен (див. нижче), визначений domain. Типи даних були розглянуті в розділі “Типи даних”; тому ви легко зрозумієте, як формується SQL-вираз для створення таблиці.
Під’єднаємося до нашої бази даних FIRSTBASE.gdb, створеної раніше в розділі “Створення бази даних”, і спробуємо попрацювати з таблицями на практиці. Коли йдеться про створення, видалення та оновлення таблиць, підійде будь-який з адміністративних інструментів InterBase - з перелічених у додатку “Інструменти адміністратора та розробника InterBase”, а також стандартна утиліта isql.exe з комплекту поставки будь-якого клону InterBase.
Ось приклад простої таблиці з назвою TABLE_EXAMPLE, що містить 3 поля різних типів:
CREATE TABLE Table_example ( ID INTEGER, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION);
Ця таблиця ілюструє найчастіший випадок у процесі розробки бази даних. Однак існують й інші способи визначення полів. Наприклад, ми можемо задати тип поля за допомогою доменів. Домен - це тип, визначений користувачем для зручності застосування певних комбінацій параметрів типу. Наприклад, можна визначити домен D_ID для задання полів ідентифікаторів. Визначивши домен, ми можемо використовувати його для задання типу поля:
CREATE DOMAIN D_ID AS INTEGER; CREATE TABLE Тable_example ( ID D_ID, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION);
Поле ID матиме тип, визначений доменом D_ID. Таким чином, визначивши тип поля в домені, необхідні перевірки та обмеження, ми можемо застосовувати цей домен багаторазово для створення полів однакового призначення. Наприклад, грошових, без стомливого та небезпечного через ризик помилки копіювання визначень змінних типів. Третій спосіб задати стовпець у таблиці - визначити його як обчислюваний (COMPUTED BY) і вказати умову, за якою обчислюватиметься його значення. Наприклад, ми можемо захотіти мати в нашій таблиці стовпець, який обчислює 10% від значення поля PRICE_1. У цьому випадку слід написати таку команду:
CREATE TABLE Тable_example ( ID INTEGER, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION, PRICE_10 COMPUTED BY (PRICE_1.0.1));
Але не думайте, що тепер, щойно ми вставимо дані в поле PRICE_1, у полі PRICE_10 з’явиться десята частина від значення цього поля. Ні, процес тут складніший. Насправді ми отримаємо потрібну десяту частину лише звернувшись до поля PRICE_10, наприклад, під час виконання запиту SELECT до цієї таблиці. Тобто в обчислюваному полі не зберігаються жодні дані, а виконується обчислення виразу, пов’язаного з полем, і результат видається як відповідь на запит.
Отже, ми розглянули 3 основні способи задання полів у таблиці. Тепер детально розглянемо опції, які можна встановити при створенні стовпця. Опція [DEFAULT {literal | NULL | USER}] - дозволяє встановити значення стовпця за замовчуванням. Це дуже зручно для автоматичного заповнення даних. Існує 3 способи встановлення значення за замовчуванням. Перший позначається як literal і дозволяє встановлювати значення за замовчуванням як текстові константи, числа або дати. Наприклад, ми можемо сформувати такі вирази для створення стовпця з текстовими значеннями за замовчуванням: NAME VARCHAR(80) DEFAULT ‘Василий Станиславович’
Таким чином, усі поля, вставлені в таблицю, отримають значення за замовчуванням, тобто якщо для поля NAME не визначено іншого значення, з’явиться рядок ‘Василий Станиславович’. Другий спосіб встановити значення за замовчуванням - вказати DEFAULT NULL у визначенні стовпця. І в новостворених записах значення цього стовпця буде NULL, якщо інше значення, звісно, не було явно задане. Приклад:
PRICE_1 DOUBLE PRECISION DEFAULT NULL
Третій спосіб встановити значення за замовчуванням - вказати DEFAULT USER у визначенні стовпця. Таким чином, у новостворених записах це поле міститиме ім’я поточного користувача, тобто користувача, який встановив з’єднання з InterBase і виконав це вставлення (докладніше про користувачів див. розділ “Безпека в InterBase: користувачі, їх функції та права” (частина 4)). Для деяких полів важливо, щоб поле мало непорожнє значення. Наприклад, поле, яке не може бути порожнім згідно з технічним завданням. Щоб встановити обмеження на рівні бази даних, що поле повинно мати певне значення, необхідно внести наступне доповнення до опису стовпця:
NAME VARCHAR(80) NOT NULL
Таким чином, буде поле, в якому не можуть зберігатися нульові значення. Зазвичай обмеження NOT NULL поєднується з опцією DEFAULT, яка однозначно присвоює коректне значення цьому полю. Але часто обмеження NOT NULL недостатньо. Наприклад, у випадку зберігання цін у базі даних цілком очевидно, що вони не можуть набувати від’ємних значень (хоча було б чудово, якби нам доплачували при купівлі товарів). Щоб змусити сервер перевіряти значення цін, що вставляються в базу даних, на умову позитивності, необхідно визначити стовпець наступним чином:
PRICE_1 DOUBLE PRECISION CHECK (PRICE_1>0)
Значення, що вставляються в стовпець PRICE_1, перевірятимуться на умову позитивності. Слід зазначити, що різні сумісні опції можна комбінувати, і, наприклад, ми можемо встановити непорожнє значення та перевірку на позитивність:
PRICE_1 DOUBLE PRECISION NOT NULL CHECK (PRICE_1>0)
При створенні колонок деякі параметри не можна поєднувати, наприклад, неможливо встановити NULL за замовчуванням і одночасно обмеження на непусте значення. Слід зазначити, що перевірки можуть виконувати набір корисних функцій для управління даними в базі даних. Ми детально розглянемо їх використання в розділі «Обмеження бази даних».
Отже, ми розглянули способи створення таблиць і полів з різними параметрами. Однак є випадки, коли нам доводиться змінювати вже існуючу таблицю. Звичайно, ми можемо повністю перестворити таблицю. Спочатку слід виконати команду видалення таблиці, а потім створити її знову. Наприклад:
DROP TABLE Table_example; CREATE TABLE Table_example(ID NUMERIC(15,2);
Але такий спосіб зміни таблиць має значні недоліки. При видаленні таблиці за допомогою команди DROP всі дані, які містяться в таблиці, видаляються, і щоб їх не втратити, необхідно скопіювати їх у тимчасові таблиці. Це досить клопітно. Тому існує команда ALTER TABLE для легкого змінення структури таблиць, яка дозволяє додавати нові поля, видаляти існуючі, а також додавати / видаляти обмеження цілісності посилань.
Наприклад, ми хочемо додати ще одну колонку до таблиці, призначену для зберігання даних про по батькові особи:
ALTER TABLE Table_example ADD Patronimic VARCHAR(80);
Після виконання цієї команди наша таблиця Table_example матиме нову колонку з назвою Patronimic і типом VARCHAR (80). Якщо ми хочемо видалити колонку з назвою NAME з таблиці, слід виконати наступне:
ALTER TABLE Table_example DROP Name;
Повний синтаксис оператора ALTER TABLE можна переглянути в [1.. Це дуже корисна команда, і ми часто будемо її використовувати.
А що робити, запитаєте ви, якщо необхідно змінити колонку? Наприклад, ми вирішили, що для зберігання імен краще використовувати поле HUMAN_NAME, ніж NAME. У цьому випадку ми можемо застосувати ALTER TABLE
ALTER TABLE Table_example ALTER COLUMN NAME TO HUMAN_NAME;
Якщо ми вирішили змінити тип поля, наприклад, збільшити кількість символів, що зберігаються в полі, нам доведеться змінити домен цього поля за допомогою оператора ALTER DOMAIN (див. розділ «Типи даних» вище).
Отже, ми розглянули створення та змінення таблиць в InterBase. Тепер час трохи заглибитися в теорію баз даних. InterBase, як уже було сказано, є реляційною базою даних. Крім того, це означає, що кожен запис у таблиці повинен мати характеристику, за якою один запис можна відрізнити від іншого. Спеціальний механізм унікальних ключів служить цій меті.
Первинні ключі в таблицях
Звичайно, ми можемо створити таблицю, яка не містить жодних ключів. Нам це не заборонено. Але, як було сказано раніше, створення ефективної бази даних неможливе без дотримання правил нормалізації. Наявність ключів є найважливішим елементом нормалізації. Тому, хоча ми не ставимо за мету розглядати теорію та нормалізацію баз даних, ми повинні ввести визначення ключів і розглянути їх функцію в InterBase. Ми будемо рухатися крок за кроком і почнемо з найпоширенішого типу ключів - первинного ключа.
Отже, що таке первинний ключ? Це одне або кілька полів у таблиці, які однозначно ідентифікують записи в межах цієї таблиці. Це звучить складно, але насправді все дуже просто. Уявіть звичайну таблицю, наприклад, бухгалтерський лист. Яка найперша колонка? Правильно, порядковий номер - 1, 2, 3 … Цей номер позначає унікальний рядок у таблиці, і достатньо знати цей номер, щоб знайти рядок у цій таблиці. У цьому прикладі це буде первинний ключ. Переважна більшість таблиць у реляційній базі даних обов’язково має первинний ключ (PK - скорочення від Primary key). Загальним правилом при створенні таблиць є створення первинного ключа. Первинний ключ можна створити під час створення таблиці або пізніше. Припустимо, що на момент створення таблиці ми вирішили, що поле ID буде нашим первинним ключем. Тоді ми можемо додати первинний ключ наступним чином:
CREATE TABLE Table_example ( ID INTEGER NOT NULL, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION, CONSTRAINT pkTable PRIMARY KEY (ID));
Що було зроблено для створення первинного ключа для таблиці table_example? Давайте пошукаємо, що змінилося у визначенні таблиці? По-перше, колонка ID отримала додаткове визначення NOT NULL. Це важливо, оскільки первинний ключ повинен бути унікальним і не допускати невизначених значень. А NULL, як ви знаєте, є невизначеним значенням. Таким чином, усі поля, включені в первинний ключ, повинні мати обмеження NOT NULL. Для завершення створення первинного ключа в кінці таблиці слід написати: CONSTRAINT ()
Повний синтаксис обмежень можна знайти в розділі «Обмеження бази даних» þ. 1, а для нашого прикладу первинного ключа це виглядатиме так:
CONSTRAINT pkTable PRIMARY KEY (ID)
Тут pkTable - це назва первинного ключа, а ID - колонки, які він містить. Такий спосіб визначення первинних ключів для таблиць зручний при масовому створенні таблиць (наприклад, при побудові прототипу бази даних на основі скриптів, отриманих різними CASE-засобами). Але що робити, якщо нам потрібно додати / видалити первинний ключ до таблиці, коли вона вже існує і заповнена даними? Для цього слід застосувати ще одне розширення команди - ALTER TABLE. Приклад додавання первинного ключа до нашої таблиці:
ALTER TABLE TABLE_EXAMPLE ADD CONSTRAINT FF PRIMARY KEY (ID);
Таким чином, таблиця Table_example матиме точно такий самий первинний ключ, як у попередньому прикладі, коли він створювався разом із таблицею. Щоб видалити первинний ключ, слід ввести наступну команду:
ALTER TABLE Table_example DROP CONSTRAINT pkTable;
Таким чином, ключ з назвою pkTable буде видалено з бази даних.
Генератори - найкращі друзі первинних ключів
Ми повинні сказати кілька слів про реалізацію первинного ключа. Оскільки він призначений для підтримки унікальності, жодні два записи в одній таблиці не можуть мати однакові значення цього ключа. Тобто, щоб виконати умову, при вставці нового запису в таблицю InterBase має перевірити всі записи в таблиці та з’ясувати, чи містить таблиця такі значення чи ні. Для швидкого пошуку InterBase має механізм індексів - спеціальних об’єктів InterBase, які дозволяють дуже швидко знайти запис у таблиці. Тому при створенні та видаленні первинного ключа створюється або видаляється індекс для того поля (або полів), яке включене в первинний ключ.
Як було сказано раніше, первинний ключ може містити кілька полів. Таким чином, ми можемо помітити унікальність комбінації значень цих полів. Наприклад, якщо ми визначимо ключ для полів ID і NAME, сервер контролюватиме, щоб у таблиці не було однакових комбінацій цих полів. Тобто комбінації полів ID і 1 та «Іванов», 2 та «Іванов» будуть правильними, оскільки вони відрізняються значеннями поля ID.
Таким чином, первинний ключ може включати кілька полів будь-яких типів. Однак на практиці найпоширенішим типом ключа є лічильник - ціле поле, яке містить зростаючі значення. Чому це так? Це відображення старої суперечки між природними та замінними ключами. Концепція природних ключів стверджує, що як ключ ми повинні намагатися використовувати значення, які реально існують у предметній області, яку відображає база даних. Наприклад, якщо ми розробляємо систему реєстрації людей для паспортного столу, згідно з цією концепцією комбінація номера та серії паспорта має бути взята як первинний ключ. Дійсно, кожна людина повинна мати унікальну комбінацію номера та серії паспорта. Однак що робити з тим фактом, що людина може змінити паспорт протягом свого життя (у зв’язку з досягненням певного віку, одруженням тощо)? У цьому випадку нам доведеться змінити номер і серію паспорта, встановлені у відповідність конкретній людині, тобто фактично змінити наш первинний ключ. Це небажано з точки зору розробки додатків баз даних: враховуючи розгалужену систему зв’язків між таблицями (наступний розділ присвячений цьому), розробнику доведеться докласти чимало зусиль, щоб контролювати цю ситуацію.
Тому в більшості випадків використовується замінний ключ. Замінний - означає штучний, тобто такий, що не існує в домені даних, який описує наша база даних, і створений штучно - для зручності розробки додатків бази даних. Як уже було сказано, зазвичай лічильник є первинним ключем. Деякі СУБД, такі як Paradox і MS SQL, мають спеціальний тип - лічильник (автоінкремент). При додаванні нового запису до таблиці значення поля цього типу автоматично збільшується на величину кроку - зазвичай на одиницю. В InterBase немає поля типу лічильника, однак таку поведінку можна реалізувати. Для створення поля, яке автоматично заповнюватиметься при додаванні запису до таблиці, використовується набір ресурсів: перший з них - генератор.
Що таке генератор? Якщо говорити просто, генератор - це іменований лічильник. У межах бази даних ми можемо створити лічильник, дати йому унікальне ім’я в межах цієї бази та керувати значеннями цього лічильника. Це і буде генератор. Ось приклад DDL-операторів, які це пояснять:
CREATE GENERATOR g1; SET GENERATOR g1 TO 2445;
У першому рядку цього прикладу створюється генератор з ім’ям g1, а в другому рядку цьому генератору присвоюється значення 2445. Тепер виникає питання, як використовувати отриманий генератор. В InterBase існує вбудована функція GEN_ID для отримання та зміни значень генераторів. Ця функція приймає як параметри ім’я генератора та значення кроку, яке потрібно застосувати до даного генератора, і повертає ціле значення, що відповідає значенню генератора, отриманому в результаті додавання кроку до нього. Ось приклад виклику функції GEN_ID у тригері або збереженій процедурі:
Current_value = GEN_ID (g1, 1)
Якщо ми хочемо отримати значення генератора, можна використати наступний запит:
SELECT GEN_ID(g1, 1) FROM RDB$ DATABASE
Оскільки таблиця RDB$ Database завжди містить лише один запис, ми отримаємо значення генератора g1 як результат даного запиту.
Тут current_value - це змінна (у наступних розділах ви знайдете інформацію про те, як використовувати змінні в InterBase), g1 - генератор, 1 - крок. У цьому прикладі значення генератора g1 потрапить у змінну current_value після додавання кроку 1 до нього, тобто наступне значення генератора. Зверніть увагу, що крок може бути не рівним 1! Більше того, він може бути навіть від’ємним: Current_value = GEN_ID (g1, -23)
У результаті виконання цієї функції поточне значення генератора g1 буде зменшено на 23. Як бачите, діапазон можливих застосувань генераторів досить широкий - його можна використовувати не лише для отримання значень первинних ключів, але й для відстеження глобальних змін у базі даних.
Люди, знайомі з базами даних, можуть поставити питання: “що станеться, якщо одночасно кілька клієнтів спробують вставити дані в ту саму таблицю і одночасно “потягнуть” генератори? Чи отримають вони однакові чи різні значення генератора?”. Вони однозначно отримають РІЗНІ значення генератора. Наскільки б “одночасною” не була спроба отримати значення генератора, кожен, хто звернувся, отримає унікальне значення. Це гарантується “конструкцією” генераторів: вони працюють на найнижчому рівні сервера, і жодні процеси запису чи вставки на них не впливають - часто кажуть, що генератори працюють “поза контекстом транзакцій”. Якщо ви хочете дізнатися про транзакції, прочитайте розділ “Транзакції. Параметри транзакцій” (частина 1); як влаштовані генератори - “Структура бази даних InterBase” (частина 4). Отже, завдяки генераторам ми маємо надійний механізм для створення унікальних первинних ключів. Однак чи можемо ми використовувати цей механізм? Як вставити значення, отримане від генератора, у поле первинного ключа?
Для цього існує два способи - вставка первинного ключа від імені клієнта та від імені сервера. Щоб освоїти перший спосіб, слід звернутися до розділу “Використання основних компонентів FIBPlus”, а щоб зрозуміти другий - до розділу “Тригери” (частина 1). Тут ми коротко розглянемо основну суть обох способів.
У випадку створення первинного ключа від імені клієнта відбувається наступне. Коли формується запис, який буде вставлено в базу даних, виконується виклик функції GEN_ID (, 1), і отримане значення підставляється в цей запис. Потім відбувається вставка в таблицю, і ми гарантовано отримуємо унікальний первинний ключ.
Другий спосіб - створення первинного ключа від імені сервера - загалом усуває будь-яку турботу з боку клієнта про те, яким буде значення первинного ключа. У цьому випадку при вставці запису спрацьовує тригер - спеціальний об’єкт бази даних, який може виконувати будь-які операції при вставці/видаленні/оновленні записів у таблицях. І в цьому тригері виконуються наступні операції: виклик функції GEN_ID, отримання необхідного значення генератора та його вставка в таблицю. Перевага другого способу полягає в тому, що при розробці клієнтського додатку немає потреби турбуватися про створення первинного ключа взагалі - єдине, що потрібно зробити, це один раз написати необхідний тригер. Але недолік полягає в тому, що ми не можемо отримати значення згенерованого ключа в додатку одразу після вставки! Якщо ми використовуємо перший спосіб, ми можемо отримати значення первинного ключа, хоча нам доведеться піклуватися про його створення щоразу при вставці. Важко сказати напевно, який спосіб кращий, усе залежить від конкретної задачі. Далі в цій книзі ми розглянемо можливі варіанти вирішення питань, пов’язаних із роботою з первинним ключем.
Висновок
Отже, у цьому розділі ми розглянули, як створювати та оновлювати таблиці в InterBase, а також як працювати з первинними ключами. Таким чином, ми розглянули основні об’єкти в InterBase, які умовно можна назвати статичними, оскільки вони лише зберігають інформацію і не виконують її перетворення. Далі ми поговоримо про способи керування інформацією та перетворення інформації в межах бази даних.