Реляційна модель: таблиці, ключі, зв'язки

Що таке таблиця, рядок і стовпець у реляційній БД?

Таблиця (table) — базова одиниця реляційної моделі: сітка, у якій стовпці описують структуру даних, а рядки наповнюють її конкретними записами. Стовпець (column, поле) — атрибут із жорстко заданим типом: id — integer, email — varchar, created_at — timestamp; тип працює як контракт, який СУБД перевіряє сама — текст у цілочисловий стовпець не запишеться. Рядок (row, запис) — один екземпляр сутності (один користувач, одне замовлення); набір стовпців у всіх рядків таблиці однаковий. Для QA це фундамент: перевіряючи, що замовлення збереглося за потрібним користувачем, ти маєш справу саме з таблицями та зв'язками між ними, навіть без жодного написаного SELECT.

Чому порядок рядків у таблиці не гарантований і чим це загрожує автотестам?

Таблиця — це множина записів, а не список, тому реляційна модель принципово не обіцяє жодного порядку рядків. Запит без ORDER BY може повертати рядки в різній послідовності на різних прогонах, на різних версіях СУБД чи після зміни плану запиту. Це класичне джерело флакі-перевірок: автотест бере «перший рядок» видачі й асертить його, а насправді «перший» щоразу інший. Практичне правило — якщо тест покладається на конкретний порядок, він мусить явно задати ORDER BY за детермінованим ключем; інакше зелений сьогодні тест почервоніє завтра без жодної зміни в продукті.

Що таке первинний ключ і які властивості роблять його ідентифікатором?

Первинний ключ (primary key, PK) — це стовпець або набір стовпців, що однозначно ідентифікує кожен рядок таблиці. Він має три властивості: унікальний (два рядки не можуть мати однаковий PK), не порожній (неявний NOT NULL — ідентифікатор без значення не вказує на жоден рядок) і один на таблицю (саме ним запис адресують). Саме на первинний ключ за замовчуванням посилаються зовнішні ключі інших таблиць. Ключ може бути й складеним (composite) — з кількох стовпців разом, як пара (order_id, product_id) у проміжній таблиці зв'язку.

Природний чи сурогатний ключ — що обереш і чому?

На практиці майже завжди беруть сурогатний, і вміння це аргументувати — те, що перевіряють на співбесіді. Природний ключ (natural key) — унікальне значення, яке дані вже мають самі по собі: email, ІПН, ISBN, код валюти USD. Сурогатний (surrogate key) — технічний ідентифікатор, позбавлений бізнес-змісту, який видає сама БД: автоінкремент (SERIAL у PostgreSQL, AUTO_INCREMENT у MySQL) або UUID. Сурогатний виграє з трьох причин: бізнес-значення нестабільні (людина змінює прізвище, користувач — email), а на ідентифікатор уже посилаються інші таблиці; унікальність «природних» значень часто оманлива (номер телефону з часом дістається іншій людині); природний ключ буває складеним і громіздким, і тягати його в кожне посилання незручно. Важливий наслідок для QA: сурогатний id дає лише технічну унікальність рядка, а не бізнес-унікальність.

Що таке зовнішній ключ і посилальна цілісність?

Зовнішній ключ (foreign key, FK) — стовпець, чиї значення зобов'язані мати відповідник серед первинних (чи унікальних) ключів іншої таблиці; наприклад, orders.user_id як FK на users.id читається як «це замовлення належить конкретному користувачеві». Найважливіше, що дає FK, — посилальна цілісність (referential integrity): БД власними обмеженнями, без коду застосунку, гарантує, що кожне посилання вказує на реальний рядок — не дозволить ані вставити замовлення з неіснуючим user_id, ані видалити користувача, поки замовлення на нього посилаються (якщо не задано інше). Для тестувальника FK — це і страховка, і об'єкт перевірки: биті посилання та «сирітські» рядки після кривої міграції — типовий клас дефектів у даних, які з UI часто непомітні.

