Обмеження (сonstraints) PostgreSQL: exclude, частковий unique, відстрочені обмеження та ін

Цілісність даних легко порушити. Буває так, що в поле price потрапляє значення 0 з-за помилки в коді програми (періодично спливають новини, як у тому чи іншому інет-магазині продавали товари за 0 доларів). Або буває, що видалили юзера з таблиці, але якісь дані про нього залишилися в інших таблицях, і ці дані вилізли в якомусь інтерфейсі.

PostgreSQL, як і будь-яка інша СУБД, вміє робити деякі перевірки при вставці/зміну даних, і цим обов'язково потрібно вміти користуватися. Давайте подивимося, що ми можемо перевіряти:

1. Кастомный підтип через ключове слово DOMAIN
В PostgreSQL ви можете створити свій тип, заснований на якому-небудь int або text з додатковою перевіркою якихось речей:

CREATE DOMAIN us_postal_code AS TEXT
CHECK(
VALUE ~ '^\d{5}$'
OR VALUE ~ '^\d{5}-\d{4}$'
);

Ми створюємо тип us_postal_code, в якому регулярками перевіряємо різні варіанти його написання. Тепер ніхто не зможе туди помилково написати «вулиця Бармалеева», там буде тільки індекс:

CREATE TABLE users (
id integer, 
name text,
email text,
postal_code us_postal_code
) ;

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

2. Check (особливо актуально для перевірки jsonb і hstore)
Вище ми використовували us_postal_code використовували оператор CHECK. Точно такий же можна написати і в конструкції CREATE TABLE.

CREATE TABLE users (
id integer, 
name text,
email text,
postal_code us_postal_code,
CHECK (length(name) >= 1 AND length(name) <= 300)
) ;

Або таблиці з товарами можна поставити check (price > 0), тоді ви не будете продавати ноуты по 0 рублів. Чи можна написати хранимку і використовувати check(superCheckFunction(price)), а в цій хранимке купу логіки перевіряти.

До речі, тип varchar(100) — це теж саме, що і тип text з додатковим check по довжині.
Треба розуміти, що check відбувається при кожному insert або update, тому, якщо у вашу таблицю йде 100500 записів в секунду, то check можливо робити не варто.

Буває важливо обвішати чеками універсальні типи даних, такі як jsonb або hstore, тому що туди можна напхати що завгодно. Можна перевіряти існування якихось ключів в json або що їх значення відповідає тому, що там має бути.

3. Перевірка на унікальність, як проста, так і часткова.
Проста перевірка, що email у різних користувачів повинен бути різний:

CREATE TABLE users (
id integer, 
name text,
email text,
postal_code us_postal_code,
deleted boolean,
UNIQUE(email)
) ;

Проте іноді потрібно перевіряти унікальність не всієї таблиці, а лише, наприклад, у користувачів з певним статусом.

Замість простого UNIQUE ви можете додати такий унікальний індекс:

CREATE UNIQUE INDEX users_unique_idx ON users(email) WHERE deleted = false;

Тоді унікальність email буде перевірятися лише у неудаленных юзерів. У where можна вставляти будь-які умови.

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

4. EXCLUDE
З допомогою оператора EXCLUDE можна зробити ще один вид унікальності. Справа в тому, що в посгресе безліч типів даних, як вбудованих, так і доданих через розширення. Наприклад, є тип даних ip4r, з його допомогою можна зберігати діапазон ip-адрес в одному полі.

І, припустимо, треба в таблиці зберігати непересічні діапазони. Взагалі, перевірити, чи перетинаються два діапазону можна за допомогою оператора &&, наприклад SELECT '127.0.0.0/24' && '127.0.0.1/32' поверне true.

У підсумку робимо просто:

CREATE TABLE ip_ranges (
ip_range ip4r,
EXCLUDE USING gist (ip_range WITH &&)
);

І тоді при вставці/апдейте postgres буде дивитися кожну рядок, не перетинається вона з вставляється (тобто не повертає використання оператора && істину). Завдяки gist індексу ця перевірка дуже швидка.

5. NOT NULL
Тут все зрозуміло, колонка не може приймати значення NULL. Найчастіше (але необов'язково) йде у зв'язці з DEFAULT.

Наприклад:

CREATE TABLE users (
id integer, 
name text NOT NULL,
email text NOT NULL,
postal_code us_postal_code,
is_married BOOLEAN NOT NULL DEFAULT true,
UNIQUE(email)
) ;

При додаванні нової колонки з not null в існуючу таблицю треба бути обережним. Справа в тому, що звичайну колонку, де допустимо null, PostgreSQL додає миттєво, навіть якщо таблиця дуже велика, наприклад, десятки мільйонів рядків. Тому що йому не треба фізично змінювати дані, що лежать на диску, null postgres не займають місця. Однак якщо ви додасте колонку name text not null default 'Вася', то посгрес за фактом полізе робити update кожного рядка, і це може зайняти багато часу, що може бути неприпустимо в деяких ситуаціях.

Тому часто у величезні таблиці такі колонки додаються в два етапи, тобто спочатку пачками заповнюють дані колонки, і тільки потім ставлять їй not null.

6. Primary key, тобто первинний ключ
Раз це первинний ключ, то воно має бути унікальним і не може бути порожнім. Загалом, в PostgreSQL PRIMARY KEY працює як комбінація UNIQUE і NOT NULL.

В інших базах даних PRIMARY KEY робить ще й інші речі, наприклад, якщо не помиляюся, в MySQL (Innodb), дані ще і автоматично кластеризуются навколо PK для прискорення доступу по цьому полю. (У посгресе, до речі, теж так можна зробити, але вручну командою CLUSTER. Але зазвичай в цьому немає необхідності)

7. FOREIGN KEY
Наприклад, у вас є таблиця

CREATE TABLE items (
id bigint PRIMARY KEY,
name varhar(1000),
status_id int
);

і таблиця зі статусами

CREATE TABLE status_dictionary (
id int PRIMARY KEY,
status_name varchar(100)
);

Ви можете вказати базі, що колонка status_id відповідає Id з таблиці status_dictionary. Наприклад, так:

CREATE TABLE items (
id bigint PRIMARY KEY,
name varhar(1000),
status_id int REFERENCES status_dictionary(id)
);

Тепер ви зможете в status_id записати тільки null або Id з таблиці status_dictionaries, і більше нічого.

Також можна це робити з двох полів:

FOREIGN KEY (a,b) REFERENCES other_table(x,y);

При вставці знову ж таки є певний оверхед, тому що при вставлення СУБД змушена лочить досить багато речей. Тому при (дуже) інтенсивної вставці можливо не варто зловживати використанням Foreign key

8. DEFERRABLE
Якщо для продуктивності треба відкласти перевірку констрейнтов, констрейнты можна позначити ключовим словом DEFERRABLE.

Вони бувають різних видів, наприклад, якщо ви зробите UNIQUE(email) DEFERRABLE INITIALLY DEFERRED, то всередині транзакції можна написати

SET CONSTRAINTS ALL DEFERRED

І тоді всі перевірки будуть Відкладені і за фактом відбудуться тільки перед словом commit
Це спрацює для UNIQUE, PRIMARY KEY і REFERENCES, але не спрацює NOT NULL CHECK.
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.