DDL, типи даних і обмеження цілісності

На які категорії поділяються команди SQL?

Класично SQL ділять на чотири підмови за тим, чим команда керує. DDL (Data Definition Language) визначає структуру — CREATE, ALTER, DROP, TRUNCATE. DML (Data Manipulation Language) працює з даними — INSERT, UPDATE, DELETE і SELECT (частина авторів виносить читання в окрему DQL). DCL (Data Control Language) керує правами — GRANT і REVOKE, саме за ним стоїть read-only доступ QA на прод. TCL (Transaction Control Language) керує транзакціями — COMMIT, ROLLBACK, SAVEPOINT. Поділ не академічний: групи по-різному поводяться щодо транзакцій і прав доступу. На співбесіді це розминка, тож відповідь має звучати без пауз.

Чим відрізняється поведінка DDL у транзакціях між PostgreSQL і MySQL?

У PostgreSQL DDL живе всередині транзакції: CREATE TABLE чи ALTER, виконані після BEGIN, зникнуть разом із ROLLBACK, ніби їх не було. У MySQL натомість кожна DDL-команда виконує неявний commit (implicit commit): відкрита транзакція фіксується в момент ALTER TABLE, і повернути її вже неможливо. Практичний наслідок стосується міграцій і клінапу в тестах: у MySQL не можна розраховувати, що обгортка BEGIN ... ROLLBACK прибере структурні зміни. Це бонусна деталь до питання про категорії SQL, яка одразу показує, що кандидат працював із різними СУБД.

Що роблять CREATE, ALTER і DROP і яка з них найнебезпечніша на живій базі?

Це трійця DDL для життєвого циклу таблиці. CREATE TABLE створює таблицю з нуля — одним виразом описує стовпці, їхні типи й обмеження. ALTER TABLE змінює вже наявну таблицю — додати чи прибрати стовпець, змінити тип, навісити або зняти обмеження. DROP TABLE знищує таблицю разом зі структурою, індексами й обмеженнями, незворотно. Найнебезпечніша на живій базі — саме ALTER: вона зачіпає таблицю з реальними даними, і QA стикається з нею через міграції. Руками тестувальник DROP не запускає — ця команда належить міграціям і адмініструванню.

Чому не можна зберігати гроші у FLOAT і що використати замість нього?

Це питання-маркер: слабка відповідь — «float неточний», сильна пояснює механізм. FLOAT і DOUBLE зберігають число у двійковій формі з плаваючою комою (IEEE 754), а багато звичних десяткових дробів у двійковій системі не мають скінченного запису — так само як 1/3 у десятковій. Класична ілюстрація: 0.1 + 0.2 дає не 0.3, а 0.30000000000000004. На одній операції похибка мікроскопічна, але фінансові операції складаються тисячами, перемножуються на курси й округлюються — накопичена похибка зрештою дає звіт, який не б'ється з реальністю. Тому гроші, ціни й курси тримають тільки в точних типах NUMERIC/DECIMAL, які зберігають і рахують значення десятково.

Що означає запис NUMERIC(12, 2)?

NUMERIC(12, 2) (синонім DECIMAL) задає точне десяткове число: до 12 значущих цифр усього, з них 2 після коми. Перше число називають точністю (precision), друге — масштабом (scale). На практиці це означає до 10 цифр до коми й 2 після — тобто суми приблизно до 10 мільярдів з копійками. База зберігає й рахує таке число десятково, без двійкових сюрпризів, тому воно ідеальне для грошей. Якщо спробувати вставити значення, що не влазить у задану точність, база відхилить його або округлить згідно з правилами масштабу — це теж перевірка для QA.

Коли FLOAT доречний, а коли ні?

FLOAT/DOUBLE доречні там, де наближеність не шкодить: наукові виміри, географічні координати, відсотки навантаження, дані сенсорів — значення, які й так є приблизними за природою. Не доречні вони скрізь, де числа рахують люди й кожна копійка має зійтися: гроші, ціни, курси, бухгалтерія. Проста ознака «тут потрібен DECIMAL» — коли результат порівнюють на точну рівність або підбивають підсумки, які мусять збігтися до останнього знака. Тобто вибір типу — це вже вимога до точності, а не технічна дрібниця.