Що станеться, якщо видалити користувача, на якого посилаються замовлення?

Правильна відповідь починається з «залежить від дії зовнішнього ключа» — і це саме те, чого чекає інтерв'юер. Поведінку при видаленні батьківського рядка задають діями ON DELETE: NO ACTION (дія за замовчуванням) і RESTRICT блокують операцію, поки існують посилання, — спроба впаде з помилкою violates foreign key constraint. CASCADE тягне видалення за собою: разом із користувачем автоматично зникнуть і всі його замовлення. SET NULL розриває зв'язок, виставляючи FK дочірніх рядків у NULL (замовлення живе далі, просто без власника), — для цього стовпець мусить бути nullable. Практичний висновок для QA: перш ніж заводити дефект «дані зникли після видалення» чи «БД не дає видалити рядок», глянь на визначення FK — часто це штатний CASCADE або заборона, а не баг.

Чим NO ACTION відрізняється від RESTRICT у зовнішньому ключі?

Відповідь «нічим, це синоніми» правильна рівно наполовину — і саме тому питання й ставлять. У MySQL/InnoDB вони справді еквівалентні: обидва відхиляють операцію над батьківською таблицею негайно, а задати RESTRICT (чи NO ACTION) — те саме, що взагалі не писати клозу ON DELETE/ON UPDATE. У PostgreSQL це різні дії: дефолт — ON DELETE NO ACTION, і його перевірку можна оголосити відкладеною (DEFERRABLE INITIALLY DEFERRED), тож база дочекається кінця транзакції, а команди всередині неї встигнуть «полагодити» стан і видалення пройде. RESTRICT строгіший: він відкладення не допускає й забороняє видалення одразу. Практичний наслідок для QA: та сама схема з NO ACTION на PostgreSQL-стенді пропустить послідовність «видалив батька → полагодив дітей → COMMIT», а на MySQL-стенді впаде на першій же команді.

Чим PRIMARY KEY відрізняється від UNIQUE?

Це найчастіше питання розділу, і сильна відповідь називає обидві відмінності, а не лише «обидва унікальні». Спільне: обидва гарантують унікальність значень і обидва підкріплені індексом, тож пошук за ними швидкий. Відмінності принципові: PRIMARY KEY — рівно один на таблицю й неявно NOT NULL; UNIQUE — скільки завгодно на таблицю й допускає NULL. За роллю PK — головний ідентифікатор рядка, на який за замовчуванням посилаються зовнішні ключі, а UNIQUE — додаткове бізнес-обмеження. Практичний приклад: у таблиці users первинним ключем роблять сурогатний id, на який посилаються замовлення, а окремий UNIQUE на email блокує реєстрацію двох акаунтів з однаковою поштою — це різні завдання, і жодне одне обмеження обох не закриє.

Чи гарантує сурогатний id, що email не зареєструють двічі?

Ні — і це поширена пастка. Сурогатний id унікальний завжди, але він забезпечує лише технічну унікальність рядка, а не бізнес-унікальність за іншим полем. Двоє людей із тим самим email спокійно отримають різні id й обидва запишуться в таблицю, бо для БД це два різні рядки. Щоб цього не сталося, потрібне окреме обмеження UNIQUE на стовпці email. Для QA це прямий тест-кейс: спробувати зареєструвати той самий email двічі й переконатися, що система відхиляє дублікат на рівні бази, а не лише перевіркою у формі, яку легко обійти прямим запитом до API.

Скільки значень NULL допускає стовпець UNIQUE?

Залежить від СУБД — і саме тому переносити висновок з одного стенда на інший не можна. У PostgreSQL і MySQL стовпець UNIQUE за замовчуванням допускає кілька рядків із NULL: порівняння NULL з NULL у SQL не дає «істину», тож такі рядки не рахуються дублікатами. У деяких СУБД (історично — SQL Server) поведінка інша: дозволений лише один NULL. Тому поведінку «UNIQUE + NULL» варто перевіряти в документації конкретної СУБД, а не відповідати з пам'яті. Практичний наслідок: якщо ти тестуєш обмеження унікальності на nullable-стовпці, поведінка з порожніми значеннями може відрізнятися між середовищами на різних СУБД.

