Пошук уроків, статей та іншого контенту
Забороняйте пропущені значення, контролюйте унікальність і перевіряйте дані за умовами.
Обмеження таблиці (constraints) перевіряють дані під час вставлення або зміни рядків. Вони не дозволяють зберігати значення, які суперечать правилам предметної області.
У PostgreSQL для цього часто використовують:
NOT NULL — значення обов’язкове;
UNIQUE — значення не повинні повторюватися;
CHECK — значення має відповідати умові.
Обмеження перевіряються автоматично під час виконання INSERT та UPDATE.
NOT NULL: заборона пропущених значеньЗа замовчуванням стовпець може містити NULL. Це означає, що значення невідоме або не задане.
Якщо значення обов’язкове, додайте NOT NULL:
CREATE TABLE users (
id integer,
username text NOT NULL,
email text NOT NULL
);Тепер PostgreSQL не дозволить додати користувача без імені або електронної адреси:
INSERT INTO users (id, username, email)
VALUES (1, 'anna', 'anna@example.com');Цей запит завершиться помилкою:
INSERT INTO users (id, username, email)
VALUES (2, NULL, 'someone@example.com');NOT NULL перевіряє лише те, що значення не є NULL. Порожній рядок — це не NULL:
INSERT INTO users (id, username, email)
VALUES (3, '', 'empty@example.com');Такий запит дозволений, тому що '' — звичайний текстовий рядок. Якщо порожні рядки теж потрібно заборонити, використайте CHECK.
UNIQUE: контроль унікальностіОбмеження UNIQUE не дозволяє зберігати однакові значення в одному стовпці:
CREATE TABLE customers (
id integer,
email text UNIQUE
);Перший запис буде успішним:
INSERT INTO customers (id, email)
VALUES (1, 'anna@example.com');Повторення тієї самої адреси викличе помилку:
INSERT INTO customers (id, email)
VALUES (2, 'anna@example.com');UNIQUE і NOT NULL вирішують різні завдання:
UNIQUE забороняє дублікати;
NOT NULL забороняє пропущене значення.
Якщо адреса має бути і обов’язковою, і унікальною, потрібні обидва обмеження:
CREATE TABLE accounts (
id integer,
email text NOT NULL UNIQUE
);NULL і UNIQUEУ PostgreSQL кілька рядків із NULL зазвичай дозволені в стовпці з UNIQUE:
CREATE TABLE profiles (
id integer,
phone text UNIQUE
);
INSERT INTO profiles (id, phone) VALUES (1, NULL);
INSERT INTO profiles (id, phone) VALUES (2, NULL);Це відбувається тому, що NULL означає невідоме значення, а два невідомі значення не вважаються однаковими.
Якщо значення має бути обов’язковим, поєднуйте обмеження:
CREATE TABLE profiles (
id integer,
phone text NOT NULL UNIQUE
);Іноді унікальною має бути не одна колонка, а комбінація колонок. Наприклад, один і той самий товар може мати різну ціну в різних магазинах, але пара store_id і product_id не повинна повторюватися:
CREATE TABLE store_products (
store_id integer NOT NULL,
product_id integer NOT NULL,
price numeric(10, 2) NOT NULL,
CONSTRAINT store_products_unique
UNIQUE (store_id, product_id)
);У цьому випадку дозволені такі рядки:
store_id | product_id
----------+-----------
1 | 10
1 | 20
2 | 10А повторна пара 1 і 10 буде заборонена.
CHECK: перевірка за умовоюCHECK дозволяє описати умову, якій має відповідати значення:
CREATE TABLE products (
name text NOT NULL,
price numeric(10, 2) NOT NULL CHECK (price >= 0),
stock integer NOT NULL CHECK (stock >= 0)
);Дозволений запис:
INSERT INTO products (name, price, stock)
VALUES ('Keyboard', 2500.00, 15);Цей запис викличе помилку, оскільки ціна від’ємна:
INSERT INTO products (name, price, stock)
VALUES ('Mouse', -100.00, 5);За допомогою CHECK можна перевірити довжину або заборонити порожній рядок:
CREATE TABLE categories (
name text NOT NULL CHECK (length(trim(name)) > 0)
);Умова length(trim(name)) > 0 означає:
видалити пробіли на початку та в кінці;
перевірити, що після цього залишився хоча б один символ.
Значення ' ' не пройде таку перевірку.
CHECK може порівнювати кілька стовпців одного рядка:
CREATE TABLE events (
title text NOT NULL,
starts_at timestamp NOT NULL,
ends_at timestamp NOT NULL,
CONSTRAINT event_dates_valid
CHECK (ends_at > starts_at)
);Подія не може завершуватися раніше або одночасно з початком.
PostgreSQL може автоматично створювати імена для обмежень, але явні імена роблять помилки зрозумілішими:
CREATE TABLE employees (
username text NOT NULL,
email text NOT NULL,
CONSTRAINT employees_username_unique UNIQUE (username),
CONSTRAINT employees_email_unique UNIQUE (email),
CONSTRAINT employees_email_format CHECK (position('@' IN email) > 1)
);Імена допомагають зрозуміти, яке саме правило порушено, а також дають змогу звертатися до обмежень під час їх зміни або видалення.
Нижче наведено самодостатній приклад таблиці користувачів:
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id integer NOT NULL,
username text NOT NULL,
email text NOT NULL,
age integer NOT NULL,
status text NOT NULL DEFAULT 'active',
CONSTRAINT users_id_unique UNIQUE (id),
CONSTRAINT users_username_unique UNIQUE (username),
CONSTRAINT users_email_unique UNIQUE (email),
CONSTRAINT users_age_valid CHECK (age >= 18),
CONSTRAINT users_status_valid CHECK (status IN ('active', 'blocked'))
);
INSERT INTO users (id, username, email, age)
VALUES
(1, 'anna', 'anna@example.com', 25),
(2, 'bohdan', 'bohdan@example.com', 31);
SELECT id, username, email, age, status
FROM users;У цьому прикладі:
id, username, email, age і status не можуть бути NULL;
id, username та email не можуть повторюватися;
вік має бути не меншим за 18;
status може мати лише значення 'active' або 'blocked';
якщо status не вказати, буде використано 'active'.
Наступні запити порушують обмеження:
-- username має бути унікальним
INSERT INTO users (id, username, email, age)
VALUES (3, 'anna', 'new@example.com', 22);
-- age не відповідає умові CHECK
INSERT INTO users (id, username, email, age)
VALUES (3, 'olha', 'olha@example.com', 16);
-- email не може бути NULL через NOT NULL
INSERT INTO users (id, username, email, age)
VALUES (3, 'oleh', NULL, 28);
-- status не входить до дозволеного списку
INSERT INTO users (id, username, email, age, status)
VALUES (3, 'ihor', 'ihor@example.com', 28, 'unknown');Обмеження можна додати після створення таблиці за допомогою ALTER TABLE:
ALTER TABLE users
ADD CONSTRAINT users_username_length
CHECK (length(username) >= 3);Після цього імена користувачів із менш ніж трьома символами будуть заборонені.
Для NOT NULL використовується окрема форма:
ALTER TABLE users
ALTER COLUMN email SET NOT NULL;Перед додаванням NOT NULL у таблиці не повинно бути рядків із NULL у цьому стовпці. Інакше PostgreSQL не зможе застосувати обмеження.
Обмеження можна видалити за його іменем:
ALTER TABLE users
DROP CONSTRAINT users_username_length;Видаляти обмеження слід обережно: після цього база даних більше не перевірятиме відповідне правило.
NULL і порожній рядокNULL:
NULLПорожній рядок:
''NOT NULL забороняє лише NULL, але не порожні рядки. Якщо порожні рядки не мають сенсу, додайте CHECK:
name text NOT NULL CHECK (length(trim(name)) > 0)UNIQUE для обов’язкового поляТаке визначення дозволяє кілька NULL:
email text UNIQUEДля обов’язкової унікальної адреси використовуйте:
email text NOT NULL UNIQUEПеревірка у frontend або backend корисна, але не замінює обмеження бази даних. Дані можуть потрапити до PostgreSQL через інший сервіс, SQL-клієнт або міграцію.
Правила, які мають завжди виконуватися для таблиці, варто закріплювати на рівні самої бази даних.
CHECK до значень із NULLЯкщо стовпець допускає NULL, умова CHECK може не заборонити пропущене значення. Для обов’язкового значення поєднуйте CHECK із NOT NULL:
price numeric NOT NULL CHECK (price >= 0)NOT NULL забороняє пропущені значення.
UNIQUE забороняє дублікати.
CHECK перевіряє значення за логічною умовою.
Для обов’язкового унікального поля потрібні NOT NULL і UNIQUE.
UNIQUE може охоплювати комбінацію кількох стовпців.
CHECK може перевіряти як один стовпець, так і взаємозв’язок кількох стовпців.
Явні імена обмежень спрощують читання схеми та подальші зміни.