У чому різниця між VARCHAR, TEXT і CHAR?

CHAR(n) — рядок фіксованої довжини, доповнюється пробілами до n; майже не використовується. VARCHAR(n) — рядок змінної довжини до n символів. TEXT — рядок без явного ліміту. Практична різниця між VARCHAR і TEXT залежить від СУБД: у PostgreSQL різниці у швидкості немає, а VARCHAR(n) поводиться як TEXT, до якого причепили перевірку довжини; у MySQL типи відрізняються помітніше — способом зберігання, роботою індексів, дефолтами. Для QA головне не академічна різниця, а два питання: що станеться при перевищенні довжини й чи правильне кодування стовпця.

Що станеться при вставці рядка, довшого за VARCHAR(n)?

Поведінка підступна й залежить від СУБД та конфігурації. У PostgreSQL вставка задовгого рядка у VARCHAR(n) дає помилку. MySQL у нестрогому режимі (без strict mode) зі старих часів обрізав рядок до n символів без жодної помилки — дані псувалися тихо. У сучасних версіях strict mode увімкнений за замовчуванням, тож задовгий рядок теж відхиляється, але фінальне слово за конфігурацією сервера. Для QA звідси готовий парний тест: значення точно в ліміт і на один символ більше — і дивимось, відмова це чи мовчазне різання. Тихе обрізання — гірший сценарій, бо дані псуються без жодного сигналу.

Чому в MySQL кодування utf8 може зіпсувати emoji і що з цим робити?

Пастка в тому, що кодування з назвою utf8 у MySQL — не справжній повний UTF-8: воно вміщає максимум 3 байти на символ, тож emoji й частина рідкісних символів у нього не влазять. Повний Unicode дає лише utf8mb4 (4 байти на символ). На практиці це виглядає так: у нікнейм вводять 😀, а після збереження читають ???? — або вставка взагалі завершується помилкою. Стандарт де-факто — саме UTF-8, який уміє будь-який символ Unicode, тому правильна конфігурація стовпця — utf8mb4. Для тестувальника це готовий тест-кейс на будь-яке текстове поле: emoji, ієрогліфи, символи з різних алфавітів, комбіновані діакритики.

Яка різниця між TIMESTAMP with time zone і without time zone?

Це один із найпідступніших багів «тест падає лише вночі». У PostgreSQL TIMESTAMPTZ (with time zone) фіксує абсолютний момент часу: на вході переводить значення із зони сесії в UTC, на виході — назад у зону сесії. А TIMESTAMP (without time zone) нічого не конвертує — тримає «настінний час» так, як записали, тож клієнти з різних зон бачать однакові цифри, які насправді означають різні моменти. Правило вибору: усе, що фіксує момент події (коли щось сталося), зберігаємо з таймзоною; без таймзони лишаємо хіба абстрактний «настінний» час на кшталт розкладу «відчиняємось о 09:00». Більшість «часових» багів — це або збереження без таймзони, або різні зони на середовищах.

Чому TIMESTAMP означає різні речі в PostgreSQL і MySQL?

Назви збивають з пантелику. У PostgreSQL TIMESTAMP без уточнення — це час без таймзони («настінний»), а абсолютний момент дає окремий TIMESTAMPTZ. У MySQL навпаки: тип TIMESTAMP зберігається в UTC і конвертується під зону сесії (але з вузьким діапазоном приблизно 1970–2038), а буквальний час без зони дає DATETIME. Тобто однакове слово TIMESTAMP в цих СУБД означає протилежні речі. Це типове діалектне питання, і плутанина тут — джерело багів при міграції коду чи схеми між СУБД, а також при читанні документації не тієї бази.

Які знаєте обмеження цілісності?

