Як створити обмеження зовнішнього ключа в SQL

Автор

Важливість обмежень в базах даних MySQL

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

Хоча існує багато реляційних баз даних, MySQL займає провідні позиції. Згідно з даними Statista на січень 2022 року, MySQL посідає друге місце у світі за популярністю.

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

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

Обмеження первинного ключа: короткий огляд

Таблиця в SQL складається з одного або кількох стовпців, що містять ключові значення, які однозначно ідентифікують кожен рядок. Стовпець або набір стовпців, позначений як первинний ключ (PK), забезпечує цілісність даних в таблиці. Обмеження первинного ключа гарантує унікальність даних та зазвичай визначається для стовпця ідентифікації.

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

Приклад: У таблиці зі стовпцями `id`, `names` та `age` можна створити складений первинний ключ на основі комбінації `id` та `names`. Це дозволить мати повторення значень `id` або `names`, але кожна комбінація `id` та `name` має бути унікальною. Наприклад, можна мати записи з `id=1`, `name=Walter` і `age=22`, а також `id=1`, `name=Henry` і `age=27`, але не можна мати інший запис з `id=1` і `name=Walter`, оскільки така комбінація вже існує.

Важливі аспекти, які варто пам’ятати:

  • Кожна таблиця може мати лише одне обмеження первинного ключа.
  • Первинні ключі не можуть містити більше 16 стовпців і мати максимальну довжину 900 символів.
  • Індекси, створені первинними ключами, можуть збільшити індексацію в таблиці. Кількість кластеризованих індексів в таблиці не може перевищувати 1, а кількість некластеризованих обмежена 999.
  • Якщо кластеризований або некластеризований індекси не вказані, то кластеризований приймається автоматично.
  • Всі стовпці, визначені в обмеженні первинного ключа, повинні бути визначені як NOT NULL. Якщо це не так, то всі стовпці, що входять до обмеження, автоматично отримують налаштування NOT NULL.
  • Якщо первинні ключі визначені в користувацькому типі стовпця CLR, реалізація цього типу повинна підтримувати двійкове впорядкування.

Обмеження зовнішнього ключа: детальний опис

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

Наприклад, ви можете мати таблицю `Sales.SalesOrderHeader` із зовнішнім ключем, який пов’язаний з таблицею `Sales.Person`, оскільки існує логічний зв’язок між продавцями і замовленнями продажів. В цьому випадку, `SalesPersonID` у стовпці `SalesOrderHeader` поєднується зі стовпцем первинного ключа таблиці `SalesPerson`. Зовнішній ключ таблиці `SalesPerson` – це стовпець `SalesPersonID` в `SalesOrderHeader`.

Цей зв’язок визначає правило, що значення `SalesPersonID` в `SalesOrderHeader` повинні існувати в таблиці `SalesPerson`.

Таблиця може посилатися на до 253 інших стовпців і таблиць як зовнішні ключі (вихідні посилання). З 2016 року SQL Server збільшив кількість таблиць і стовпців, на які можна посилатися (вхідні посилання), з 253 до 10000. Однак існують певні обмеження:

  • Посилання зовнішнього ключа понад 253 доступні лише для операцій DELETE DML. Операції MERGE та UPDATE не підтримуються.
  • Таблиці із посиланнями зовнішніх ключів не можуть мати більше 253 посилань зовнішнього ключа.
  • Для таблиць зберігання стовпців, таблиць, оптимізованих для пам’яті, та розділених таблиць посилання на зовнішні ключі обмежені 253.

Які переваги зовнішніх ключів?

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

  • Посилальна цілісність: Обмеження зовнішнього ключа гарантують, що кожен запис дочірньої таблиці має відповідний запис у батьківській таблиці, підтримуючи узгодженість даних в обох таблицях.
  • Запобігання втраті записів: Якщо ви видаляєте батьківську таблицю, обмеження зовнішнього ключа гарантує, що пов’язана дочірня таблиця також буде видалена, запобігаючи випадкам втрати даних та невідповідності.
  • Покращена продуктивність: Обмеження зовнішнього ключа покращують продуктивність запитів, дозволяючи системі управління базою даних оптимізувати запити на основі зв’язків між таблицями.

Індекси обмежень зовнішнього ключа

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

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

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

Поради щодо створення обмежень зовнішнього ключа в SQL

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

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

Створення зовнішнього ключа під час створення таблиці

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


 CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
 );

Цей код створює таблицю `orders` з первинним ключем `order_id`, стовпцем `customer_id` та `order_date`. Обмеження зовнішнього ключа додається до `customer_id` та посилається на `customer_id` у таблиці `customers`.

