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

Тема роботи: Введення обмежень в базах даних в InterBase.

Мета роботи: Навчити створювати первинні та вторинні ключі у середовищі сервера InterBase.

 

ОСНОВНІ ТЕОРЕТИЧНІ ВІДОМОСТІ

Види обмежень в базі даних.

Існують такі види обмежень в базі даних InterBase:

Ø первинний ключ – PRIMARY KEY;

Ø унікальний ключ – UNIQUE KEY;

Ø зовнішній ключ – FOREIGN KEY – може включати автоматичні тригери ON UPDATE і ON DELETE; перевірки – CHECK.

Обмеження бази даних бувають двох типів – на основі одного поля і на основі декількох полів таблиці. Першим в описі загального синтаксису обмежень йде ключове слово [ CONSTRAINT constraint_name ]. Тут CONSTRAINT означає обмеження, а constraint_name – ім’я цього обмеження. Як бачите, все це взяте в квадратні дужки і, значить, необов’язкове.

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

Якщо не вказати ім’я для обмеження, InterBase автоматично генерує його. Однак краще все ж явно назначити ім’я, щоб покращити читабельність схеми бази даних.

Призначивши ім’я обмеженню, необхідно задати його тип. Розглянемо різні типи обмежень.

 

Первинний і унікальний ключі.

 

Приклади первинних і унікальних ключів:

CREATE TABLE Pk1(

Number NUMERIC( 15,0 ) NOT NULL PRIMARY KEY, /*первинний ключ*/

Name VARCHAR (5O) NOT NULL UNIQUE, /*унікальний ключ */

Kilkist INTEGER NOT NULL UNIQUE /* ще один унікальний ключ */);

Синтаксис створення первинного і унікального ключів на основі декількох полів:

<PkUktconstraint> = [ CONSTRAINT constraint ] { PRIMARY KEY|UNIQUE) (col [, col...])

CREATE TABLE Pk2(
Number INTEGER NOT NULL,
Name VARCHAR (5O) NOT NULL,
Kilkist INTEGER NOT NULL,
Vartist NUMERIC (15,4) NOT NULL,
CONSTRAINT PkPk2 PRIMARY KEY (Number, Name),

/* первинний ключ PkPk2 на основі двох полів */
CONSTRAINT UkPk2 UNIQUE (kol, Stoim));

/* унікальний ключ UkPk2 на основі двох полів */

Зверніть увагу на те, що всі поля, які входять в склад первинного і унікального ключів, повинні декларуватися як NOT NULL, так як ці ключі не можутьмати невизначеного значення.
Окрім створення обмежень первинних і унікальних ключів в момент створення таблиці існує можливість додавати обмеження в уже існуючу таблицю. Для цього використовується команда 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 Pkall PRIMARY KEY (idl, id2);

потім унікальний ключ:
ALTER TABLE PkAlter
ADD CONSTRAINT Ukal UNIQUE (uid);

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

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

 

Наступним обмеженням, яке часто використовується в базах даних InterBase, є обмеження зовнішнього ключа. Це дуже потужний засіб для підтримання цілістності за посиланнями в базі даних, яке дозволяє не лише контролювати наявність правильних посилань в базі даних, але й автоматично керувати цими посиланнями!

 

Для збереження такого документа в базі даних створюються дві таблиці – одна для збереження заголовків накладних, а друга – для збереження вмісту накладної – записів про товари і їх кількість. Такі таблиці називаються головною і підпорядкованою або таблицею-майстером і деталь-таблицею.

 

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

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

 

Давайте розглянемо смисл накладених обмежень зовнішнього ключа на прикладі таблиць, що містять інформацію про накладні.

 

Для цього створимо дві таблиці для збереження накладної – таблицю SALES для збереження заголовку і таблицю ITEMS для збереження інформації про товари, що входять до накладної.

CREATE TABLE SALES (

SALE_NUMBER INTEGER NOT NULL,

CUSTOMER_ID INTEGER,

SALE_DATE TIMESTAMP,

TOTAL_PRICE DOUBLE PRECISION,

CONSTRAINT "PSALE_NUMBER" PRIMARY KEY (SALE_NUMBER));

 

Зверніть увагу на те, що ми відразу задали первинний ключ в таблиці заголовка на основі поля SALE_NUMBER – номер покупки. Решта полів таблиці SALES містять інформацію про заголовок накладної – ідентифікаційний номер покупця, дату закупки, суму, заплачену покупцем по всім накладним даної купівельної операції. Тепер визначимо таблицю для збереження інформації про товарах, що входять до накладної:

 

CREATE TABLE ITEMS (

SALE_NUMBER INTEGER,

ITEM_NO INTEGER,

PART_NO VARCHAR (10),

QTY SMALLINT);

 