Обмеження (constraints) — правила, вшиті в саму таблицю: їх не обійде жоден запит, хоч би що робив застосунок. NOT NULL вимагає значення в кожному рядку. UNIQUE забороняє повтори. CHECK навішує на рядок довільну булеву умову — скажімо, CHECK (amount >= 0). DEFAULT підставляє значення, коли його не передали (строго кажучи, це правило заповнення, а не обмеження). FOREIGN KEY тримає посилальну цілісність: значення мусить існувати в сусідній таблиці. Сильний кандидат додасть, що PRIMARY KEY — це не окреме обмеження, а комбінація NOT NULL + UNIQUE, і згадає пастку UNIQUE з NULL. Для тестувальника цей перелік — готовий список вимог, які хтось уже сформулював у схемі.

Чому UNIQUE-обмеження на email може не завадити кільком порожнім значенням?

Це улюблена пастка на співбесіді. За стандартом SQL NULL означає «невідомо», а не конкретне значення, тому два NULL не вважаються рівними одне одному. Наслідок: UNIQUE-стовпець зазвичай дозволяє скільки завгодно рядків із NULL — вони не рахуються дублікатами. На практиці UNIQUE на email спокійно пропустить хоч три, хоч тридцять рядків із порожнім email. Якщо за вимогою email мусить бути в кожного, самого UNIQUE замало — потрібне ще NOT NULL. Для QA це прямий негативний тест: перевірити, чи справді система забороняє порожній email там, де він обов'язковий, а не лише дублікати заповнених.

Чому PRIMARY KEY ніколи не буває NULL і завжди унікальний?

Тому що PRIMARY KEY — не самостійне обмеження, а комбінація NOT NULL + UNIQUE з деякими додатковими гарантіями (таблиця може мати лише один первинний ключ, і на нього зазвичай спираються зовнішні ключі). Складова NOT NULL не дає ключу бути порожнім, а UNIQUE — повторюватися. Тому первинний ключ однозначно ідентифікує рядок: за його значенням завжди можна знайти рівно один запис або жодного. Це відрізняє його від звичайного UNIQUE-стовпця, який, як щойно зазначено, кілька NULL пропускає.

Що робить FOREIGN KEY і від чого він захищає?

Зовнішній ключ (foreign key) забезпечує посилальну цілісність (referential integrity): значення в стовпці мусить існувати в іншій таблиці. Наприклад, user_id у таблиці замовлень із REFERENCES users(id) не дасть створити замовлення на неіснуючого користувача — база відхилить вставку. Крім того, залежно від правил ON DELETE, зовнішній ключ керує каскадами: що робити з замовленнями, коли видаляють користувача (заборонити, обнулити чи видалити разом). Для QA це і захист від «сиріт» (рядків, що посилаються в нікуди), і джерело перевірок: спробувати створити запис із посиланням на неіснуючий ключ і переконатися, що база відмовляє.

Чому додавання NOT NULL стовпця міграцією може впасти і як це роблять правильно?

Класична пастка міграцій: додати стовпець одразу NOT NULL без DEFAULT до таблиці, у якій уже є рядки. Наявним рядкам нізвідки взяти значення для нового обов'язкового стовпця, тож у PostgreSQL така команда одразу падає (а MySQL без strict mode може мовчки заповнити рядки неявним дефолтом — що по-своєму гірше). Правильно це роблять у два-три кроки: додати стовпець nullable, заповнити його значеннями для наявних рядків, а вже потім навісити NOT NULL. Це частина патерну expand-contract, коли схему розширюють сумісно, мігрують дані й лише потім прибирають старе. Для QA це означає, що міграцію треба перевіряти не на порожній таблиці, а на реалістичному наборі даних, інакше падіння спливе вже на проді.

Обмеження — це відповідальність бекенду. Що тут узагалі тестувати QA?

Це перевірка на зрілість. Ключова ідея: обмеження в базі — це теж вимоги, і перевіряти їх треба з двох боків. Перший бік — що обмеження справді працює: спроба вставити NULL у NOT NULL, дублікат у UNIQUE, від'ємну суму проти CHECK, посилання на неіснуючий запис проти FK має бути відхилена; якщо база пропустила те, що не мала, — дані вже брудні. Другий бік — що застосунок реагує на порушення чемно: коли обмеження спрацьовує, користувачу має прилетіти осмислена відповідь — 400 чи 409 на рівні API, — а не 500 із сирим текстом SQL-помилки чи білий екран. Сильна відповідь згадає й шари захисту (UI → API → БД), і баги на стику шарів: UI дозволяє довший рядок, ніж уміщає VARCHAR, API приймає статус, якого немає в ENUM.

