Довідкова цілісність БД і зовнішній ключ

22

Від автора: даний урок, присвячений такій фундаментальній темі, як довідкової цілісності бд (база даних MySQL. Урок відноситься до нового для нашого сайту розділу, присвяченому БД, зокрема БД MySQL. Даний урок є в деякій мірі презентационым, і представляє він відеокурс, що планується до виходу в найближчим часом.

Отже, приступимо до вивчення цілісності БД

Деталі уроку «Цілісність бд, зовнішній ключ»

Тема: MySQL

Складність: Середня

Урок: Відео версія (.mp4)

Час: 01:02:07

Розмір архіву: 62 Mb

Довідкова цілісність БД і зовнішній ключДовідкова цілісність БД і зовнішній ключ

Перш за все, давайте визначимося з самим поняттям цілісність БД. З ним, я думаю, Ви вже стикалися усвідомлено або неусвідомлено. У самому загальному вигляді поняття цілісності БД передбачає відповідність наявної в ній інформації внутрішньою структурою БД, її логікою.

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

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

Тепер про довідкової цілісності БД. Для того, щоб зрозуміти зазначений принцип, створимо БД міст світу. У цій БД ми будемо зберігати назва міста та назва країни, у якій знаходиться місто. Легко зрозуміти, що якщо ми створимо 1 таблицю, то назви країн будуть дублюватися, і при цьому не раз. Якщо у нас є 100 міст однієї країни, то ми повинні біля кожного міста вказати одне і те ж назву країни.

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

Таким чином, у нас з’явилося 2 таблиці таблиця країн і міст. При цьому таблиця країн — це т. зв. довідник. Чому? Дуже легко зрозуміти на прикладі. Отже, маємо таблицю країн з двома країнами: Україна і Росія. Ідентифікатор України буде 1, а Росії — 2. У таблиці країн маємо 3 міста: Київ, Москва, Львів. Для приналежності кожного міста до конкретної країні ми для них поставимо ідентифікатор країни — Києва та Львова — це 1, а у Москви — 2. А розшифровка цих ідентифікаторів знаходиться у таблиці країн, тобто в довіднику.

Тепер, якщо у країни зміниться назва, то ми змінюємо його тільки в довіднику, а таблицю міст не чіпаємо взагалі, оскільки в ній немає назв стан (вони у довіднику). Погодьтеся, зручно. Фактично ми отримали ситуацію, коли ідентифікатор країни з довідника (батьківська таблиця) використовується в іншій таблиці (дочірній). Це як раз і є згадуваний вище зовнішній ключ, який пов’язує поле дочірньої таблиці з полем батьків.

Але поки що цей зв’язок тільки у нас в розумі. Для сервера її немає. Відповідно, жодних обмежень такий зв’язок не накладає. Що нам заважає зараз додати в таблицю міст місто з ідентифікатором країни 3? Тобто це країна, яка не представлена в довіднику. Абсолютно нічого не заважає. Додавши зазначену запис в таблицю міст ми порушуємо цілісність даних. Приміром, ми виводимо на сайт список країн і по кліку на країну виводимо список міст цієї країни. Наш «блудний» місто без країни, таким чином, ніколи виведений не буде. Виходить, що це «зайва», «болтающаяся» запис.

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

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

2. Типи батьківського і дочірнього полів повинні бути абсолютно ідентичними.

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

4. Зв’язок зовнішнього ключа повинна спиратися на поле з обмеженнями в батьківській таблиці, тобто це поле повинно містити обмеження PRIMARY KEY або UNIQUE.

От і все. Тепер можемо створити 2 таблиці з використанням обмеження зовнішнього ключа.

Створюємо таблицю міст:

— 1
CREATE TABLE country
country_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
country_name VARCHAR(50) NOT NULL,
PRIMARY KEY (country_id)
) ENGINE=InnoDB;

Створюємо таблицю (довідник) країн:

— 2
CREATE TABLE city
(
city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
city_name VARCHAR(50) NOT NULL,
country_id TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (city_id),
INDEX ixCity (country_id),
CONSTRAINT country_city FOREIGN KEY (country_id) REFERENCES country (country_id)
) ENGINE=InnoDB;

Тепер давайте розберемо вказані правила з урахуванням запитів. Поле з зовнішнім ключем — це «city.country_id» (для того, щоб не заплутатися, перед назвою поля ми вказали через точку ім’я таблиці). Батьківське поле (на яке посилається зовнішній ключ) — це «country.country_id»

1. Для використання обмежень зовнішнього ключа типи обох таблиць повинні бути InnoDB.
Як бачимо, для обох таблиць містять однаковий тип — «ENGINE=InnoDB».

2. Типи батьківського і дочірнього полів повинні бути абсолютно ідентичними.
Це правило також виконується. Тип дочірнього поля «city.country_id» — «TINYINT UNSIGNED». Тип батька «country.country_id» — також «TINYINT UNSIGNED».

3. Зв’язок зовнішнього ключа спирається на індекси, тому і батьківське, і дочірнє поля повинні містити індекси. Тут також все ок. Дочірнє полі ми індексуємо, додаючи індекс у запиті — «INDEX ixCity (country_id)». Батько індексується автоматично. Справа в тому, що для батька ми маємо PRIMARY KEY, що як раз і передбачає автоматичний індекс для нього.

4. Зв’язок зовнішнього ключа повинна спиратися на поле з обмеженнями в батьківській таблиці, тобто це поле повинно містити обмеження PRIMARY KEY або UNIQUE. І тут все виконується: «country.country_id» — «PRIMARY KEY (country_id)».

Відмінно! Таблиці створені і зв’язок встановлено. Тепер ми не зможемо додати місто з ідентифікатором країни, яка не представлена в довіднику. До речі, існує і зворотна залежність. За замовчуванням ми не зможемо також видалити і країну з довідника, якщо для неї в дочірній таблиці є міста, що, в принципі, логічно.

Але ми можемо поліпшити цю зв’язок і зробити так, щоб якщо ми щось змінюємо в довіднику, то ці зміни стосувалися також дочірньою таблиці. Для цього після оголошення обмеження зовнішнього ключа ми можемо записати вирази «ON DELETE CASCADE ON UPDATE CASCADE». За замовчуванням значення цих виразів вказані «ON DELETE RESTRICT ON UPDATE RESTRICT», тобто заборона. Таким чином, наш запит стане таким:

— 2
CREATE TABLE city
(
city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
city_name VARCHAR(50) NOT NULL,
country_id TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (city_id),
INDEX ixCity (country_id),
CONSTRAINT country_city FOREIGN KEY (country_id) REFERENCES country (country_id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

Тепер, якщо ми видалимо з довідника країну, то з дочірньою таблиці без всяких додаткових запитів будуть видалені всі міста цієї країни. Зручно, не чи правда? І не порушується цілісність даних.
Ну і наостанок давайте простежимо, як можна організувати зв’язок зовнішнього ключа через популярний веб-додаток phpMyAdmin.

Отже, перш за все, повинні збігатися типи таблиць:

Довідкова цілісність БД і зовнішній ключ

Довідкова цілісність БД і зовнішній ключ

Типи полів повинні збігатися:

Довідкова цілісність БД і зовнішній ключ

Створюємо індекс на поле з зовнішнім ключем, клікаючи по відповідній іконці:

Довідкова цілісність БД і зовнішній ключ

Для визначення зв’язку в дочірній таблиці переходимо по посиланню «Relation view»:

Довідкова цілісність БД і зовнішній ключ

І, нарешті, вказуємо зв’язок з батьківським полем і, при необхідності, задаємо поведінка при оновленні/видалення інформації в довіднику:

Довідкова цілісність БД і зовнішній ключ

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

Удачі і до нових зустрічей!