Реляційна модель
Навіщо це
Section titled “Навіщо це”Потрібен звіт: скільки покупців «Крамниці» живе в Києві й скільки ні. Запити settlement_id = 703448 і settlement_id <> 703448 повертають 429 і 4113. Разом 4542, а покупців 5000. Ще 458 не потрапили в жоден набір: у них settlement_id IS NULL. Помилки немає: умова й її заперечення просто не ділять рядки на два набори.
У тій самій таблиці є й інша проблема. Аналітик вважає email ідентифікатором покупця, але імпорт зі старої платформи залишив 21 групу з 43 рядків, що різняться лише регістром. Тому CREATE UNIQUE INDEX ON customers (lower(email)) не виконується. Первинний ключ у customers є, а покупець усе одно може існувати двічі.
Обидві поломки мають одне джерело. SQL запозичив ідеї з реляційної моделі й у кількох місцях свідомо відійшов від неї. Чому модель перемогла, розказано в модулі 1.
Передумови. Досить уміти прочитати простий SELECT. Запити докладно розібрано в модулі 3, і він спирається на цей модуль. Таблиці «Крамниці» описано на сторінці Датасет; числа з датасету в модулі виміряно на розмірі small.
Таблиця, ключі, домен
Section titled “Таблиця, ключі, домен”Стаття Кодда A Relational Model of Data for Large Shared Data Banks (1970) запропонувала описувати дані математичним поняттям, а не вказівниками між записами. Так програма не залежить від розкладки даних на диску. Теорія називає таблицю відношенням, рядок кортежем, а колонку атрибутом. Далі пишемо «таблиця», «рядок», «колонка».
Таблиця має заголовок (колонки, кожна зі своїм доменом, тобто множиною допустимих значень) і тіло (множину рядків). У кожного рядка по одному значенню з домену кожної колонки.
SQL послаблює три властивості. Рядки не мають порядку: «першого рядка таблиці» в моделі немає. Повторів немає: множина не містить однакових елементів. Колонки мають імена й не мають порядку: значення знаходять за назвою, а не за позицією.
Домен суворіший за тип. У моделі порівнюють лише значення одного домену. А customer_id і product_id мають однаковий тип integer, тому orders.customer_id = products.product_id SQL виконає без зауважень.
Ключ відповідає на питання: за чим знайти один рядок і ніякий інший. Потенційний ключ — мінімальний набір колонок, значення яких різні в усіх рядках. З нього не можна прибрати жодну колонку без втрати унікальності. Один потенційний ключ обирають первинним, решта лишаються потенційними.
У «Крамниці» ключі різні. У order_items складений (order_id, line_no), бо окремо жодна колонка позицію не визначає. У shipments первинний ключ order_id водночас посилається на orders: одне відправлення на замовлення. У customers ключ customer_id видає сама база.
Ключ — властивість схеми: гарантія для всіх станів, які база колись прийме. Те, що значення зараз не повторюються, про ключ нічого не каже. Два кандидати:
candidate | total_rows | distinct_values--------------------------------------+------------+----------------- customers · email | 5000 | 5000 customers · lower(email) | 5000 | 4978 order_items · (order_id, product_id) | 39504 | 39504Точний email різний в усіх 5000 рядків, але lower(email) уже ні: 22 зайві рядки від імпорту. До того ж пошту змінюють, а видалений обліковий запис отримує deleted-<id>@anon.example. Значення, на яке посилаються інші таблиці, так перезаписувати не можна.
Пара (order_id, product_id) унікальна в усіх 39 504 рядках, але це збіг даних. Завтра той самий товар може потрапити в замовлення двома позиціями з різною знижкою, і схема не має цьому заважати.
Звідси різниця між природним і сурогатним ключем. Природний береться з предметної області: артикул sku, код країни, податковий номер. Сурогатний видає база: customer_id як GENERATED ALWAYS AS IDENTITY. Він не змінюється й дешевший в індексах та зовнішніх ключах, але унікальності предметної області не дає: двом рядкам з однаковою поштою він не завадив. Тому його доповнюють UNIQUE на природний потенційний ключ, де такий справді існує. Вибір між identity і UUID розібрано в модулі 5.
Зовнішній ключ — колонка або набір колонок, значення яких мусять збігатися зі значенням потенційного ключа іншої (або тієї самої) таблиці. orders.customer_id посилається на customers, а categories.parent_id на categories: так записують дерево. Зовнішній ключ може бути порожнім: customers.settlement_id допускає NULL («місто не вказане»), і перевірку тоді пропускають.
NULL і тризначна логіка
Section titled “NULL і тризначна логіка”NULL — не значення, а позначка, що значення немає. Причин кілька, і схема їх не розрізняє. birth_date IS NULL означає «не знаємо», payments.paid_at IS NULL — «ще не сплатили», orders.promo_code IS NULL — «промокод не застосовували». Про зміст кожного NULL домовляються в проєкті.
SQL ввів для таких випадків третє значення істинності, unknown. Порівняння, в якому є NULL, дає unknown, навіть коли порівнюють NULL із NULL. Зв’язки AND, OR, NOT мають розширені таблиці істинності:
Правило відновлюється міркуванням: unknown означає «може бути і так, і так». false AND unknown хибне за будь-якого другого операнда. А true AND unknown від нього залежить і лишається невідомим. Усі дев’ять пар:
Порожня клітинка у виводі psql означає NULL. Головний наслідок: WHERE, ON і HAVING залишають рядок лише тоді, коли умова істинна, а false і unknown відкидають однаково. Тому умова й її заперечення не ділять рядки навпіл:
kyiv | not_kyiv | not_kyiv_distinct | no_settlement | total------+----------+-------------------+---------------+------- 429 | 4113 | 4571 | 458 | 5000IS NULL і IS NOT NULL завжди дають true або false. IS DISTINCT FROM порівнює з урахуванням порожніх значень: два NULL для нього однакові, а NULL і будь-яке значення різні. Тому в третій колонці 4571 = 4113 + 458. IS NOT DISTINCT FROM замінює =, коли NULL = NULL має дати true. MySQL для цього має оператор <=>, SQLite — IS.
Усередині SQL два NULL то різні, то однакові, залежно від конструкції:
| Контекст | Два NULL |
|---|---|
=, <>, < |
unknown |
DISTINCT, GROUP BY, UNION, INTERSECT, EXCEPT |
однакові: лишається один рядок |
UNIQUE |
різні: у колонці можна мати багато NULL (UNIQUE NULLS NOT DISTINCT у PostgreSQL 15 і новіших змінює це) |
ORDER BY |
у PostgreSQL за зростанням NULL іде останнім, NULLS FIRST міняє |
Агрегати, NOT IN із NULL у підзапиті й LEFT JOIN з умовою у WHERE розібрано в модулі 3.
Обмеження цілісності
Section titled “Обмеження цілісності”Обмеження цілісності — правило, якому має відповідати кожен допустимий стан бази. База відхиляє зміну, що його порушує. Обмеження бувають на домен (тип, NOT NULL, CHECK), на сутність (первинний ключ унікальний і не NULL), на посилання (кожне значення зовнішнього ключа існує в батьківській таблиці). Для потенційних ключів, що не стали первинними, є UNIQUE.
Стандарт SQL має CREATE ASSERTION для правил між кількома таблицями, наприклад «total_amount дорівнює сумі позицій замовлення», але PostgreSQL його не реалізує. Такі правила лежать на тригерах (модуль 6) і на застосунку з правильною ізоляцією (модуль 10).
CHECK має тонкість із NULL: він відхиляє рядок лише тоді, коли умова хибна, а «невідомо» пропускає.
CREATE TEMP TABLE lot (price numeric CHECK (price > 0), sku text UNIQUE);INSERT INTO lot VALUES (NULL, NULL), (NULL, NULL), (5, 'a');SELECT count(*) AS all_rows, count(*) FILTER (WHERE price > 0) AS passes_where FROM lot; all_rows | passes_where----------+-------------- 3 | 1Два рядки з price = NULL потрапили в таблицю попри CHECK (price > 0), а WHERE price > 0 їх відкинув. Та сама умова дала протилежні вердикти: CHECK пропускає все, крім хибного, WHERE пропускає лише істинне. Два NULL у sku теж вмістилися: для UNIQUE порожні значення різні. Заборонити порожнє може лише явне NOT NULL.
Реляційна алгебра
Section titled “Реляційна алгебра”Реляційна алгебра — набір операцій, що беруть таблиці й повертають таблиці. Результат теж таблиця, тож операції вкладаються одна в одну, як підзапити. Планувальник запитів (модуль 9) будує з них дерево й замінює рівнозначним, але дешевшим.
| Операція | Запис | SQL |
|---|---|---|
| вибірка (selection) | σумова(R) | WHERE |
| проєкція (projection) | πколонки(R) | список у SELECT, з DISTINCT |
| перейменування (rename) | ρ(R) | AS |
| декартів добуток | R × S | CROSS JOIN |
| з’єднання таблиць (join) | R ⋈умова S | JOIN … ON |
| об’єднання | R ∪ S | UNION |
| різниця | R − S | EXCEPT |
| перетин | R ∩ S | INTERSECT |
Мінімум — вибірка, проєкція, перейменування, добуток, об’єднання й різниця. JOIN виражається вибіркою над добутком, а перетин формулою R − (R − S).
Чотири операції в одному запиті: великі міста й їхні області, π(σ(settlements ⋈ regions)).
-- π settlement, region ( σ population > 900000 ( settlements ⋈ regions ) )SELECT DISTINCT s.name AS settlement, r.name AS regionFROM settlements AS sJOIN regions AS r ON r.region_id = s.region_idWHERE s.population > 900000ORDER BY settlement; settlement | region------------+-------------------------- Дніпро | Дніпропетровська область Донецьк | Донецька область Київ | м. Київ Одеса | Одеська область Харків | Харківська областьORDER BY в алгебру не входить: таблиця в моделі не має порядку, сортування лише для показу. Перейменування стає обов’язковим, коли таблицю з’єднують із нею самою. Категорія й її батько лежать в одній таблиці, тож запит починається з FROM categories AS c JOIN categories AS p ON p.category_id = c.parent_id, і без AS його не написати.
Операнди операцій над множинами мають однакову структуру: стільки ж колонок із сумісними типами. Покупці, які замовляли, і покупці, які писали відгуки:
SELECT 'замовляли або писали' AS op, count(*) AS customers FROM ( SELECT customer_id FROM orders UNION SELECT customer_id FROM reviews) AS tUNION ALLSELECT 'замовляли і писали', count(*) FROM ( SELECT customer_id FROM orders INTERSECT SELECT customer_id FROM reviews) AS tUNION ALLSELECT 'замовляли, але не писали', count(*) FROM ( SELECT customer_id FROM orders EXCEPT SELECT customer_id FROM reviews) AS tUNION ALLSELECT 'писали, але не замовляли', count(*) FROM ( SELECT customer_id FROM reviews EXCEPT SELECT customer_id FROM orders) AS t; op | customers--------------------------+----------- замовляли або писали | 3211 замовляли і писали | 1537 замовляли, але не писали | 1643 писали, але не замовляли | 31Числа сходяться: 1537 + 1643 + 31 = 3211. Ті 31 покупець залишили відгуки зі старої платформи, де order_id порожній.
В одному місці SQL копіює алгебру надто буквально. Natural join зв’язує таблиці за всіма колонками з однаковими іменами, і NATURAL JOIN робить те саме:
SELECT (SELECT count(*) FROM orders NATURAL JOIN customers) AS natural_join, (SELECT count(*) FROM orders JOIN customers USING (customer_id)) AS join_using; natural_join | join_using--------------+------------ 0 | 22030Крім customer_id, в обох таблицях є status: замовлення бувають delivered, покупці active, спільних значень немає, і результат порожній. Нова колонка з випадково збіжною назвою змінить відповідь без жодного сигналу. Тому таблиці з’єднують явно: ON або USING.
Множини проти мультимножин
Section titled “Множини проти мультимножин”Модель оперує множинами, SQL — мультимножинами (bags): рядок може повторюватися. Це найбільший відступ, і він свідомий. Без повторів агрегати були б хибними: sum(unit_price) по двох однакових позиціях мусить додати обидві. До того ж прибирання повторів коштує сортування чи хеш-таблиці (модуль 9), і база не платить за нього без запиту.
| Модель | SQL | |
|---|---|---|
| повтори рядків | неможливі | можливі, DISTINCT їх прибирає |
| порядок рядків | немає | немає без ORDER BY |
| порядок колонок | немає, є імена | є: SELECT *, INSERT без списку, UNION |
Таблиця з первинним ключем повторів не має: у «Крамниці» 16 таблиць і 16 первинних ключів. Повтори з’являються в результатах, бо їх породжують проєкція й JOIN. У pickup_points 1692 рядки, але різних пар (carrier, kind) лише три:
bag_rows | set_rows | union_all | union_distinct----------+----------+-----------+---------------- 1692 | 3 | 25113 | 3211В алгебрі π дала б три рядки, SQL без DISTINCT повертає всі 1692. UNION ALL складає мультимножини (22 030 замовлень і 3083 відгуки), а UNION, як в алгебрі, лишає різні значення. Коли гілки не перетинаються, UNION ALL і швидший, і правильний: UNION без потреби додає дорогий крок прибирання повторів. INTERSECT ALL і EXCEPT ALL рахують кратності: для значень (1, 1, 1, 2) і (1, 1, 3) перший дає 1, 1, другий 1, 2.
Порядок колонок у SQL, на відміну від моделі, має значення. UNION зіставляє колонки за позицією, а не за іменем:
SELECT 1 AS a, 2 AS b UNION SELECT 2 AS b, 1 AS a; a | b---+--- 1 | 2 2 | 1За іменами вийшов би один рядок (a = 1, b = 2). SQL повернув два рядки з іменами першої гілки. Коли порядок у гілках розходиться, результат мовчки хибний.
Як це насправді
Section titled “Як це насправді”Ключі й обмеження лежать у системних каталогах. \d order_items показує їх разом із таблицею (вивід скорочено):
Indexes: "order_items_pkey" PRIMARY KEY, btree (order_id, line_no)...Foreign-key constraints: "order_items_order_id_fkey" FOREIGN KEY (order_id) REFERENCES orders(order_id) "order_items_product_id_fkey" FOREIGN KEY (product_id) REFERENCES products(product_id)Індекс лише один, від первинного ключа. На product_id його немає: PostgreSQL не створює індекс для зовнішнього ключа сам, на відміну від InnoDB. Тому JOIN за product_id читає таблицю повністю. Індекси додають у модулі 8 і в L4.
Стандартний вигляд дає information_schema.table_constraints, а повніший, зі змістом кожного обмеження, pg_constraint:
SELECT conname, contype, pg_get_constraintdef(oid) AS definitionFROM pg_constraintWHERE conrelid = 'order_items'::regclass AND contype IN ('p', 'f')ORDER BY contype DESC, conname; conname | contype | definition-----------------------------+---------+---------------------------------------------------------- order_items_pkey | p | PRIMARY KEY (order_id, line_no) order_items_order_id_fkey | f | FOREIGN KEY (order_id) REFERENCES orders(order_id) order_items_product_id_fkey | f | FOREIGN KEY (product_id) REFERENCES products(product_id)information_schema показує NOT NULL як CHECK: для order_items там десять CHECK, чотири справжні й шість NOT NULL. З PostgreSQL 18 NOT NULL є й у pg_constraint (contype = 'n'), тож без фільтра там 13 рядків, а не 7.
Порушення кожного обмеження дає власний текст і код SQLSTATE класу 23, за яким застосунок відрізняє такі помилки від решти. Ось вони з PostgreSQL 18.6, транзакції відкочено:
-- зовнішній ключ, 23503INSERT INTO inventory (product_id, quantity, updated_at) VALUES (999999, 1, now());ERROR: insert or update on table "inventory" violates foreign key constraint "inventory_product_id_fkey"DETAIL: Key (product_id)=(999999) is not present in table "products".
-- зовнішній ключ з боку батька, 23503DELETE FROM customers WHERE customer_id = 1;ERROR: update or delete on table "customers" violates foreign key constraint "reviews_customer_id_fkey" on table "reviews"DETAIL: Key (customer_id)=(1) is still referenced from table "reviews".
-- CHECK, 23514UPDATE products SET price = -5 WHERE product_id = 1;ERROR: new row for relation "products" violates check constraint "products_price_check"Коди знято з \set VERBOSITY verbose. Дублікат первинного ключа дає 23505 (duplicate key value violates unique constraint), NOT NULL — 23502. DETAIL містить значення ключа, а для CHECK і NOT NULL цілий рядок. Це можуть бути персональні дані, тож користувачеві показують власне повідомлення за кодом, а не сирий текст помилки.
Алгебра видна й у плані запиту. Умова c.settlement_id = 703448 записана після JOIN, але планувальник виконує її на Seq Scan on customers, ще до Hash Join:
EXPLAIN SELECT o.order_idFROM orders AS o JOIN customers AS c ON c.customer_id = o.customer_idWHERE c.settlement_id = 703448; QUERY PLAN--------------------------------------------------------------------------- Hash Join (cost=153.86..704.04 rows=1890 width=8) Hash Cond: (o.customer_id = c.customer_id) -> Seq Scan on orders o (cost=0.00..492.30 rows=22030 width=16) -> Hash (cost=148.50..148.50 rows=429 width=8) -> Seq Scan on customers c (cost=0.00..148.50 rows=429 width=8) Filter: (settlement_id = 703448)Це тотожність σ(R ⋈ S) = R ⋈ σ(S), коли умова стосується лише S: у хеш-таблицю потрапило 429 рядків із 5000. Вивід із PostgreSQL 18.6, у пісочниці вартості можуть відрізнятися.
Типові помилки розуміння
Section titled “Типові помилки розуміння”«NULL — це порожній рядок або нуль». Вони значення, а NULL — відсутність: '' = '' істинно, NULL = NULL невідомо.
«x = 1 і x <> 1 разом повертають усі рядки». Рядки з NULL не потрапляють у жоден набір: в обох умовах вони невідомі. Повною ця пара буває лише для колонок із NOT NULL.
«Сурогатний первинний ключ уже гарантує унікальність». Він гарантує лише свою. Унікальність пошти чи артикула потребує окремого UNIQUE, і без нього дублі накопичуються, як у customers.
«Значення унікальні, отже це ключ». Ключ — властивість схеми: він має витримувати всі стани, які бізнес дозволяє. Унікальність у поточних даних завтра може зникнути.
Перевір себе
Лабораторна
Section titled “Лабораторна”L1 перевіряє тризначну логіку, IS DISTINCT FROM і різницю між UNION та UNION ALL на запитах із пастками. L2 вимагає оголосити ключі й обмеження цілісності у власній схемі й показати, що вони відхиляють некоректні дані.
Джерела
Section titled “Джерела”- E. F. Codd, A Relational Model of Data for Large Shared Data Banks, Communications of the ACM, 13(6), 1970.
- PostgreSQL 18, документація: Constraints, Comparison Functions and Operators (
IS DISTINCT FROM), Combining Queries, pg_constraint, Error Codes. - A. Silberschatz, H. Korth, S. Sudarshan, Database System Concepts, 7-ме вид.: реляційна модель і реляційна алгебра.
- C. J. Date, SQL and Relational Theory: про відступи SQL від моделі.