Тест шле дані, що порушують обмеження, а сервер відповідає 500. Це очікувано?

Ні — це щонайменше «півбага» бекенду. Обмеження справді спрацювало й відхилило погані дані, це добре. Але добре спроєктований застосунок мусить перехопити помилку бази й повернути свідому помилку клієнта — 400 чи 409 (наприклад, 409 Conflict на дублікат унікального значення), а не падати з необробленим винятком і віддавати 500 з голим текстом SQL-помилки. 500 тут означає, що якийсь шар коду не обробив передбачувану ситуацію. Фіксувати варто обидві складові: тригер прийшов від даних, але реакція сервера — окремий дефект. Саме тому негативні тести на порушення обмежень цінні: вони перевіряють не лише що база відмовляє, а й що застосунок робить це контрольовано.

Що варто знати про ENUM і JSONB з погляду ризиків для даних?

ENUM — фіксований набір допустимих рядкових значень, наприклад статус замовлення ('new', 'paid', 'shipped'). Пастка: додати нове значення — це ALTER, тобто окрема міграція; якщо в статуси додали refunded, а тип не оновили, вставка такого статусу впаде — і це очікувано, а не баг. JSONB (PostgreSQL; у MySQL близький JSON) зберігає структурований JSON, який можна індексувати й запитувати всередині, але саме його гнучкість — це клас багів: усередину можна покласти будь-яку структуру, і база не перевіряє її форму так, як перевіряє звичайні стовпці. Зникле поле чи змінений тип усередині JSON база пропустить мовчки, тож валідація вмісту лягає на застосунок і на тести.

Три кейси, у яких вибір типу й обмеження вирішує, зелений тест чи брудні дані: рев'ю CREATE TABLE з таблицею рішень «який тип обрати й чому», API-тести на межі типів (довжина, emoji, точність грошей) і негативні тести на самі обмеження з перевіркою, що застосунок відповідає чемно, а не 500.

Кейс 1. Рев'ю CREATE TABLE: де тут майбутні баги

Прийшла міграція з новою таблицею платежів. Її варто читати як список вимог — кожен стовпець це рішення, яке або захистить дані, або породить дефект.

CREATE TABLE payments (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id     BIGINT NOT NULL REFERENCES users(id),
    amount      FLOAT NOT NULL,                 -- підозра №1
    currency    VARCHAR(10),                    -- підозра №2
    note        VARCHAR(50),                    -- підозра №3
    status      VARCHAR(20) NOT NULL DEFAULT 'new',
    paid_at     TIMESTAMP                       -- підозра №4
);

Таблиця рішень — що не так і чим це загрожує:

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

• FLOAT для грошей — найдорожча помилка. На одній операції похибка мікроскопічна, але платежі сумуються й множаться на курси; шукати потім розбіжність на цент у звіті — окреме полювання. Правило категоричне: гроші, ціни, курси — тільки NUMERIC/DECIMAL.
• TIMESTAMP без таймзони на paid_at. Момент оплати — це подія, їй потрібен абсолютний час (TIMESTAMPTZ). Інакше «оплачено о 23:30 за Києвом» комусь за океаном покажеться завтрашнім числом.
• Стовпець без обмеження — це дозвіл на сміття. status без CHECK чи ENUM прийме будь-який рядок; currency без NOT NULL пропустить порожнє. Кожне пропущене обмеження — вимога, яку ніхто не стереже.
• user_id тут якраз зроблено правильно: NOT NULL REFERENCES users(id) не дасть створити платіж на неіснуючого користувача. Це зразок, з яким варто звіряти решту стовпців.

Кейс 2. API-тести на межі типів

Межі типів — це вимоги, які найзручніше перевіряти через API, де видно код відповіді й тіло. Три класичні межі: довжина VARCHAR, Unicode/emoji й точність грошей.

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

