Створення схеми та перевірка CRUD і constraints

Мета та потрібні знання

Створити невелику реляційну базу, перевірити ключі й обмеження та виконати CRUD із передбачуваним результатом. Потрібно знати одиницю рядка, NOT NULL, CHECK, REFERENCES і WHERE. Результат — SQL-файл, словник даних та журнал успішних і відхилених операцій.

Теоретичний мінімум

Аудиторія має власний код і додатну місткість. Заняття посилається на існуючу аудиторію й має момент початку. Код є предметним унікальним атрибутом; числовий id — технічним ключем. Місткість описує кількість місць, тому integer і CHECK > 0 відповідають змісту. Цей приклад перевіряє структуру й CRUD; накладання часових інтервалів потребувало б окремого правила.

Підготовка середовища

Використовуйте локальний PostgreSQL або окремий навчальний Supabase project. Для локального сервера підключіться через pgAdmin Query Tool або psql -d назва_навчальної_бази. У Supabase відкрийте SQL Editor власного навчального проєкту. Потрібні права створення схеми й таблиць. Перевірте з’єднання командою SELECT version(), current_database(), current_user;.

Скрипт нижче створює власну схему один раз і не видаляє наявних даних. Якщо схема вже існує після минулого запуску, використайте іншу навчальну назву та змініть SET search_path відповідно. Не виконуйте роботу у production-базі. Для окремих експериментів відкривайте новий запит і після помилки в явній транзакції виконуйте ROLLBACK.

Повний демонстраційний SQL

Збережіть як lab01-demo.sql і виконайте повністю:

CREATE SCHEMA lab01_demo;
SET search_path TO lab01_demo;
CREATE TABLE room (
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  code text NOT NULL UNIQUE CHECK (length(trim(code)) > 0),
  capacity integer NOT NULL CHECK (capacity > 0)
);
CREATE TABLE lesson (
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL CHECK (length(trim(title)) > 0),
  room_id integer NOT NULL REFERENCES room(id),
  starts_at timestamptz NOT NULL
);
INSERT INTO room (code, capacity) VALUES ('A-101',20),('B-202',30);
INSERT INTO lesson (title, room_id, starts_at) VALUES
  ('SQL практика',1,'2027-04-12 10:00:00+03'),
  ('Моделювання',2,'2027-04-12 12:00:00+03');
SELECT id, code, capacity FROM room ORDER BY id;
UPDATE room SET capacity = 24 WHERE code = 'A-101'
RETURNING code, capacity;
SELECT title FROM lesson
WHERE starts_at >= TIMESTAMPTZ '2027-04-12 00:00:00+03'
ORDER BY starts_at, id;
-- Демонструємо скасування зміни без втрати даних.
BEGIN;
DELETE FROM lesson WHERE id = 2 RETURNING id, title;
ROLLBACK;
SELECT COUNT(*) AS lessons_after_rollback FROM lesson;

Пояснення коду й очікуваний результат

Схема відокремлює таблиці прикладу. Дві аудиторії отримують id 1 та 2; на них посилаються два заняття. UPDATE повертає A-101 і 24. Вибірка за датою дає обидва заняття. DELETE всередині транзакції тимчасово видаляє друге заняття, але ROLLBACK скасовує зміну. Фінальна кількість знову 2.

Timestamptz представляє момент; відображення може мати інший часовий зсув відповідно до сесії. Це не втрата моменту. Перевіряйте порівняння на однаковій семантиці. RETURNING дозволяє побачити фактично змінені рядки без додаткового читання.

Покрокове виконання

1. Запишіть одиницю рядка для кожної таблиці та залежність між ними.
2. Виконайте скрипт і збережіть фінальні результати SELECT.
3. В окремих запитах спробуйте повторити код A-101, вставити capacity = 0 та створити lesson з room_id = 999.
4. Для кожної відмови запишіть правило й назву обмеження з повідомлення. Стан таблиць має залишитися коректним.
5. Вставте ще одну аудиторію без занять та перевірте, що це допустимо.
6. Оновіть лише її місткість з WHERE за ключем. Перевірте, що змінено рівно один рядок.
7. Спробуйте видалити аудиторію з заняттям. Поясніть, чому зовнішній ключ обмежує дію.

Самостійне завдання

Створіть окрему схему для обладнання та його перевірок. Один рядок обладнання — фізичний пристрій з унікальним інвентарним номером, назвою та додатною початковою вартістю. Одна перевірка — подія для пристрою з датою, результатом і коментарем. Визначте, які поля обов’язкові, та обґрунтуйте типи. Результат перевірки обмежте погодженим переліком, наприклад passed, failed, pending.

Додайте щонайменше три пристрої, чотири перевірки, одну зміну атрибута за ключем, фільтр за датою та демонстрацію скасування DELETE. Перевірте повторний інвентарний номер, відсутнє обладнання в посиланні й некоректну вартість. Створення цієї власної схеми та запитів є основним завданням; готова схема аудиторій допомагає опанувати техніку.

Додаткова вимога — пояснити, як зберігати невідомий коментар і чи порожній текст відрізняється від NULL у вашій моделі. У словнику даних вкажіть одиницю вартості та семантику дати перевірки. Не додавайте автоматичний CASCADE без предметного пояснення.

Типові помилки й налагодження

Relation does not exist часто означає неправильний search_path або з’єднання з іншою базою. Перевірте current_database() і схему. Duplicate key після повторного запуску не означає, що треба видалити всі дані: використайте свіжу навчальну схему. Foreign key violation потребує перевірки посилання, а не вимкнення обмеження.

UPDATE без WHERE змінює всі рядки. Перед власною зміною покажіть SELECT за тим самим ключем і зафіксуйте кількість RETURNING. Якщо транзакція aborted, виконайте ROLLBACK і дослідіть початкову помилку. Повторення наступних команд у цьому стані не відновлює транзакцію.

Результат та контрольні питання

Подайте SQL-файл, словник атрибутів, результати читання та таблицю негативних перевірок. Чому код аудиторії UNIQUE, а title заняття може повторюватися? Яка різниця між CHECK та NOT NULL? Що збереглося після ROLLBACK? Яке правило визначає допустимість видалення батьківського рядка?

Додаткова предметна перевірка

Для власної схеми окремо перевірте NULL у кожному обов’язковому полі. CHECK допустимого результату не забороняє NULL самостійно, тому перелік значень та обов’язковість задаються різними обмеженнями. У таблиці негативних тестів збережіть очікувану причину відмови й фактичний стан після неї. Якщо операція всередині транзакції завершилася помилкою, завершіть ROLLBACK перед наступним незалежним сценарієм.

Схема процесу

Самоперевірка

Підсумок

Обмеження бази захищають допустимі стани незалежно від клієнта. CRUD перевіряємо за кількістю та значеннями рядків. Негативні сценарії підтверджують дію предметних правил.

Джерела

• PostgreSQL Tutorial
• SQL commands