5. Зв'язки між таблицями

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

Реляційна база даних — це сукупність таблиць, пов'язаних між собою відношеннями. Розглянемо приклад, заснований на попередній таблиці [BIBLIO], структура якої була такою:

Приклад вмісту був таким:

Нам може знадобитися інформація про різних авторів цих творів, наприклад, про nom та prénom, їхні дати народження, nationalité. Створимо таку таблицю. Клацнімо правою кнопкою миші на [DBBIBLIO / Tables], а потім виберемо опцію [New Table]:

Тепер створимо таку таблицю [AUTEURS]:

`id`	первинний ключ таблиці — слугує для однозначної ідентифікації рядка
`nom`	ім'я автора
`prénom`	ім’я автора, якщо воно є
`date_naissance`	дата народження
`nationalite`	його країна походження

Зміст таблиці [AUTEURS] може бути таким:

Повернемося до таблиці [BIBLIO] та її вмісту:

У стовпці [AUTEUR] таблиці більше немає потреби вказувати ім’я автора. Натомість краще вказати його ідентифікатор (id) з таблиці [AUTEURS]. Отже, створимо нову таблицю під назвою [LIVRES]. Для її створення ми використаємо скрипт [biblio.sql], створений у розділі 3.14. Завантажуємо цей скрипт за допомогою інструменту [Script Executive, Ctrl-F12]:

Ми модифікуємо скрипт створення таблиці BIBLIO, щоб привести його у відповідність до скрипта таблиці LIVRES:

CREATE TABLE LIVRES (
ID          INTEGER NOT NULL,
TITRE       VARCHAR(30) NOT NULL,
AUTEUR      INTEGER,
GENRE       VARCHAR(30) NOT NULL,
ACHAT       DATE NOT NULL,
PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
DISPONIBLE  CHAR(1) NOT NULL
);

INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (2, 'Les fleurs du mal', 8, 'POèME', '1978-01-01', 120, 'n');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (3, 'Tintin au Tibet', 6, 'BD', '1990-11-10', 70, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (4, 'Du côté de chez Swann', 3, 'ROMAN', '1978-12-08', 220.5, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (5, 'La terre', 4, 'ROMAN', '1990-06-12', 55.13, 'n');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (6, 'Madame Bovary', 1, 'ROMAN', '1988-03-12', 143.33, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (7, 'Manhattan transfer', 7, 'ROMAN', '1987-08-30', 352.8, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (8, 'Tintin en Amérique', 6, 'BD', '1991-05-15', 70, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (10, 'Le père Goriot', 5, 'Roman', '1991-09-01', 210, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (11, 'Poèmes saturniens', 2, 'Poème', '1992-09-02', 200, 'o');

COMMIT WORK;



/******************************************************************************/
/****                                                     Унікальні обмеження ****/
/******************************************************************************/

ALTER TABLE LIVRES ADD CONSTRAINT UNQ1_LIVRES UNIQUE (TITRE);


/******************************************************************************/
/****                                                           Первинні ключі ****/
/******************************************************************************/

ALTER TABLE LIVRES ADD CONSTRAINT PK_LIVRES PRIMARY KEY (ID);

Ми коментуємо лише зміни:

рядок 4: поле [AUTEUR] у таблиці стає цілим числом. Це число вказує на одного з авторів таблиці [AUTEURS], створеної раніше.
рядки 11–19: імена авторів замінено на їхні номери.
рядок 29: назва обмеження була змінена. Раніше воно називалося [ UNQ1_BIBLIO ]. Тепер воно називається [ UNQ1_LIVRES ]. Ця назва може бути будь-якою. Однак бажано, щоб вона мала сенс. У даному випадку цього не зроблено. Обмеження для різних полів та різних таблиць бази даних повинні відрізнятися за допомогою різних імен. Нагадаємо, що обмеження у рядку 29 вимагає, щоб заголовок у таблиці був унікальним.
рядок 36: зміна імені обмеження на первинний ключ ID.

Запустимо цей скрипт. Якщо все пройде успішно, ми отримаємо нову таблицю [LIVRES] такого вигляду:

Можна задатися питанням, чи виграли ми в результаті. Адже в таблиці [LIVRES] замість імен авторів вказані їхні номери. Оскільки авторів тисячі, встановити зв’язок між книгою та її автором здається складним завданням. На щастя, нам на допомогу приходить мова SQL. Вона дозволяє нам здійснювати запити до кількох таблиць одночасно. Як приклад наведемо запит SQL, який дозволяє отримати назви книг із бібліотеки разом з інформацією про їхніх авторів. Скористаємося редактором SQL (F12), щоб відправити такий запит SQL:

SQL> select LIVRES.titre, AUTEURS.nom, AUTEURS.prenom,AUTEURS.date_naissance
FROM LIVRES inner join AUTEURS on LIVRES.AUTEUR=AUTEURS.ID
ORDER BY AUTEURS.nom asc

Поки що зарано пояснювати це замовлення SQL. Ми повернемося до нього найближчим часом. Результат цього запиту такий:

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

Підсумуємо те, що ми щойно зробили:

у нас є дві таблиці, що містять інформацію різного характеру:
- таблиця AUTEURS містить інформацію про авторів
- таблиця LIVRES містить інформацію про книги, придбані бібліотекою
ці таблиці пов’язані між собою. Кожна книга обов’язково має автора. Вона може мати навіть декількох авторів. Цей випадок тут не враховувався. Поле [AUTEUR] таблиці [LIVRES] посилається на рядок таблиці [AUTEURS]. Це називається відношенням.

Відношення, що пов’язує таблицю [LIVRES] з таблицею [AUTEURS], насправді є різновидом обмеження: запис у таблиці [LIVRES] завжди повинен мати номер автора, який існує в таблиці [AUTEURS]. Якби рядок таблиці [LIVRES] містив номер автора, якого немає в таблиці [AUTEURS], то виникла б аномальна ситуація, коли неможливо було б визначити автора книги.

Таблиця SGBD здатна перевіряти, чи це обмеження завжди дотримується. Для цього ми додамо обмеження до таблиці [LIVRES]:

Зв'язок, що пов'язує поле [AUTEUR] таблиці [LIVRES] з полем [ID] таблиці [AUTEURS], називається зв'язком із зовнішнім ключем. Поле [AUTEUR] таблиці [LIVRES] у наведеному вище майстрі називається «зовнішнім ключем» або «foreign key». Визначення зовнішнього ключа означає, що значення стовпця [c1] таблиці [T1] має існувати у стовпці [c2] таблиці [T2]. Стовпець [c1] називається «зовнішнім ключем» таблиці T1 щодо стовпця [c2] таблиці [T2]. Столбець [c2] часто є первинним ключем таблиці [T2], але це не обов’язково.

Ми визначаємо зовнішній ключ [AUTEUR] таблиці [LIVRES] на полі [ID] таблиці [AUTEURS] наступним чином:

назва обмеження: вільна
стовпець «чужорідний ключ», у даному випадку стовпець [AUTEUR] таблиці [LIVRES]
таблиця, на яку посилається зовнішній ключ. У цьому випадку стовпець [AUTEUR] таблиці [LIVRES] повинен мати значення у стовпці [ID] таблиці [AUTEURS]. Отже, посилання здійснюється на таблицю [AUTEURS].
Столбець, на який посилається зовнішній ключ. У даному випадку це стовпець [ID] таблиці [AUTEURS].

Перевіряємо це обмеження:

Якщо все гаразд, воно приймається:

Які наслідки має це нове обмеження зовнішнього ключа? За допомогою редактора SQL (F12) спробуємо вставити рядок у таблицю LIVRES із неіснуючим номером автора:

Операція [INSERT], наведена вище, спробувала вставити книгу з неіснуючим номером автора (100). Виконання запиту завершилося невдачею. Відповідне повідомлення про помилку вказує на порушення обмеження зовнішнього ключа «FK_LIVRES_AUTEURS». Це саме те обмеження, яке ми щойно визначили.

5.2. Операції з'єднання двох таблиць

У базі даних [DBBIBLIO] (або будь-якій іншій) створимо дві тестові таблиці з іменами TA та TB, які мають такі визначення:

Таблиця TA

- ID: первинний ключ таблиці TA

- DATA: довільні дані

Таблиця TB

- ID: первинний ключ таблиці TB

- IDTA: зовнішній ключ таблиці TB, який посилається на стовпець ID таблиці TA. Отже, значення стовпця IDTA таблиці TA має існувати у стовпці ID таблиці TA

- VALEUR: будь-які дані

У редакторі SQL (F12) ми будемо видавати команди SQL, які одночасно використовують обидві таблиці TA та TB.

SQL>select * from TA,  TB

Команда SQL за ключовим словом FROM задіює обидві таблиці TA та TB. Операція FROM TA, TB призведе до тимчасового створення нової таблиці, в якій кожен рядок таблиці TA буде пов’язаний з кожним рядком таблиці TB. Отже, якщо таблиця TA має NA рядків, а таблиця TB має NB рядків, то таблиця, що утворилася в результаті, матиме NA × NB рядків. Це показано на знімку екрана вище. Крім того, кожен рядок містить стовпці обох таблиць. Стовпці coli, вказані в порядку [SELECT col1, col2, ... FROM ...], позначають ті, які слід зберегти. Тут ключове слово * вказує, що запитуються всі стовпці результуючої таблиці. Іноді кажуть, що результуюча таблиця з попереднього запиту SQL є декартовим добутком таблиць TA та TB.

Вище кожен рядок таблиці TA було зіставлено з кожним рядком таблиці TB. Зазвичай до рядка таблиці TA потрібно зіставити рядки таблиці TB, які мають з ним зв’язок. Цей зв’язок часто має вигляд обмеження зовнішнього ключа. Саме так і є в даному випадку. З рядком таблиці TA можна пов’язати рядки таблиці TB, які задовольняють відношенню TB.IDTA=TA.ID. Існує кілька способів виконати цей запит:

SQL>select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA, TB where TA.ID=TB.IDTA

Попереднє замовлення SQL аналогічне попередньому, проте має дві відмінності:

рядки, отримані в результаті декартового добутку TA × TB, фільтруються за допомогою умови WHERE, яка пов’язує з рядком таблиці TA лише ті рядки таблиці TB, які задовольняють співвідношенню TB.IDTA=TA.ID
запитуються лише певні стовпці за допомогою синтаксису [T.col], де T — ім’я таблиці, а col — ім’я стовпця цієї таблиці. Цей синтаксис дозволяє усунути неоднозначність, яка може виникнути, якщо дві таблиці мають стовпці з однаковими іменами. Якщо такої неоднозначності немає, можна використовувати синтаксис [col], не вказуючи таблицю, до якої належить цей стовпець.

Отриманий результат такий:

Такий самий результат можна отримати за допомогою такого запиту: SQL:

SQL>select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA inner join TB on TA.ID=TB.idta

З терміна [inner join] походить назва «внутрішнє з'єднання», яку дають цьому типу операцій між двома таблицями. Ми побачимо, що існує також «зовнішнє з'єднання». У внутрішньому з'єднанні порядок таблиць у запиті не впливає на результат: FROM TA inner join TB еквівалентно FROM TB inner join TA.

Попередній запит SQL включає в результуючу таблицю лише ті рядки таблиці TA, на які посилається принаймні один рядок таблиці TB. Таким чином, рядок TA [3, data3] не з’являється у результаті, оскільки на нього не посилається жоден рядок з таблиці TB. Може знадобитися отримати всі рядки таблиці TA, незалежно від того, чи є на них посилання в рядку таблиці TB. У цьому випадку використовується зовнішнє з'єднання між двома таблицями:

SQL> select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA left outer join TB on TA.ID= TB.IDTA

Тут маємо ліве зовнішнє з'єднання («left outer join»). Щоб зрозуміти вираз «FROM TA left outer join TB», треба уявити собі з'єднання, в якому таблиця TA знаходиться ліворуч, а таблиця TB — праворуч. Усі рядки лівої таблиці потрапляють у результат лівого зовнішнього з'єднання, навіть ті, для яких умова з'єднання не виконується. Ця умова з'єднання не обов'язково є обмеженням зовнішнього ключа, хоча це й є найпоширенішим випадком.

У такому порядку:

SQL> select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TB left outer join TA on TA.ID= TB.IDTA

таблиця TB є «лівою» у зовнішньому з’єднанні. Отже, у результаті будуть присутні всі рядки з таблиці TB:

На відміну від внутрішнього з'єднання, порядок таблиць тут має значення. Існують також прямі зовнішні з'єднання:

FROM TA left outer join TB еквівалентно FROM TB right outer join TA: таблиця TA знаходиться ліворуч
FROM TB лівий зовнішній з'єднання з TA еквівалентно FROM TA правому зовнішньому з'єднанню з TB: таблиця TB знаходиться ліворуч

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