test('рядок на межі проходить, задовгий — відхиляється, а не мовчки ріжеться', async ({ request }) => {
  const atLimit = 'x'.repeat(50);        // рівно VARCHAR(50)
  const overLimit = 'x'.repeat(51);      // на символ довше

  const ok = await request.post('/api/payments', { data: { note: atLimit, amount: '10.00' } });
  expect(ok.status()).toBe(201);

  const tooLong = await request.post('/api/payments', { data: { note: overLimit, amount: '10.00' } });
  // очікуємо свідому помилку валідації, НЕ 201 з тихо обрізаним note
  expect(tooLong.status()).toBe(400);
});

test('emoji в текстовому полі зберігається без спотворення', async ({ request }) => {
  const nickname = 'qa😀🚀'; // потрібне utf8mb4, якщо база на MySQL
  const created = await request.post('/api/users', { data: { name: nickname, email: `u.${Date.now()}@ex.com` } });
  expect(created.status()).toBe(201);

  const id = (await created.json()).id;
  const back = await (await request.get(`/api/users/${id}`)).json();
  // класична пастка MySQL utf8: emoji перетворюється на ???? або губиться
  expect(back.name).toBe(nickname);
});

Точність грошей перевіряють на операції, де похибка FLOAT виплила б: сума копійок або множення на курс.

test('сума платежів сходиться до копійки', async ({ request }) => {
  // 0.1 + 0.2 у FLOAT дало б 0.30000000000000004
  await request.post('/api/payments', { data: { amount: '0.10' } });
  await request.post('/api/payments', { data: { amount: '0.20' } });

  const total = (await (await request.get('/api/payments/total')).json()).total;
  expect(total).toBe('0.30'); // точний тип → рівно 0.30, без хвоста
});

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

• Перевищення довжини перевіряють парою «на межі + на символ довше». Головна небезпека не помилка, а тихе обрізання: якщо задовгий note повертає 201, а в базі лежить урізаний рядок — дані зіпсовано без сигналу. Асерт має ловити саме відмову, а не мовчазний успіх.
• Emoji — дешевий детектор кодування. Один символ 😀 через повний цикл (записали → прочитали) показує, чи стоїть utf8mb4. Розширте до ієрогліфів і комбінованих діакритиків для важливих полів.
• Гроші звіряйте як рядок, а не як число з плаваючою комою. Порівняння 0.30 рядком не дає похибці IEEE 754 просочитися в сам тест; якщо API віддає гроші числом типу number, це вже привід до розмови про контракт.

Кейс 3. Обмеження як вимоги: порушити й перевірити реакцію

Кожне обмеження в схемі — готовий негативний тест. Перевіряємо і що база відхиляє погані дані, і що застосунок віддає чемну помилку, а не 500 з голим SQL.

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

test('дублікат email → 409, а не 500', async ({ request }) => {
  const email = `qa.${Date.now()}@example.com`;
  await request.post('/api/users', { data: { email, name: 'First' } });

  const dup = await request.post('/api/users', { data: { email, name: 'Second' } });
  expect(dup.status()).toBe(409); // UNIQUE спрацював як бізнес-правило, оброблено чемно
});

test('порушення CHECK (від\'ємна сума) → 400, не 500', async ({ request }) => {
  const res = await request.post('/api/payments', { data: { amount: '-5.00' } });
  expect(res.status()).toBe(400); // CHECK (amount >= 0) відхилив, застосунок пояснив чому
});

test('FK: платіж на неіснуючого користувача відхиляється', async ({ request }) => {
  const res = await request.post('/api/payments', { data: { user_id: 999999999, amount: '10.00' } });
  // зовнішній ключ не дасть «сироту»; очікуємо 400/409, а не тихе створення й не 500
  expect([400, 409]).toContain(res.status());
});

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