Створення зовнішнього ключа після створення таблиці

Якщо ви вже створили таблицю та хочете додати обмеження зовнішнього ключа, використовуйте оператор `ALTER TABLE`, як показано нижче:


 ALTER TABLE orders
 ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

У цьому випадку ми додали обмеження зовнішнього ключа для `customer_id` в таблиці `orders`, посилаючись на `customer_id` в таблиці `customers`.

Створення зовнішнього ключа без перевірки наявних даних

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


 ALTER TABLE orders
 ADD CONSTRAINT fk_orders_customers
 FOREIGN KEY (customer_id)
 REFERENCES customers(customer_id)
 NOT VALIDATE;

Команда `NOT VALIDATE` вказує базі даних не перевіряти наявні дані. Це корисно в ситуаціях, коли ви маєте великий обсяг даних і хочете пришвидшити процес додавання обмеження.

Створення зовнішнього ключа через DELETE/UPDATE

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

#1. NO ACTION

Правило `NO ACTION` є поведінкою за замовчуванням, коли створюється обмеження зовнішнього ключа. Це означає, що жодних дій не виконується, коли рядок, на який посилається зовнішній ключ, видаляється або оновлюється.

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


 ALTER TABLE orders
 ADD CONSTRAINT fk_orders_customers
 FOREIGN KEY (customer_id)
 REFERENCES customers(customer_id)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION;

#2. CASCADE

Правило `CASCADE` для `ON DELETE` і `ON UPDATE` означає, що при оновленні або видаленні рядка в батьківській таблиці, рядки, на які посилаються, також будуть оновлені або видалені. Цей метод є ефективним для підтримки посилальної цілісності. Приклад:


 ALTER TABLE orders
 ADD CONSTRAINT fk_orders_customers
 FOREIGN KEY (customer_id)
 REFERENCES customers(customer_id)
 ON DELETE CASCADE
 ON UPDATE CASCADE;

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

Існують деякі обмеження для використання `CASCADE`:

  • Не можна використовувати `CASCADE`, якщо стовпець часової мітки є частиною зовнішнього ключа або ключа, на який посилаються.
  • Якщо таблиця має тригер `INSTEAD OF DELETE`, не можна вказати `ON DELETE CASCADE`.
  • Якщо таблиця має тригер `INSTEAD OF UPDATE`, не можна вказати `ON UPDATE CASCADE`.

#3. SET NULL

При видаленні або оновленні рядка в батьківській таблиці, усі значення, що утворюють зовнішній ключ, встановлюються в `NULL`. Для використання цього правила, стовпці зовнішнього ключа повинні допускати значення `NULL`, і його не можна використовувати в таблицях з тригерами `INSTEAD OF UPDATE`. Приклад:


 ALTER TABLE orders
 ADD CONSTRAINT fk_orders_customers
 FOREIGN KEY (customer_id)
 REFERENCES customers(customer_id)
 ON DELETE SET NULL
 ON UPDATE SET NULL

У цьому прикладі, значення стовпця `customer_id` в таблиці `orders` будуть встановлені в `NULL`, якщо відповідний рядок в таблиці `customers` буде видалено або оновлено.

#4. SET DEFAULT

Тут всі значення зовнішнього ключа встановлюються в типове значення, якщо рядок, на який він посилається, у батьківській таблиці оновлюється або видаляється. Це обмеження працює, якщо всі стовпці зовнішнього ключа мають значення за замовчуванням. Якщо стовпець допускає `NULL`, то його значення за замовчуванням буде `NULL`. Цей параметр не можна використовувати для таблиць з тригерами `INSTEAD OF UPDATE`. Приклад:


 ALTER TABLE orders
 ADD CONSTRAINT fk_orders_customers
 FOREIGN KEY (customer_id)
 REFERENCES customers(customer_id)
 ON DELETE SET DEFAULT
 ON UPDATE SET DEFAULT;

В цьому прикладі, для стовпця `customer_id` в таблиці `orders` буде встановлено типове значення, коли відповідний рядок в таблиці `customers` видаляється або оновлюється.

Заключні слова

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

Ви також можете комбінувати різні обмеження в таблицях з посиланнями. Наприклад, обмеження `CASCADE`, `SET NULL`, `SET DEFAULT` і `NO ACTION` можна використовувати одночасно.

Якщо в таблиці зустрічається `NO ACTION`, то вона повертається до інших правил обмеження. У випадку з `DELETE`, комбінація цих правил може бути ініційована, і правило `NO ACTION` виконується останнім.

Для подальшого вивчення, ви можете переглянути шпаргалку SQL.