Пошук уроків, статей та іншого контенту
Застосуйте обмеження таблиць і стовпців, щоб база даних відхиляла некоректні або суперечливі дані.
Обмеження цілісності — це правила, які база даних перевіряє під час додавання або зміни даних.
Вони захищають таблиці від:
порожніх значень там, де вони заборонені;
повторення унікальних значень;
посилань на неіснуючі записи;
значень, що не відповідають заданій умові;
суперечностей між пов’язаними таблицями.
Обмеження важливо задавати саме в базі даних, а не покладатися лише на перевірки в застосунку. Дані можуть надходити з різних програм, скриптів або адміністративних запитів, але правила бази даних діятимуть для всіх.
NOT NULLОбмеження NOT NULL забороняє зберігати в стовпці значення NULL.
NULL означає «значення невідоме або відсутнє». Це не те саме, що порожній рядок '' або число 0.
CREATE TABLE users (
id integer,
username text NOT NULL
);
-- Коректно
INSERT INTO users (id, username)
VALUES (1, 'olena');
-- Помилка: username не може бути NULL
INSERT INTO users (id, username)
VALUES (2, NULL);NOT NULL доречно використовувати для обов’язкових полів:
імені;
електронної пошти;
дати створення;
ідентифікатора пов’язаного запису.
Якщо поле може бути відсутнім за правилами предметної області, NOT NULL для нього не потрібен.
PRIMARY KEYПервинний ключ (PRIMARY KEY) однозначно ідентифікує рядок у таблиці.
Первинний ключ:
має бути унікальним;
не може містити NULL;
у таблиці може бути лише один первинний ключ.
Найчастіше первинним ключем є стовпець id.
CREATE TABLE categories (
id integer PRIMARY KEY,
name text NOT NULL
);
INSERT INTO categories (id, name)
VALUES (1, 'Книги');
-- Помилка: id 1 уже використовується
INSERT INTO categories (id, name)
VALUES (1, 'Фільми');
-- Помилка: первинний ключ не може бути NULL
INSERT INTO categories (id, name)
VALUES (NULL, 'Музика');Обмеження можна записати й окремо, використовуючи ім’я:
CREATE TABLE products (
id integer,
name text NOT NULL,
CONSTRAINT products_pkey PRIMARY KEY (id)
);Іменовані обмеження полегшують читання схеми та повідомлень про помилки.
UNIQUEUNIQUE забороняє повторення значень у стовпці або наборі стовпців.
Наприклад, електронна пошта користувача має бути унікальною:
CREATE TABLE accounts (
id integer PRIMARY KEY,
email text NOT NULL UNIQUE
);
INSERT INTO accounts (id, email)
VALUES (1, 'anna@example.com');
-- Помилка: така електронна пошта вже існує
INSERT INTO accounts (id, email)
VALUES (2, 'anna@example.com');Іменований варіант:
CREATE TABLE coupons (
id integer PRIMARY KEY,
code text NOT NULL,
CONSTRAINT coupons_code_unique UNIQUE (code)
);Іноді унікальним має бути не кожне поле окремо, а їхня комбінація.
Наприклад, один і той самий товар може бути доданий до замовлення лише один раз:
CREATE TABLE order_items (
order_id integer NOT NULL,
product_id integer NOT NULL,
quantity integer NOT NULL,
CONSTRAINT order_items_unique_product
UNIQUE (order_id, product_id)
);Це дозволяє повторити order_id у різних рядках і product_id у різних замовленнях, але забороняє однакову пару (order_id, product_id).
NULL і UNIQUEУ PostgreSQL кілька рядків із NULL у стовпці UNIQUE зазвичай дозволені, оскільки NULL не вважається рівним іншому NULL.
CREATE TABLE profiles (
id integer PRIMARY KEY,
phone text UNIQUE
);
-- Обидва рядки коректні: phone має значення NULL
INSERT INTO profiles (id, phone)
VALUES (1, NULL), (2, NULL);Якщо значення має бути і заповненим, і унікальним, використовуйте обидва обмеження:
CREATE TABLE members (
id integer PRIMARY KEY,
email text NOT NULL UNIQUE
);FOREIGN KEYЗовнішній ключ (FOREIGN KEY) створює зв’язок між таблицями.
Він гарантує, що значення в дочірній таблиці посилається на наявний рядок батьківської таблиці.
CREATE TABLE departments (
id integer PRIMARY KEY,
name text NOT NULL UNIQUE
);
CREATE TABLE employees (
id integer PRIMARY KEY,
name text NOT NULL,
department_id integer NOT NULL,
CONSTRAINT employees_department_fk
FOREIGN KEY (department_id)
REFERENCES departments (id)
);
INSERT INTO departments (id, name)
VALUES (1, 'Розробка');
-- Коректно: відділ із таким id існує
INSERT INTO employees (id, name, department_id)
VALUES (1, 'Олег', 1);
-- Помилка: відділу з id 99 не існує
INSERT INTO employees (id, name, department_id)
VALUES (2, 'Ірина', 99);Таблицю, на яку посилаються, потрібно створити першою. Так само спочатку додають батьківські записи, а потім дочірні.
FOREIGN KEY без NOT NULL дозволяє NULL:
CREATE TABLE tasks (
id integer PRIMARY KEY,
title text NOT NULL,
employee_id integer,
FOREIGN KEY (employee_id) REFERENCES employees (id)
);У цьому прикладі завдання може бути не призначене працівнику. Якщо кожне завдання обов’язково повинно мати виконавця, потрібно додати NOT NULL.
CHECKCHECK перевіряє логічну умову для кожного рядка.
CREATE TABLE products (
id integer PRIMARY KEY,
name text NOT NULL,
price numeric(10, 2) NOT NULL,
stock integer NOT NULL,
CONSTRAINT products_price_check CHECK (price >= 0),
CONSTRAINT products_stock_check CHECK (stock >= 0)
);
-- Коректно
INSERT INTO products (id, name, price, stock)
VALUES (1, 'Клавіатура', 1200.00, 15);
-- Помилка: ціна не може бути від’ємною
INSERT INTO products (id, name, price, stock)
VALUES (2, 'Миша', -50.00, 10);Можна перевіряти складніші умови:
CREATE TABLE discounts (
id integer PRIMARY KEY,
name text NOT NULL,
percent numeric(5, 2) NOT NULL,
CONSTRAINT discounts_percent_check
CHECK (percent >= 0 AND percent <= 100)
);Важливо: умова CHECK не вважається порушеною, якщо її результат — TRUE або NULL. Тому для обов’язкового значення часто потрібні обидва обмеження:
CREATE TABLE ratings (
id integer PRIMARY KEY,
value integer NOT NULL CHECK (value BETWEEN 1 AND 5)
);NOT NULL забороняє відсутнє значення, а CHECK обмежує діапазон допустимих значень.
DEFAULTDEFAULT задає значення, яке PostgreSQL використає, якщо під час вставки стовпець не вказали.
CREATE TABLE articles (
id integer PRIMARY KEY,
title text NOT NULL,
is_published boolean NOT NULL DEFAULT false,
created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO articles (id, title)
VALUES (1, 'Вступ до SQL');
SELECT id, title, is_published, created_at
FROM articles;Для цього рядка PostgreSQL автоматично встановить:
is_published у false;
created_at у поточний час.
DEFAULT не забороняє явно передати інше значення:
INSERT INTO articles (id, title, is_published)
VALUES (2, 'Обмеження цілісності', true);DEFAULT також не замінює NOT NULL. Якщо стовпець оголошено як NOT NULL, передавання явного NULL усе одно спричинить помилку.
Нижче наведено самодостатній приклад із кількома типами обмежень.
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
id integer PRIMARY KEY,
email text NOT NULL,
name text NOT NULL,
registered_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT customers_email_unique UNIQUE (email),
CONSTRAINT customers_name_check CHECK (length(name) >= 2)
);
CREATE TABLE orders (
id integer PRIMARY KEY,
customer_id integer NOT NULL,
total numeric(10, 2) NOT NULL DEFAULT 0,
status text NOT NULL DEFAULT 'new',
created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id)
REFERENCES customers (id),
CONSTRAINT orders_total_check
CHECK (total >= 0),
CONSTRAINT orders_status_check
CHECK (status IN ('new', 'paid', 'cancelled'))
);
INSERT INTO customers (id, email, name)
VALUES
(1, 'olha@example.com', 'Ольга'),
(2, 'taras@example.com', 'Тарас');
INSERT INTO orders (id, customer_id, total)
VALUES (1, 1, 250.50);
INSERT INTO orders (id, customer_id, total, status)
VALUES (2, 2, 100.00, 'paid');
SELECT
orders.id,
customers.name AS customer_name,
orders.total,
orders.status
FROM orders
JOIN customers ON customers.id = orders.customer_id
ORDER BY orders.id;У цьому прикладі:
customers.id і orders.id — первинні ключі;
customers.email — обов’язкове унікальне поле;
customers.name має містити щонайменше два символи;
orders.customer_id — обов’язковий зовнішній ключ;
orders.total не може бути від’ємним;
orders.status може мати лише одне з трьох значень;
status, total і дати мають значення за замовчуванням.
Обмеження можна створити під час створення таблиці або додати пізніше через ALTER TABLE.
CREATE TABLE subscribers (
id integer PRIMARY KEY,
email text
);
ALTER TABLE subscribers
ALTER COLUMN email SET NOT NULL;
ALTER TABLE subscribers
ADD CONSTRAINT subscribers_email_unique UNIQUE (email);Перед додаванням обмеження на вже наявні дані потрібно переконатися, що вони йому відповідають. Якщо в таблиці є NULL, команда SET NOT NULL завершиться помилкою. Якщо є дублікати, додавання UNIQUE також завершиться помилкою.
Обмеження можна видалити за його іменем:
ALTER TABLE subscribers
DROP CONSTRAINT subscribers_email_unique;Видаляти обмеження слід обережно: після цього база даних більше не перевірятиме відповідне правило.
Коли операція порушує обмеження, PostgreSQL скасовує цю операцію та повертає помилку.
Наприклад:
-- Порушення NOT NULL
INSERT INTO customers (id, email, name)
VALUES (3, NULL, 'Марія');
-- Порушення UNIQUE
INSERT INTO customers (id, email, name)
VALUES (3, 'olha@example.com', 'Марія');
-- Порушення CHECK
INSERT INTO orders (id, customer_id, total, status)
VALUES (3, 1, -10, 'paid');
-- Порушення FOREIGN KEY
INSERT INTO orders (id, customer_id, total)
VALUES (4, 999, 50);У робочій програмі помилку потрібно обробити та повідомити користувачу зрозумілу причину. Але саме база даних має залишатися останнім рівнем захисту цілісності.
Перевірка в коді застосунку корисна для зручного повідомлення користувачу, але не замінює обмеження бази даних.
Інший процес може записати дані напряму й обійти перевірку застосунку. Для критичних правил використовуйте NOT NULL, UNIQUE, FOREIGN KEY і CHECK.
NULL та порожнім рядком-- NULL: значення відсутнє
INSERT INTO profiles (id, phone)
VALUES (3, NULL);
-- Порожній рядок: значення є, але воно порожнє
INSERT INTO profiles (id, phone)
VALUES (4, '');NOT NULL забороняє лише NULL. Якщо порожній рядок також неприпустимий, це потрібно окремо перевірити через CHECK:
CREATE TABLE contacts (
id integer PRIMARY KEY,
name text NOT NULL CHECK (length(trim(name)) > 0)
);NOT NULL разом із CHECKТакий запис дозволяє NULL:
CREATE TABLE scores (
value integer CHECK (value >= 0)
);Якщо значення обов’язкове, правильніше написати:
CREATE TABLE scores (
value integer NOT NULL CHECK (value >= 0)
);Спочатку створюють таблицю, на яку посилається зовнішній ключ, а потім таблицю з зовнішнім ключем.
Під час вставки порядок такий самий:
додати батьківський запис;
додати дочірній запис із посиланням на нього.
Якщо правило стосується поєднання значень, використовуйте складене обмеження:
CREATE TABLE enrollments (
student_id integer NOT NULL,
course_id integer NOT NULL,
CONSTRAINT enrollments_unique
UNIQUE (student_id, course_id)
);Два окремі UNIQUE для student_id і course_id означали б, що кожен студент та кожен курс можуть зустрітися лише один раз у всій таблиці, що зазвичай неправильно.
NOT NULL забороняє відсутні значення.
PRIMARY KEY однозначно ідентифікує рядок.
UNIQUE забороняє дублікати.
FOREIGN KEY підтримує коректні зв’язки між таблицями.
CHECK перевіряє значення за логічною умовою.
DEFAULT задає значення, якщо його не передали.
Для обов’язкового значення часто потрібно поєднувати NOT NULL з CHECK або UNIQUE.
Обмеження мають бути сформульовані відповідно до правил предметної області та захищати дані незалежно від коду застосунку.