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

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

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

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

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

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

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

Додавання 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)
);

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

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

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-сервера в кожній з цих ситуацій:

# reference_option Пояснення
1 RESTRICT Заборонити дію, якщо знайдено посилання на рядок
2 CASCADE Змінити id у залежних рядках
3 SET NULL Встановити id у залежних рядках у NULL
4 NO ACTION Нічого не робити
5 SET DEFAULT x Встановити id у залежних стоках значення x

Ось як би ми могли змінити нашу таблицю 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.

Java-університет