Давайте розглянемо поля таблиці ITEMS. По перше, ще поле ITEM_NO, яким ідентифікується номер накладної. По-друге – це SALE_NUMBER – цілочисельне поле, яке використовується для посилання на ідентифікатор заголовка SALE_NUMBER в таблиці заголовків накладних SALES. Далі іде поле PART_NO – поле, яке використовується для посилання на ідентифікатор PART_NUMBER (номер товару) в таблиці PART, і QTY – що описує сумарну вартість купленого товару.

В принципі, ніщо не заважає нам заповнити таблицю ITEMS записами, що посилаються на неіснуючі записи в таблиці SALES. Також нічого не заважає вилученню заголовка вже існуючої накладної, в результаті чого записи про товари можуть стати "нічиїми". Це ж саме можна сказати і про поле PART_NO.

 

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

Тому необхідно явно накласти обмеження на те, що в таблицю ITEMS можуть вміщуватися лише такі записи про товари, які мають коректне ПОСИЛАННЯ на заголовок накладної. Власне це і є обмеження зовнішнього ключа, яке дозволяє вставляти в поля, що входять в обмеження, лише ті значення, які є в іншій таблиці.

 

Таке обмеження можна організувати за допомогою зовнішнього ключа. Для даного прикладу необхідно накласти обмеження зовнішнього ключа на поле SALE_NUMBER і пов’язати його з первинним ключем SALE_NUMBER в SALES. Додати зовнішні ключі в уже існуючу таблицю можна такими командами:

 

ALTER TABLE ITEMS ADD FOREIGN KEY (PART_NO) REFERENCES PART (PART_NUMBER);

ALTER TABLE ITEMS ADD FOREIGN KEY (SALE_NUMBER) REFERENCES SALES (SALE_NUMBER);

Зауваження! Для створення зовнішнього ключа таблиці ITEMS по полю SALE_NUMBER необхідно створити первинні ключі.

Тут ITEMS – ім’я таблиці, на яку накладається обмеження зовнішнього ключа; PART та SALES – імена таблиць, що надають значення (ОСНОВУ посилання) для зовнішнього ключа. В дужках наводяться імена полів, по яким встановлюється зв’язок.

В наведених прикладах перед параметром FOREIGN KEY може розміщуватися CONSTRAINT foreign_name для того, щоб задати прямо ім’я обмеження. Якщо іде зв’язок по групі полів, всі вони перелічуються в дужках через кому.

 

Обмеження CHECK

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

Синтаксис цього обмеження:

< tconstraint > = [CONSTRAINT constraint] CHECK (<search_condition>)

Тут constraint – ім’я обмеження; < search_condition > – умови пошуку, в якому в ролі параметра може приймати участь вставлене/змінене значення. Якщо умови пошуку виконуються, то вставка/зміна цього значення дозволяється, якщо ні – виникає помилка. Самий простий приклад перевірки:

CREATE TABLE Checktst (
ID integer CHECK( ID>0 ));

Ця перевірка встановлює, чи більше нуля вставлюване/змінюване значення поля ID, і в залежності від результату дозволяє вставити/змінити нове значення чи викликати виключення. Поле може мати лише одне обмеження CHECK.

Вилучення обмежень.

Часто доводиться вилучати різні обмеження по різним причинам. Щоб вилучити обмеження, слід використати речення ALTER TABLE такого виду:

ALTER TABLE Сablename
DROP CONSTRAINT constraint_name
де constraint_name – ім’я обмеження, яке слід вилучити. Слід відзначити, що вилучати обмеження може лише власник таблиці або системний адміністратор SYSDBA.

З А В Д А Н Н Я

1. Відкрийте на сервері базу даних Sales, яку ви створили.

2. За допомогою ISQL додайте до таблиць CUSTOMER, PART, SALES первинні ключі по полям CUSTOMER_ID, PART_NUMBER, SALE_NUMBER відповідно.

3. За допомогою ISQL додайте до таблиць SALES та ITEMS вторинні ключі по полям CUSTOMER_ID, PART_NUMBER, SALE_NUMBER відповідно.

4. Перегляньте за допомогою IB Console створені вами елементи бази даних.

5. Оформіть звіт по роботі.

 

КОНТРОЛЬНІ ЗАПИТАННЯ

1. Які типи обмежень існують?

2. Яка команда створює обмеження?

3. Яка команда вилучає обмеження?

4. Яке обмеження створює первинний ключ?

5. Як додати обмеження в уже існуючу таблицю?

6. Що виконує обмеження CHECK? Яка його структура?

7. Коли доцільно використовувати обмеження DEFAULT?