Мова SQL і DDL

Попередні теми відповідали на запитання, які таблиці потрібні та як вони мають бути пов'язані. Тепер перетворимо спроєктовану структуру на команди, які розуміє PostgreSQL. Для цього використаємо SQL і його групу DDL.

Цілі лекції

Після опрацювання матеріалу ви зможете:

• пояснити призначення SQL і роль стандарту ANSI/ISO SQL;
• розрізняти групи DDL, DML, DCL і TCL;
• добирати основні типи даних PostgreSQL;
• створювати схему та таблиці командою CREATE;
• задавати іменовані обмеження PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK і значення DEFAULT;
• визначати правильний порядок створення пов'язаних таблиць.

Передумови

Потрібно розуміти поняття таблиці, стовпця, рядка, домену, первинного та зовнішнього ключів. Для виконання прикладів потрібне підключення до навчальної бази PostgreSQL через psql, pgAdmin або DBeaver.

1. SQL і стандарт ANSI/ISO

SQL (Structured Query Language) - декларативна мова визначення структури, опрацювання та керування реляційними базами даних. Декларативність означає, що команда переважно описує потрібний результат, а спосіб її виконання обирає СКБД.

SQL стандартизують міжнародні організації ANSI та ISO. Стандарт визначає спільні правила й конструкції, завдяки яким базові команди в різних реляційних СКБД подібні. Проте кожна СКБД має власні типи, функції та розширення. У цьому курсі використовуємо діалект PostgreSQL, тому код потрібно виконувати саме в PostgreSQL.

SQL-команда зазвичай завершується крапкою з комою:

CREATE SCHEMA college;

Ключові слова SQL нечутливі до регістру, але поширена домовленість - писати їх великими літерами, а власні назви об'єктів - малими:

CREATE TABLE college.departments (
    department_id INTEGER
);

Не варто брати звичайні назви в подвійні лапки без потреби. PostgreSQL перетворює нецитовані ідентифікатори на нижній регістр, а цитовані робить чутливими до регістру. Через це таблиця "Students" потребуватиме лапок у кожному наступному зверненні. Практичний стиль курсу: snake_case, латинські літери, без пробілів.

2. Групи команд SQL

Поділ SQL на групи пояснює призначення команд. Межі в навчальній літературі можуть трохи відрізнятися, але для курсу використовуємо таку класифікацію:

У цій лекції працюємо лише з частиною DDL, яка створює структуру: CREATE SCHEMA і CREATE TABLE. Зміна та видалення структури, очищення таблиць і керування правами (ALTER, DROP, TRUNCATE, GRANT, REVOKE) належать до лекції 11. DML докладно починається з лекції 12, а транзакції розглядатимуться окремою темою.

3. Схема в PostgreSQL

У PostgreSQL схема є простором імен усередині бази даних. Вона групує таблиці та інші об'єкти й дає змогу мати однакові назви об'єктів у різних просторах, наприклад college.students і archive.students.

Є два базові варіанти організації навчальної бази:

1. Використовувати стандартну схему public. Це найпростіше для маленької одноцільової бази.
2. Створити окрему схему предметної області. Це чіткіше відокремлює навчальні, службові або модульні об'єкти.

Для наскрізного прикладу створимо окрему схему:

CREATE SCHEMA college;

Далі звертатимемося до об'єктів повними іменами схема.таблиця. Такий стиль не залежить від поточного search_path і однозначно показує місце об'єкта:

college.departments
college.courses
college.enrollments

Окремі схеми корисні для модулів, команд або різних рівнів доступу. Надмірно ділити маленьку базу на десятки схем не потрібно: вибір має спрощувати структуру, а не приховувати її.

4. Основні типи даних PostgreSQL

Тип стовпця визначає, які значення можна зберігати та які операції над ними допустимі. Тип потрібно обирати за змістом даних, а не за випадковим прикладом.

Числа

Для кількості кредитів достатньо SMALLINT, для більшості цілих ідентифікаторів - INTEGER, а для дуже великих діапазонів - BIGINT. Суму грошей зберігають у точному типі, наприклад:

tuition_fee NUMERIC(10, 2)

NUMERIC(10, 2) допускає загалом до 10 цифр, із них 2 після десяткового роздільника. REAL і DOUBLE PRECISION є наближеними типами, тому їх не слід автоматично обирати для фінансових сум.

Текст

TEXT підходить для тексту довільної практичної довжини. VARCHAR(100) задає верхню межу в 100 символів. Межа корисна лише тоді, коли вона є реальною вимогою. CHAR(n) доповнює коротше значення пробілами й рідко потрібен для звичайних назв.

