CONSTRAINT: цілісність бази даних

1. Контроль цілісності бази даних

Ще одна важлива річ, яку потрібно знати про бази даних, — це констрейни (CONSTRAINS). За допомогою констрейнів ти можеш контролювати зміни даних у своїх таблицях та підтримувати їхню цілісність та узгодженість.

Що таке узгодженість даних, коли ми говоримо про базу даних?

Давай візьмемо наш інтернет-магазин з таблицями employee, product та task. Ми вже знаємо, що в таблиці task можуть бути завдання, не призначені ні на кого: employee_id у таких рядках дорівнює NULL.

А ось що буде, якщо в таблиці task буде запис з employee_id рівним, скажімо, 115? Адже ми не маємо такого співробітника. У нас немає співробітника з id = 115 у таблиці employee. Водночас посилання на співробітника з таким id є в таблиці task. Це і є прикладом неузгодженості даних.

То як нам узгодити ці дані? Ідеально було б зробити так, щоб SQL-сервер за будь-якої зміни даних контролював усі ці нюанси. І така можливість є, називається вона FOREIGN_KEY.

Якщо якась колонка у вашій таблиці містить не просто цифри, а id рядків з іншої таблиці, це можна вказати явно.

2. Додавання FOREIGN KEY

Такий ключ можна додавати в таблицю як на етапі її створення, так і після, за допомогою ALTER TABLE. Формат принципово не відрізняється. Ми наведемо обидва варіанти.

Загальний вигляд такого ключа/правила має вигляд:

FOREIGN KEY (колонка)
  REFERENCES таблиця(колонка)

Давай додамо до таблиці task цей ключ/правило, щоб гарантувати, що всі employee_id з таблиці посилаються на існуючий запис у таблиці employee. Виглядатиме цей скрипт так:

ALTER TABLE task
      ADD FOREIGN KEY (employee_id)
  REFERENCES employee(id)

А якщо б ми вирішили додати це правило в момент створення таблиці task, код виглядав би так:

CREATE TABLE task (
      id INT,
      name VARCHAR(100),
      employee_id INT,
      deadline DATE,
 
      PRIMARY KEY (id),
  FOREIGN KEY (employee_id)
REFERENCES employee(id)
);

До речі, бувають ситуації, коли рядок, на який ми посилаємося, має унікальний складовий ключ: наприклад “ПІБ та рік народження” або “productCategoryId та productId”. Тоді FOREIGN KEY можна записати таким чином:

FOREIGN KEY (наша_колонка1, наша_колонка2)
  REFERENCES таблиця(їх_колонка1, їх_колонка2)

3. FOREIGN KEY та зміна даних

А тепер уяви ситуацію, коли в таблиці employee ми вирішили оновити деякі дані, і в нас змінився id співробітника. Що при цьому станеться з даними таблиці task? Правильно: вони стануть неактуальними, і цілісність нашої бази даних порушиться.

Щоб цього уникнути, можна вказати SQL-серверу, щоб він під час зміни id у таблиці employee змінював би й employee_id всіх рядків у всіх таблицях, які посилаються на це конкретне id, що змінилося.

Такі сценарії називаються OnUpdate та OnDelete. Що робити у разі зміни id-запису та що робити у разі видалення запису?

З видаленням взагалі не все так однозначно. Якщо у тебе є залежні об'єкти, представлені рядками в БД, які посилаються один на одного, то можливі різноманітні сценарії поведінки під час видалення одного об'єкта.

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

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

Ось як ми можемо описати ці сценарії за допомогою FOREIGN KEY. Загальний вигляд такого ключа/правила має вигляд:

FOREIGN KEY (колонка)
  REFERENCES таблиця(колонка)
 [ON DELETE reference_option]
 [ON UPDATE reference_option]

Що робити у разі видалення (ON DELETE) чи зміни (ON UPDATE) записів? Загалом може бути 5 варіантів дії SQL-сервера в кожній з цих ситуацій:

Ось яким чином ми могли би змінити нашу таблицю task:

ALTER TABLE task
  ADD FOREIGN KEY (employee_id)
  REFERENCES employee(id)
  ON UPDATE CASCADE
  ON DELETE RESTRICT;

Що тут написано:

ON UPDATE CASCADE: якщо змінюється ключ id у таблиці employee, то так само поміняти employee_id у таблиці task, яка на нього посилається.

ON DELETE RESTRICT: якщо видаляється рядок з таблиці employee і є посилання з таблиці task, то заборонити видалення рядка з таблиці employee.