• Дублікат UNIQUE — це 409 Conflict, а не 500. Обмеження мусить спрацювати, але помилку бази застосунок зобов'язаний перехопити й перекласти на зрозумілий код клієнта. 500 з текстом SQL-помилки — окремий дефект бекенду, який фіксують поряд із самим фактом порушення.
• CHECK перевіряють саме забороненим значенням. Від'ємна сума проти CHECK (amount >= 0) має дати 400; якщо база пропустила її — це баг цілісності, і дані вже брудні.
• FK ловить «сиріт» на вході. Спроба послатися на неіснуючий user_id має бути відхилена; тихе створення означало б, що зовнішнього ключа насправді немає або він вимкнений.
• Пастка UNIQUE з NULL. Якщо email обов'язковий, окремо перевірте, що система забороняє порожній email: саме лише UNIQUE кілька NULL пропустить, бо NULL не дорівнює NULL. Обов'язковість гарантує NOT NULL, а не унікальність.

Підмови SQL і DDL

• Можу перелічити чотири категорії SQL: DDL (структура), DML (дані), DCL (права), TCL (транзакції) — з парою прикладів кожної.
• Знаю, що DDL у PostgreSQL транзакційний (можна відкотити в BEGIN ... ROLLBACK), а в MySQL кожна DDL-команда робить неявний commit.
• Розумію призначення CREATE / ALTER / DROP і що ALTER — найнебезпечніша операція на живій базі, бо зачіпає реальні дані.

Типи даних: числа

• Можу пояснити різницю точних (NUMERIC/DECIMAL, INTEGER) і наближених (REAL/FLOAT/DOUBLE) типів: десяткове зберігання проти двійкового IEEE 754.
• Розумію, чому гроші не тримають у FLOAT: двійкова похибка (0.1 + 0.2 не дорівнює 0.3) накопичується на сумах і множеннях.
• Знаю, що гроші, ціни й курси — тільки в NUMERIC/DECIMAL, і що означає NUMERIC(12, 2) (точність і масштаб).

Типи даних: текст і кодування

• Знаю різницю CHAR / VARCHAR(n) / TEXT і що вона залежить від СУБД (у PostgreSQL VARCHAR(n) ≈ TEXT з перевіркою довжини).
• Можу пояснити поведінку при перевищенні довжини: PostgreSQL відхиляє, MySQL без strict mode історично міг мовчки обрізати.
• Розумію пастку MySQL: utf8 неповне (3 байти) і не вміщає emoji; для повного Unicode потрібне utf8mb4.

Типи даних: дата й час

• Розумію різницю TIMESTAMP with time zone (абсолютний момент у UTC) і without time zone («настінний час» без конвертації).
• Знаю правило: для подій (коли щось сталося) — тип із таймзоною; чистий TIMESTAMP — для абстрактних «настінних» значень.
• Пам'ятаю, що TIMESTAMP у PostgreSQL і MySQL означає різні речі (у MySQL TIMESTAMP — UTC із діапазоном ~1970–2038, а DATETIME — без зони).

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

• Можу перелічити NOT NULL, UNIQUE, CHECK, DEFAULT, FOREIGN KEY і пояснити призначення кожного.
• Знаю пастку UNIQUE з NULL: кілька рядків із NULL не вважаються дублікатами, тож UNIQUE на email не гарантує обов'язковості.
• Розумію, що PRIMARY KEY — це NOT NULL + UNIQUE, тому первинний ключ ніколи не NULL і завжди унікальний.
• Можу пояснити посилальну цілісність через FOREIGN KEY: значення мусить існувати в іншій таблиці, ON DELETE керує каскадами.
• Знаю, чому ADD COLUMN ... NOT NULL без DEFAULT на непорожній таблиці в PostgreSQL впаде, і що це роблять кроками (expand-contract).

Що тестує QA

• Розумію, що обмеження в базі — це вимоги, і перевіряю їх з двох боків: що база відхиляє погані дані і що застосунок реагує чемною помилкою (400/409, а не 500 з голим SQL).
• Знаю шари захисту UI → API → БД і полюю на баги на стику (UI дозволяє довше за VARCHAR, API приймає статус поза ENUM).
• Перевіряю межі типів: довжина рядка (обрізає чи відхиляє), точність грошей після множень і сум, Unicode/emoji, таймзони між середовищами.