Що означає зовнішній ключ, який допускає NULL?

Nullable FK означає опційний зв'язок, а не зіпсовані дані. Наприклад, orders.manager_id зі значенням NULL — замовлення, у якого менеджера поки просто немає: стан цілком штатний. Тому не варто заводити баг «сирітський рядок» щоразу, коли бачиш NULL у зовнішньому ключі: спершу з'ясуй, чи цей зв'язок за схемою обов'язковий. Різниця важлива для QA-перевірок: обов'язковий FK оголошують NOT NULL (замовлення без користувача не має існувати), а опційний свідомо лишають nullable — і тест має перевіряти саме той інваріант, який закладено в схему, а не абстрактне «FK завжди має бути заповнений».

Які бувають типи зв'язків між таблицями і як реалізується кожен?

Реляційна модель тримається на трьох типах зв'язків. Один-до-багатьох (1:N) — найпоширеніший: один користувач має багато замовлень, кожне замовлення належить одному користувачеві; реалізація проста — FK на боці «багато» (user_id у orders). Один-до-одного (1:1) — рідкісний: рядку однієї таблиці відповідає щонайбільше один рядок іншої; реалізують через FK з накладеним UNIQUE або через спільний первинний ключ. Багато-до-багатьох (M:N) — одне замовлення містить багато товарів, і один товар входить у багато замовлень; самими лише FK у двох таблицях його не зібрати — потрібна проміжна таблиця. Хто впізнає ці три патерни з першого погляду, той уже наполовину розібрався в незнайомій схемі.

Як реалізувати зв'язок many-to-many?

