Що ви опануєте
Навчитеся обирати типи, задавати первинні й зовнішні ключі, обов’язковість, унікальність та предметні обмеження. Цілісність означає, що база зберігає стани, які відповідають її правилам. Якщо в системі реєстрації кількість місць не може бути від’ємною, це правило повинне перевірятися під час кожного запису незалежно від клієнта.
Типи та предметне значення
Integer і bigint зберігають цілі числа з різними межами. Numeric(p,s) задає десяткову точність і кількість цифр після крапки: numeric(10,2) підходить для навчального прикладу суми у гривнях. Double precision представляє наближені вимірювання; для точної грошової арифметики зазвичай обирають numeric або цілі мінімальні грошові одиниці.
Text зберігає рядки, boolean — логічне значення, date — календарну дату. Timestamp with time zone, скорочено timestamptz, представляє момент часу; PostgreSQL відображає його відповідно до часової зони сесії. Вихідна назва зони не зберігається разом із моментом. Якщо бізнес-правило залежить від локальної зони майбутньої події, її ідентифікатор варто моделювати окремо.
UUID — 128-бітний ідентифікатор. Він зручний для розподіленого створення записів, але не є механізмом авторизації. Знання або незнання id не повинне визначати право читати рядок. Тип обирають за семантикою й навантаженням, а не за довжиною прикладу в документації.
Обов’язковість і перевірки
NOT NULL забороняє відсутнє значення. CHECK перевіряє умову для запису. DEFAULT пропонує значення під час вставлення, коли стовпець пропущено; явно переданий NULL залишиться NULL, якщо його не заборонити. Значення за замовчуванням не підтверджує правдивість факту: today у даті завершення створює хибну історію, якщо подія ще триває.
Первинний ключ поєднує унікальність та обов’язковість і може складатися з кількох стовпців. UNIQUE за стандартною поведінкою допускає кілька NULL, оскільки вони не вважаються рівними для цієї перевірки. Для іншої семантики PostgreSQL підтримує UNIQUE NULLS NOT DISTINCT. Спершу визначайте предметне правило, потім обирайте його реалізацію.
Виконувана схема реєстрації
CREATE SCHEMA lecture_constraints;
SET search_path TO lecture_constraints;
CREATE TABLE student (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
display_name text NOT NULL CHECK (length(trim(display_name)) > 0)
);
CREATE TABLE seminar (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
starts_at timestamptz NOT NULL,
capacity integer NOT NULL CHECK (capacity > 0)
);
CREATE TABLE registration (
student_id bigint NOT NULL REFERENCES student(id),
seminar_id bigint NOT NULL REFERENCES seminar(id),
created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (student_id, seminar_id)
);
INSERT INTO student (email, display_name)
VALUES ('student@example.org', 'Олена');
INSERT INTO seminar (title, starts_at, capacity)
VALUES ('SQL', '2027-03-10 10:00:00+02', 20);
INSERT INTO registration (student_id, seminar_id) VALUES (1, 1);
SELECT * FROM registration;
Identity починає генерувати id для нової таблиці; явні 1 у цьому навчальному наборі відповідають першому рядку. Registration є таблицею зв’язку багато-до-багатьох: студент може мати багато семінарів, семінар — багато студентів. Складений ключ забороняє повторну реєстрацію тієї самої пари.
Email у прикладі унікальний за фактичним текстом. Якщо правило враховує регістр або нормалізацію пробілів, потрібна погоджена канонічна форма та відповідне обмеження. Ім’я не робимо унікальним: дві різні людини можуть мати однакові імена. Значення title також може повторюватися для подій у різні дати.
Як працюють зовнішні ключі
Зовнішній ключ гарантує існування запису, на який посилається значення. Він не гарантує правильність предметного вибору: реєстрація може вказувати на існуючий, але помилково обраний семінар. Для видалення батьківського рядка треба визначити політику. NO ACTION — стандартна дія; CASCADE видаляє залежні рядки; SET NULL має зміст для необов’язкового зв’язку.
Для історії реєстрацій автоматичне каскадне видалення студента може порушити вимоги збереження фактів. Можливі деактивація облікового запису або окремий процес анонімізації. Ці рішення залежать від вимог і не визначаються універсальним правилом для всіх таблиць.
Межа одного рядка
CHECK capacity > 0 перевіряє лише місткість семінару. Він не обмежує кількість registration. Для запису останнього вільного місця потрібна узгоджена транзакційна операція: два клієнти можуть одночасно прочитати однаковий залишок. Пізніша лекція розгляне блокування. PostgreSQL не підтримує підзапит до інших рядків у звичайному CHECK; міжрядкові правила потребують відповідного механізму.
Практична перевірка й налагодження
Спробуйте окремо вставити студенту повторний email, зареєструвати пару вдруге, використати seminar_id, якого немає, та створити capacity = 0. Кожен тест повинен отримати відповідну помилку. Відхилені запити не треба об’єднувати з початковою демонстрацією, якщо клієнт припиняє виконання після першої помилки.
У явній транзакції помилка переводить її в стан abort; наступні запити до ROLLBACK не виконуються нормально. Це важливо під час ручних експериментів. Перевіряйте constraint_name у повідомленні й надавайте зрозуміле пояснення користувачу застосунку, зберігаючи технічні подробиці в журналі.
Типові помилки та професійні практики
Зберігання дат у text ускладнює сортування, діапазони й перевірку. Використовуйте date або timestamptz за семантикою. Відсутність NOT NULL біля CHECK залишає пропуски допустимими. Перевірка в інтерфейсі покращує взаємодію, а обмеження бази захищають від усіх шляхів запису.
Називайте важливі обмеження, документуйте одиниці й часову зону, перевіряйте міграцію на копії структури та тестових даних. Перед додаванням UNIQUE до заповненої таблиці знайдіть дублікати й визначте їхню долю. Збереження цілісності включає розуміння старих даних і способу виправлення, а не лише успішне створення порожньої таблиці.
Обмеження як спільний контракт клієнтів
До однієї бази можуть звертатися вебінтерфейс, мобільний застосунок і службовий імпорт. Перевірка місткості лише у формі не захищає від некоректного CSV-імпорту. Обмеження PostgreSQL застосовуються на межі запису для відповідних операцій і ролей. Клієнтські перевірки залишаються корисними для раннього зрозумілого повідомлення, але база підтримує спільний інваріант.
Для зовнішнього ключа потрібно визначити, чи посилання обов’язкове. REFERENCES без NOT NULL допускає відсутність простого посилання. У registration первинний ключ забезпечує обов’язковість обох його компонентів. Для необов’язкового відповідального працівника NULL може означати, що його ще не призначено, і тоді це допустимий етап процесу.
Унікальність визначає конкретну область: email у всій таблиці, пара студент–семінар або код кімнати в межах корпусу. Для останнього варіанта можна використати складене UNIQUE(building_id,code). Окреме UNIQUE(code) надмірно обмежило б різні корпуси з однаковими номерами. Перевірка має відповідати предметному контексту.
Перед додаванням правила до заповненої таблиці знайдіть записи, які його порушують. Для дублюючих пар визначте, який факт є правильним і чи потрібно об’єднати залежні дані. Випадкове видалення одного рядка може втратити історію. Збережіть план міграції, перевірки до й після та спосіб відновлення. Так обмеження стає частиною процесу підтримки якості, а не лише початкового DDL.
Схема процесу
Визначаємо представлення значення.
Самоперевірка
Як обґрунтувати відповідь своїми словами?
Підсумок
Типи та обмеження переводять правила предметної області у перевірки бази. Зовнішній ключ, CHECK і UNIQUE мають різні завдання. Міжрядкові правила потребують узгодженої транзакційної логіки.