Дата і час

DATE зберігає календарну дату без часу. TIMESTAMP зберігає дату й час без інформації про часовий пояс. TIMESTAMPTZ у PostgreSQL зберігає момент часу та коректно відображає його відповідно до часового поясу сеансу. Для події, яка має однозначний момент у часі, зазвичай доречний TIMESTAMPTZ.

Генеровані ідентифікатори

Сучасний стандартний спосіб автоматичної генерації числового ідентифікатора в PostgreSQL:

department_id INTEGER GENERATED ALWAYS AS IDENTITY

GENERATED ALWAYS означає, що значення зазвичай генерує PostgreSQL. Існує також GENERATED BY DEFAULT, яке дозволяє явно передати власне значення. У нових схемах IDENTITY варто віддавати перевагу старому псевдотипу SERIAL.

5. CREATE TABLE

Загальна форма створення таблиці:

CREATE TABLE schema_name.table_name (
    column_name data_type column_constraint,
    column_name data_type,
    table_constraint
);

Створимо таблицю підрозділів:

CREATE TABLE college.departments (
    department_id INTEGER GENERATED ALWAYS AS IDENTITY,
    code VARCHAR(10) NOT NULL,
    name TEXT NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    CONSTRAINT pk_departments PRIMARY KEY (department_id),
    CONSTRAINT uq_departments_code UNIQUE (code),
    CONSTRAINT uq_departments_name UNIQUE (name)
);

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

6. Обмеження цілісності

Обмеження (constraint) - правило, яке PostgreSQL перевіряє під час зміни даних. Обмеження не замінюють правильного проєктування, але не дозволяють зберегти стани, що суперечать визначеним правилам.

Для імен використовуватимемо префікси:

Іменування полегшує читання повідомлень про помилки та майбутні міграції. Назви мають бути унікальними в межах схеми для тих об'єктів, які PostgreSQL створює на їхній основі.

PRIMARY KEY

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

CONSTRAINT pk_departments PRIMARY KEY (department_id)

NOT NULL

NOT NULL забороняє відсутнє значення у конкретному стовпці:

name TEXT NOT NULL

У PostgreSQL NOT NULL записують біля стовпця. Окремого імені в цій базовій формі йому не задають. Вимога користувача «усі обмеження мають імена» на практиці має виняток для звичайного NOT NULL: назви задаємо табличним PRIMARY KEY, FOREIGN KEY, UNIQUE і CHECK, а NOT NULL лишається властивістю стовпця.

UNIQUE

UNIQUE забороняє дублювати значення або комбінацію значень:

CONSTRAINT uq_departments_code UNIQUE (code)

У PostgreSQL звичайне UNIQUE допускає кілька рядків із NULL, тому для обов'язкового унікального значення його часто поєднують із NOT NULL.

CHECK

CHECK перевіряє логічну умову для кожного рядка:

CONSTRAINT chk_courses_credits CHECK (credits BETWEEN 1 AND 30)

Важлива особливість: результат умови CHECK не повинен бути FALSE; значення NULL може пройти перевірку. Тому обов'язковий стовпець із діапазоном потребує і NOT NULL, і CHECK.

DEFAULT

DEFAULT задає значення, якщо під час додавання рядка стовпець не вказали:

is_active BOOLEAN NOT NULL DEFAULT TRUE

DEFAULT не робить стовпець обов'язковим і не замінює явно передане NULL. Це правило формування початкового значення, а не перевірка всіх можливих значень.

FOREIGN KEY

FOREIGN KEY забезпечує посилальну цілісність: значення має відповідати ключу наявного рядка в іншій таблиці або бути NULL, якщо стовпець не має NOT NULL.

Створимо таблицю курсів після таблиці підрозділів:

CREATE TABLE college.courses (
    course_id INTEGER GENERATED ALWAYS AS IDENTITY,
    department_id INTEGER NOT NULL,
    code VARCHAR(20) NOT NULL,
    title TEXT NOT NULL,
    credits SMALLINT NOT NULL DEFAULT 3,
    tuition_fee NUMERIC(10, 2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT pk_courses PRIMARY KEY (course_id),
    CONSTRAINT fk_courses_department
        FOREIGN KEY (department_id)
        REFERENCES college.departments (department_id),
    CONSTRAINT uq_courses_code UNIQUE (code),
    CONSTRAINT chk_courses_credits CHECK (credits BETWEEN 1 AND 30),
    CONSTRAINT chk_courses_tuition_fee CHECK (tuition_fee >= 0)
);

Таблиця college.departments є батьківською, а college.courses - дочірньою щодо цього зв'язку. Батьківську таблицю потрібно створити раніше, щоб на момент створення зовнішнього ключа ціль уже існувала.

7. Складений ключ і таблиця зв'язку

Для реєстрації студентів на курси потрібна таблиця зв'язку. Тут використаємо складений первинний ключ: одна пара student_id і course_id не може повторитися.

CREATE TABLE college.enrollments (
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    enrolled_on DATE NOT NULL DEFAULT CURRENT_DATE,
    status VARCHAR(12) NOT NULL DEFAULT 'active',
    CONSTRAINT pk_enrollments PRIMARY KEY (student_id, course_id),
    CONSTRAINT fk_enrollments_course
        FOREIGN KEY (course_id)
        REFERENCES college.courses (course_id),
    CONSTRAINT chk_enrollments_student_id CHECK (student_id > 0),
    CONSTRAINT chk_enrollments_status
        CHECK (status IN ('active', 'completed', 'cancelled'))
);

У цьому навчальному фрагменті student_id перевіряється як додатне число, але ще не посилається на таблицю студентів, бо її не створювали. У повній схемі для нього також має бути зовнішній ключ. Не можна оголошувати посилання на об'єкт, якого немає.

8. Порядок проєктування DDL-скрипту

Перед запуском CREATE TABLE корисно пройти короткий алгоритм:

1. Визначити схему й правила назв.
2. Перелічити таблиці та залежності між ними.
3. Створити незалежні батьківські таблиці.
4. Створити залежні таблиці із зовнішніми ключами.
5. Для кожного стовпця вибрати найвужчий доречний тип.
6. Додати обов'язковість, унікальність, діапазони та початкові значення.
7. Дати обмеженням зрозумілі стабільні імена.
8. Виконати весь скрипт у чистій навчальній базі й прочитати повідомлення PostgreSQL.

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

Практичні завдання

Завдання 1. Створіть довідник аудиторій

У схемі college створіть таблицю rooms із такими правилами:

• room_id - автоматично генерований INTEGER і первинний ключ;
• room_number - обов'язковий текст до 10 символів, унікальний;
• capacity - обов'язкове мале ціле число від 1 до 300;
• has_projector - обов'язкове логічне значення, типово FALSE;
• усі табличні обмеження мають зрозумілі імена.

Запустіть команду в PostgreSQL і переконайтеся, що таблицю створено.

Завдання 2. Додайте дисципліни до підрозділів

Створіть таблицю college.subjects, яка:

• має автоматичний первинний ключ subject_id;
• посилається обов'язковим department_id на college.departments;
• містить обов'язкові code VARCHAR(20) і title TEXT;
• не допускає повторення code;
• має hours SMALLINT зі значенням за замовчуванням 30 і діапазоном від 15 до 300;
• містить усі обмеження вже під час CREATE TABLE.

Поясніть, чому college.departments потрібно створити першою.

Завдання 3. Спроєктуйте фрагмент системи розкладу

Створіть у правильному порядку таблиці teachers і lesson_slots.

Для teachers передбачте автоматичний ключ, обов'язкове повне ім'я, обов'язкову унікальну електронну адресу та is_active зі значенням TRUE за замовчуванням.

Для lesson_slots передбачте автоматичний ключ, обов'язкове посилання на викладача, дату заняття, час початку, тривалість від 1 до 6 академічних годин і формат із множини onsite, online, mixed. Доберіть типи, задайте імена обмеженням і коротко обґрунтуйте кожен NOT NULL, UNIQUE, CHECK та DEFAULT.

Підсумок

• SQL є стандартизованою декларативною мовою, але PostgreSQL має власний діалект і типи.
• DDL визначає структуру, DML працює з даними, DCL керує правами, TCL - транзакціями.
• Схема PostgreSQL групує об'єкти й утворює простір імен; повні імена роблять DDL однозначним.
• Тип даних потрібно вибирати за змістом: точні суми зберігають у NUMERIC, календарні дати - у DATE, моменти часу - у TIMESTAMPTZ.
• CREATE TABLE одночасно задає стовпці, типи та правила цілісності.
• PRIMARY KEY, FOREIGN KEY, UNIQUE і CHECK доцільно іменувати; NOT NULL задають біля стовпця.
• DEFAULT підставляє значення лише тоді, коли стовпець не вказано, а CHECK для обов'язкового значення потрібно поєднувати з NOT NULL.
• Батьківські таблиці створюють до дочірніх таблиць із зовнішніми ключами.
• Зміна та видалення структури, очищення таблиць і керування правами розглядатимуться в лекції 11.