Очікувана відповідь одна: через проміжну (зв'язувальну, junction) таблицю з двома зовнішніми ключами. Прямим зовнішнім ключем M:N не зробити — «список» id у клітинку не запишеш, реляційна модель такого не знає. M:N завжди розпадається на пару зв'язків 1:N через окрему таблицю, де один рядок фіксує один факт зв'язку: order_items з order_id і product_id. Сильна відповідь додає, що первинний ключ такої таблиці зазвичай складений — пара (order_id, product_id), щоб той самий товар не потрапив у замовлення двома рядками, — і що на самому зв'язку зручно тримати атрибути: quantity, ціну на момент покупки. Той самий патерн: студенти й курси (таблиця записів на курс), статті й теги (таблиця тегування).

Навіщо взагалі зв'язок 1:1, якщо можна тримати все в одній таблиці?

Технічно 1:1 справді можна злити в одну таблицю, тож питання перевіряє, чи розумієш мотивацію розділення. Причин дві: перша — прибрати з «гарячої» users важкі чи рідко потрібні поля (біографія, аватарка), щоб основні запити лишалися легкими; друга — ізолювати чутливі дані з окремими правами доступу. Реалізують 1:1 через FK з обмеженням UNIQUE (інакше до одного users.id причепиться кілька профілів) або через спільний первинний ключ. Для QA важливо, що обидва рядки мають лишатися синхронними: створення користувача без профілю чи навпаки — потенційний баг, залежно від того, як застосунок гарантує пару.

Як читати ER-діаграму й нотацію «воронячих лапок»?

ER-діаграма (entity-relationship diagram) — погляд на базу згори: прямокутники-сутності (таблиці), їхні атрибути й лінії зв'язків. Найуживаніша нотація ліній — «воронячі лапки» (crow's foot), де кардинальність закодована символом на кінці лінії: «один» малюється рискою |, «нуль» (участь необов'язкова) — кружечком o, «багато» — трипалим розгалуженням, за яке нотація й дістала назву. Символи комбінуються в «один і тільки один», «нуль або один», «один або багато», «нуль або багато»; атрибути підписують PK/FK, щоб було видно, де який ключ. Читаючи діаграму вголос, ти фактично проговорюєш правила застосунку: «користувач розміщує нуль або багато замовлень» — це зв'язок 1:N. Для QA кожна лінія — FK-обмеження, що заслуговує на окрему перевірку, а кожна позначка UK/UNIQUE — правило унікальності, яке мусить відсікати дублікати.

Як за схемою впізнати, що десь є зв'язок many-to-many?

Ознака — проміжна таблиця, яка складається переважно з двох зовнішніх ключів. Побачив таблицю на кшталт order_items з парою FK (order_id, product_id) і, можливо, кількома атрибутами самого зв'язку (quantity) — з високою ймовірністю це розкладений зв'язок M:N між двома «головними» сутностями. Її первинний ключ зазвичай складений — з тієї самої пари зовнішніх ключів. На ER-діаграмі це видно як дві лінії 1:N, що сходяться до однієї таблиці посередині. Для тестувальника така таблиця — окремий об'єкт перевірок: чи не дублюються факти зв'язку, чи коректно поводяться обидва FK при видаленні батьків, чи не лишаються «висячі» рядки зв'язку після видалення товару або замовлення.

Тест очікує конкретний рядок, а результат плаває між прогонами. З чого почати розбір?

Найімовірніша причина — відсутність детермінованого сортування, а не баг продукту. Оскільки порядок рядків без ORDER BY не гарантований, будь-яка перевірка виду «візьми перший рядок і асертни його поле» стає лотереєю: сьогодні БД повернула один порядок, завтра інший. Перше, що варто зробити, — переконатися, що запит (чи ORM за ним) явно сортує за стабільним ключем, наприклад за id або created_at. Другий підозрюваний — недостатня ізоляція тестових даних: паралельні прогони чи залишки від попереднього тесту домішують «чужі» рядки у видачу. Тобто симптом «зелено поодинці, червоно в наборі» тут читається як проблема сортування або ізоляції стану, а не як дефект застосунку.

Замовлення посилається на user_id, якого немає в users. Це взагалі можливо і про що каже?

За наявності коректного зовнішнього ключа з посилальною цілісністю це неможливо: БД не дасть ані вставити таке замовлення, ані видалити користувача, лишивши сирітський запис. Тож якщо такий рядок реально існує, це серйозний сигнал — найчастіше про те, що зовнішній ключ узагалі не оголошений (посилання є лише «на папері», а обмеження в схемі немає), або його вимкнули під час масової міграції даних і не ввімкнули назад. Це справжній баг цілісності даних, який часто не видно в UI, поки застосунок не спробує підтягнути неіснуючого користувача й не впаде. Для QA це привід перевірити не лише дані, а й саму схему: чи є REFERENCES там, де за логікою мусить бути, і чи не лишили міграції «дірку» в цілісності.

Три кейси, де реляційна модель вирішує, знайшов ти реальний баг цілісності чи погнався за примарою: читання ER-діаграми як карти перевірок, розбір «це баг чи штатний ON DELETE?» на живій ситуації й перевірка цілісності прямо в БД замість сліпої віри в UI. Скрізь — що дивитися і чому.

Кейс 1. ER-діаграма як карта того, що можна зламати

Тобі дали схему інтернет-магазину на онбордингу й попросили «прикинути, що тут тестувати». Спершу читаємо діаграму вголос — і вона сама розповідає правила застосунку.

Кожна лінія й позначка перетворюється на конкретну перевірку:

Що дивитися і чому:

• Таблиця з двох зовнішніх ключів — майже завжди зв'язок many-to-many. ORDER_ITEMS тримає order_id і product_id, її первинний ключ складений з цієї пари. Побачив такий патерн — знай, що десь ховається M:N, і його треба покрити окремо від «головних» таблиць.
• o проти | на кінці лінії — це питання «а nullable він чи ні?». ||--o{ (нуль або багато) допускає користувача без жодного замовлення — легальний стан. Якби там стояло ||--|{ (один або багато), користувач без замовлень був би порушенням; це різні тест-кейси.
• UK (unique key) — не те саме, що PK. Первинний ключ USERS — сурогатний id (на нього посилаються замовлення), а email лише UNIQUE. Дублікат email пролізе, якщо це обмеження забули: перевіряти його треба прямим повторним запитом, а не довірою до валідації у формі.

Кейс 2. «Це баг чи штатна поведінка?» — розбір по ON DELETE

Прилітає тікет: «Видалив тестового користувача через адмінку — разом із ним зникли всі його замовлення. Втрата даних, критичний баг!». Перш ніж заводити дефект, дивимося на визначення зовнішнього ключа.

CREATE TABLE orders (
  id       SERIAL PRIMARY KEY,
  user_id  INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE
);

Ось ON DELETE CASCADE — і «зникнення» замовлень тут штатне: так спроєктовано, видалення батька каскадить на дітей. Це не баг застосунку, а поведінка БД, закладена в схему. Таблиця рішень для трьох варіантів:

Що дивитися і чому:

• Перш ніж писати «втрата даних», відкрий схему. Різниця між багом і фічею тут — один рядок у визначенні FK. Сильний QA у тікеті одразу уточнює: «поведінка задана ON DELETE CASCADE, це очікувано; питання лише в тому, чи каскад — бажаний продуктовий сценарій».
• Заборона виглядає як збій, а насправді захист. Скарга «не можу видалити рядок, БД лається» — це не поламка, а спрацьоване обмеження: на рядок посилаються дочірні записи. Баг був би, якби видалення пройшло й лишило сирітські записи.
• Другий рядок таблиці читається по-різному на двох стендах. NO ACTION і RESTRICT еквівалентні лише в MySQL/InnoDB. У PostgreSQL NO ACTION (дефолт) можна оголосити відкладеним (DEFERRABLE INITIALLY DEFERRED) — тоді помилки не буде взагалі, якщо до COMMIT дітей встигли перевісити або видалити; RESTRICT відкладення не допускає. Тож «на моїй базі така сама схема поводиться інакше» тут — не містика, а діалект.
• Справжній дефект — четвертий рядок таблиці. Якщо в базі реально є замовлення на неіснуючого користувача, значить, FK узагалі немає або його вимкнули під час міграції. Оце варто ескалювати: цілісність «на папері» без обмеження в схемі рано чи пізно впаде в проді.

Кейс 3. Перевірка цілісності прямо в БД, а не через UI

UI показує, що користувача видалено, а форма реєстрації «наче» не пускає дублікат email. «Наче» — недостатньо: інваріанти цілісності надійніше перевіряти прямо в базі, бо UI й API можуть брехати, а обмеження БД — ні.

import { test, expect } from '@playwright/test';
import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

test('після видалення юзера не лишилось сиріт-замовлень', async () => {
  // orphan = замовлення, чий user_id не має пари в users
  const { rows } = await pool.query(
    `SELECT COUNT(*)::int AS orphans
       FROM orders o
       LEFT JOIN users u ON u.id = o.user_id
      WHERE u.id IS NULL`,
  );
  expect(rows[0].orphans, 'знайдено сирітські замовлення — порушено цілісність').toBe(0);
});

Бізнес-унікальність email — окрема перевірка: сурогатний id її не гарантує, тож тестуємо саме обмеження UNIQUE, а не форму.

test('email не можна зареєструвати двічі — на рівні БД', async () => {
  const email = `dup-${Date.now()}@example.com`;
  await pool.query(`INSERT INTO users(email) VALUES ($1)`, [email]);

  // друга спроба того самого email має впертися в UNIQUE, а не створити рядок
  await expect(
    pool.query(`INSERT INTO users(email) VALUES ($1)`, [email]),
  ).rejects.toThrow(/duplicate key value|unique constraint/i);
});

Що дивитися і чому:

• LEFT JOIN ... WHERE u.id IS NULL — канонічний пошук сирітських записів. Ліве з'єднання лишає всі замовлення, а u.id IS NULL виловлює ті, для яких пари в users не знайшлося. Нуль сирітських записів — цілісність ціла; будь-яке число більше нуля — привід копати міграцію чи вимкнений FK.
• Дублікат email ловиться помилкою БД, а не «тихим» другим рядком. Якщо UNIQUE на місці, друга вставка падає з duplicate key value. Якщо ж обидві вставки пройшли — обмеження немає, і валідація у формі лише маскує діру: прямий запит до API створить два акаунти.
• Не плутай технічну й бізнес-унікальність у тесті. Перевіряти id на унікальність безглуздо — його гарантує сама БД. Цінність має перевірка саме бізнес-ключа (email), бо це той інваріант, який реально ламається на практиці.

Таблиця, рядок, схема

• Розумію, чому порядок рядків без ORDER BY не гарантований (таблиця — множина, не список) і чому «перший рядок без сортування» — джерело флакі-тесту, що лікується явним ORDER BY за стабільним ключем.
• Знаю, що стовпець має фіксований тип-контракт, який БД перевіряє сама (рядок у integer-стовпець не покласти).
• Розрізняю два значення слова «схема»: опис структури (таблиці, типи, ключі — його змінюють міграції) і простір імен (namespace) на кшталт public.users.

Первинний ключ

• Знаю три властивості первинного ключа: унікальний, NOT NULL (неявно) і один на таблицю.
• Можу пояснити різницю природний (natural) vs сурогатний (surrogate) ключ і чому на практиці майже завжди беруть сурогатний.
• Знаю, що первинний ключ буває складеним (composite) — з кількох стовпців разом, як пара (order_id, product_id).

Зовнішній ключ і посилальна цілісність

• Можу пояснити, що зовнішній ключ (FK) — це стовпець, значення якого мусить збігатися з PK (чи UNIQUE) іншої таблиці.
• Розумію, що посилальна цілісність — гарантія БД, що посилання завжди веде на реальний рядок, без коду застосунку.
• Знаю дії ON DELETE: NO ACTION (за замовчуванням) і RESTRICT — заборонити, CASCADE — видалити дітей, SET NULL — обнулити FK (потребує nullable-стовпця).
• Знаю, що NO ACTION і RESTRICT — синоніми лише в MySQL/InnoDB: у PostgreSQL NO ACTION допускає відкладену до кінця транзакції перевірку, а RESTRICT — ні.
• Можу відрізнити штатний CASCADE/заборону на FK від бага: перш ніж заводити дефект «дані зникли»/«не дає видалити», дивлюся на визначення FK.
• Розумію, що nullable FK — це опційний зв'язок (замовлення без менеджера), а не сирітський рядок.

PRIMARY KEY проти UNIQUE

• Можу назвати обидві відмінності: PK один на таблицю й неявно NOT NULL, а UNIQUE — багато й допускає NULL.
• Знаю спільне: обидва гарантують унікальність і обидва створюють під собою індекс.
• Розумію, що сурогатний id не замінює UNIQUE на email — бізнес-дублікат пролізе без окремого обмеження.
• Пам'ятаю нюанс про NULL: у PostgreSQL/MySQL UNIQUE-стовпець за замовчуванням допускає кілька NULL, у SQL Server історично — лише один.

Зв'язки й ER-діаграма

• Розрізняю три типи зв'язків і реалізацію кожного: 1:N — FK на боці «багато»; 1:1 — FK з UNIQUE; M:N — junction-таблиця з двох FK, бо «список» id у клітинку не покласти.
• Знаю, навіщо різати сутність на 1:1: винести великі/рідкісні поля або відокремити чутливі дані.
• Умію читати «воронячі лапки» (crow's foot): | — один, o — нуль/необов'язково, розгалуження — багато.
• Впізнаю проміжну таблицю за ознакою «складається переважно з двох FK» — сигнал, що десь є зв'язок many-to-many.
• Розумію, що для QA ER-діаграма — карта того, що можна зламати: кожна лінія це FK-перевірка, кожен UK — правило унікальності.