Увійти Реєстрація
Вакансії
Вакансії Laravel Компанії Статистика
Підготовка
Як підготуватися Співбесіди Тестування Документація
Блог
Всі статті Серії
Екосистема
Пакети Ресурси Проєкти Інструменти Події Відео
Інше
Про нас Реклама

Як забезпечити складні правила цілісності в базі: відкладені обмеження й constraint-тригери?

Звичайні обмеження (NOT NULL, CHECK, UNIQUE, FOREIGN KEY) перевіряються одразу після кожного оператора. Але деякі правила за природою тимчасово порушуються посеред транзакції.

Приклад 1 - обмін позиціями. Колонка position унікальна, і треба поміняти місцями два записи:

UPDATE steps SET position = 2 WHERE id = 1;   -- конфлікт: позиція 2 вже зайнята
UPDATE steps SET position = 1 WHERE id = 2;

Відкладене обмеження перевіряється в момент коміту:

ALTER TABLE steps ADD CONSTRAINT steps_position_unique
UNIQUE (list_id, position) DEFERRABLE INITIALLY IMMEDIATE;

BEGIN;
SET CONSTRAINTS steps_position_unique DEFERRED;
UPDATE steps SET position = 2 WHERE id = 1;
UPDATE steps SET position = 1 WHERE id = 2;
COMMIT;   -- перевірка тут

DEFERRABLE можна задати для UNIQUE, PRIMARY KEY, FOREIGN KEY і EXCLUDE, але не для CHECK і NOT NULL.

Приклад 2 - правило між рядками. «Сума часток власників компанії дорівнює 100%» - CHECK бачить лише один рядок. Тут допомагає constraint-тригер: тригер AFTER, який можна відкласти до коміту:

CREATE CONSTRAINT TRIGGER shares_total_check
AFTER INSERT OR UPDATE OR DELETE ON shares
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE FUNCTION check_shares_total();

Функція рахує суму для компанії й кидає виняток, якщо вона не 100. Транзакція може вставити чотири рядки по 25% - перевірка відбудеться після останнього.

Приклад 3 - заборона перетину інтервалів: EXCLUDE USING gist (room_id WITH =, during WITH &&) - вбудоване рішення без тригерів.

Ризики правил у тригерах:

  • конкуренція: дві паралельні транзакції можуть кожна побачити суму 75% без змін іншої й обидві закомітитися - разом порушивши правило. Потрібне блокування батьківського рядка (SELECT ... FROM companies WHERE id = ? FOR UPDATE) чи рівень SERIALIZABLE;
  • продуктивність: відкладені рядкові тригери накопичуються в пам'яті до коміту - масова операція може стати дуже дорогою;
  • повідомлення про помилку приходить при коміті, а не на рядку, що порушив правило, - застосунку важче показати користувачу зрозумілу причину.

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

Докладніше в документації: CREATE TRIGGER

Схожі питання