Якщо ви власник підприємства, ви, напевно, стикалися з цінністю та потребою в даних у своєму бізнесі. Наявність засобів для зберігання та маніпулювання базами даних підвищує цінність бізнесу.
Бази даних організовані відповідно до певної конвенції та дозволяють структурувати дані у з’єднаннях, що приводить нас до реляційних баз даних, які були прийняті як форма керування даними з 1970-х років. А на сучасному ринку реляційним базам даних віддають перевагу завдяки їхнім можливостям при маніпулюванні даними.
Хоча існує багато доступних реляційних баз даних, за даними Statista станом на січень 2022 року, MySQL потрапила до лідируючих, займаючи друге місце у світі.
У SQL-сервері обмеження — це попередньо визначені правила та обмеження, які застосовуються в одному або кількох стовпцях; вони прив’язані до значень у стовпці та допомагають підтримувати цілісність, точність і надійність даних у вказаних стовпцях.
Простіше кажучи, лише ті дані, які відповідають правилу обмеження, успішно вставляються в стовпець. Операція вставки припиняється, якщо дані не відповідають критеріям.
Ця публікація припускає, що ви стикалися з реляційними базами даних, зокрема – MySQL, і з нетерпінням чекаємо зміцнення своїх знань у цій галузі. Зрештою, я поділюся деякими порадами щодо взаємодії з обмеженнями зовнішнього ключа.
Основні ключові обмеження – підсумок
Таблиця в SQL передбачає один або декілька стовпців, що містять ключові значення, які точно визначають кожен рядок у системах. Стовпець або стовпці під назвою первинний ключ (PK) таблиці виконують роль забезпечення цілісності сутності таблиці. Обмеження первинного ключа гарантують унікальність даних і часто визначаються в стовпці ідентифікації.
Після визначення обмежень первинного ключа для вашої таблиці механізм бази даних автоматично накладає унікальність даних, генеруючи унікальні індекси для кожного з основних стовпців. Первинні ключі пропонують надзвичайну перевагу при використанні в запитах, забезпечуючи швидкий доступ до даних.
Якщо обмеження первинного ключа визначені для кількох стовпців, це називається складеним або складеним первинним ключем. І в цьому випадку кожен стовпець первинного ключа може містити повторювані значення. Однак об’єднані значення з усіх стовпців у первинному ключі мають бути унікальними.
Хорошим прикладом є випадок, коли у вас є таблиця зі стовпцями `id`, `names` і `age`. Коли ви визначаєте його обмеження первинного ключа на комбінацію `id` та `names`, ви можете мати повторювані екземпляри значень `id` або `names`. Проте кожна комбінація має бути унікальною, щоб уникнути дублювання рядків. Отже, у вас можуть бути записи з `id=1` і `name=Walter`, `age-22» і `id=1`, `name=Henry` і `age=27`, але ви не можете мати інші записи з `id=1` і `name=Walter`, оскільки комбінація не є унікальною.
Ось деякі важливі аспекти, які слід знати:
Обмеження зовнішнього ключа – виклад
Зовнішній ключ (FK) передбачає стовпець або комбінацію кількох, які використовуються для створення та зв’язування зв’язку між двома таблицями та керування даними, які зберігаються в таблиці зовнішнього ключа.
Посилання на зовнішній ключ передбачає створення зв’язку між двома таблицями; коли стовпець або стовпці, що містять первинний ключ для іншої таблиці, посилаються на стовпець або стовпці в іншій таблиці.
У сценарії посилання на зовнішній ключ з’єднання створюється між двома таблицями, коли на стовпець або стовпці, що містять первинні ключі в таблиці, посилаються стовпці в іншій.
У практичному випадку ви можете мати таблицю Sales.SalesOrderHeader із зовнішнім ключем, що зв’язується з іншою таблицею Sales.Person, оскільки існує логічний зв’язок між продавцями та замовленнями на продаж.
Тут SalesPersonID у стовпці SalesOrderHeader поєднується зі стовпцем первинного ключа таблиці SalesPerson. Зовнішній ключ таблиці SalesPerson — це стовпець SalesPersonID у SalesOrderHeader.
Цей зв’язок визначає правило: значення SalesPersonID не може бути у вашій таблиці SalesOrderHeader, якщо воно не існує в таблиці SalesPerson.
Таблиця може посилатися на до 253 інших стовпців і таблиць як зовнішні ключі, які також називаються вихідними посиланнями. З 2016 року сервер SQL збільшив кількість таблиць і стовпців, на які можна посилатися в одній таблиці, також відомих як вхідні посилання, з 253 до 10000. Однак це збільшення супроводжується деякими обмеженнями:
Які переваги зовнішніх ключів?
Як зазначалося раніше, обмеження зовнішнього ключа відіграють важливу роль у захисті цілісності та узгодженості даних у реляційній базі даних. Ось розбивка причин, чому обмеження зовнішнього ключа важливі.
Індекси зовнішніх ключових обмежень
Обмеження зовнішнього ключа не створюють автоматично відповідні індекси, як основний. Ви можете вручну створити індекси для обмежень зовнішнього ключа; це вигідно з наступних причин.
- Стовпці зовнішнього ключа часто використовуються в критеріях об’єднання під час об’єднання даних із пов’язаних таблиць у запитах шляхом зіставлення стовпців, прив’язаних до обмеження. Індекси допомагають базі даних знаходити пов’язані дані в сторонній таблиці.
- Якщо ви змінюєте обмеження первинного ключа, вони звіряються з зовнішніми у відповідних таблицях.
Створення індексів не є обов’язковим. Ви все ще можете поєднувати дані з двох таблиць, не вказуючи обмеження первинного та зовнішнього ключів. Однак додавання обмежень зовнішнього ключа оптимізує таблиці та об’єднує їх у запит, який відповідає критеріям використання ключів. Якщо ви змінюєте обмеження первинного ключа, вони звіряються з зовнішніми у пов’язаних.
Поради щодо створення обмежень зовнішнього ключа в 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». У цьому випадку обмеження FOREIGN KEY додається до стовпця «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. НІЯКИХ ДІЙ
Як і в багатьох інших базах даних, правило «БЕЗ ДІЙ» є поведінкою за замовчуванням, коли ви створюєте обмеження зовнішнього ключа. Це означає, що жодних дій не виконується, коли рядок, на який посилається, видаляється або оновлюється.
Обробник бази даних викликає помилку, якщо обмеження зовнішнього ключа порушується. Однак це не рекомендується, оскільки це може призвести до проблем посилальної цілісності, оскільки обмеження зовнішнього ключа має бути виконано. Ось приклад того, як це зробити:
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» є ще одним варіантом для дій «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, якщо стовпець часової позначки є частиною зовнішнього ключа або ключа, на який посилається.
- Якщо ваша таблиця має тригер INSTEAD OF DELETE, ви не можете вказати ON DELETED CASCADE.
- Ви не можете вказати ON UPDATE CASCADE, якщо ваша таблиця має тригер INSTEAD OF UPDATE.
#3. SET 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», якщо відповідний рядок у таблиці «customers» буде видалено або оновлено.
#4. ВСТАНОВИТИ ЗА ЗАМОВЧУВАННЯМ
Тут ви встановлюєте всі значення, які роблять зовнішній ключ типовим за умови, що рядок, на який посилається, у батьківській таблиці оновлено або видалено.
Це обмеження виконується, якщо всі стовпці зовнішнього ключа мають визначення за замовчуванням. Якщо стовпець має значення 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, вона повертається до інших правил обмежень. В інших випадках дія ВИДАЛИТИ може ініціювати комбінацію цих правил, і правило НЕ ДІЇ буде виконано останнім.
Далі перегляньте шпаргалку SQL.