Что такое база данных и таблицы

← назад к разделу

Покупатель пишет в поддержку: «Я оплатил, а заказ висит неоплаченным», а в магазине у заказа написано «Оплачен». По экрану спор не решить: интерфейс показывает свою подпись, а не значение из базы. В базе стоит PAID — или PENDING_PAYMENT, и тогда прав покупатель.

Таблица: строки и столбцы

Весь цикл работает с учебным интернет-магазином в песочнице. Две его таблицы встретятся в каждом примере — customer, покупатели:

И orders, заказы:

Столбцы объявлены с типами: total_amount здесь numeric(15,2), и слово база в него не примет. Запросы цикла выполняются по этим таблицам по-настоящему, кнопкой «Запустить» под примером:

живой пример

SELECT * FROM customer;

Девять строк. Разбор SELECT — в следующей статье; дальше по циклу универсальный SQL с оглядкой на PostgreSQL, в MySQL, Oracle и SQL Server примеры идут почти без правок.

Ограничения: NOT NULL, UNIQUE и проверки

Ограничение — правило, которое база проверяет при каждой записи; нарушили — записи не будет.

NOT NULL: почта покупателя в учебной базе обязательна, и отказ выглядит так:

ERROR:  null value in column "email" of relation "customer" violates not-null constraint

UNIQUE: второго покупателя с адресом a.volkova@example.com база не заведёт. «Пользователь с такой почтой уже существует» на форме регистрации — это вот этот отказ, доехавший до интерфейса:

ERROR:  duplicate key value violates unique constraint "customer_email_key"
DETAIL:  Key (email)=(a.volkova@example.com) already exists.

CHECK — проверка значения вроде «сумма заказа больше нуля»: в учебной базе таких нет, в рабочих встречаются постоянно.

У UNIQUE есть дыра, о которую спотыкаются регулярно: NULL не равен NULL, поэтому в столбце email UNIQUE без NOT NULL спокойно уживутся три строки с пустой почтой — уникальности они не нарушают. «Одно значение или ничего» даёт только пара UNIQUE + NOT NULL, а с PostgreSQL 15 ещё и UNIQUE NULLS NOT DISTINCT, где второй NULL уже отвергается как дубль.

Объявленное ограничение — гарантия: столбец NOT NULL не бывает пустым ни в одной строке, и ветку «а если пусто» писать незачем. Не объявлено — гарантии нет, сколько бы уверенно о ней ни говорили (схема и создание таблиц).

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

cus-01 ничего не рассказывает о человеке — потому и не меняется, когда тот сменит фамилию или почту. В этом смысл первичного ключа, и потому в заявке заказ называют по id, а не «заказ Волковой от вторника».

Внешний ключ — столбец с ключом строки из другой таблицы: orders.customer_id (базы с такими связями называют реляционными). Фамилия Анны лежит в одном месте, копий нет, и правка одной строки в customer меняет её во всех отчётах по заказам. Платят лишним шагом при чтении:

живой пример

SELECT id, status, total_amount FROM orders WHERE customer_id = 'cus-01';

Семь строк, от ord-01 до ord-07, имени покупателя среди них нет — за ним второй запрос:

живой пример

SELECT first_name, last_name, email FROM customer WHERE id = 'cus-01';

Склеивать такие пары в один ответ умеет JOIN (отдельная статья).

Внешний ключ бывает объявленным правилом, которое база проверяет: тогда заказ на несуществующего покупателя не создать, а покупателя с заказами не удалить. А бывает просто договорённостью — в учебной базе на customer_id правила нет, связь есть по смыслу, проверки нет.

Второе встречается чаще, чем кажется, и не от лени. Объявленный ключ стоит денег на записи: PostgreSQL заводит индекс только на тот столбец, куда ссылаются, а на ссылающийся — нет. Пока orders.customer_id без индекса, каждое удаление покупателя заставляет базу просмотреть orders целиком, чтобы убедиться, что ссылок не осталось; на большой таблице это заметно, и связь оставляют договорённостью. Вывод для читающего: «заказа без покупателя быть не может» — не аксиома, а утверждение, которое стоит проверить по схеме.

Почему одна таблица customer, а другая orders

Две школы именования: одна называет таблицу по одной её строке (customer — «карточка покупателя»), другая по содержимому (orders — «заказы»). В одной базе они уживаются: customer, orders, order_items и payments стоят рядом. Поэтому имя таблицы не угадывают, его смотрят в списке таблиц.

Регистр — тот же случай, только злее. PostgreSQL приводит незакавыченные имена к нижнему, а таблица, приехавшая переносом из другой СУБД, может быть создана как "MixedCase" — с большими буквами в каталоге. Запрос SELECT * FROM MixedCase тогда отвечает relation "mixedcase" does not exist, и имя придётся писать в кавычках всегда и везде.

Схема базы

\dt — список таблиц:

lab=# \dt
              List of relations
 Schema |       Name       | Type  |  Owner
--------+------------------+-------+----------
 lab    | customer         | table | postgres
 lab    | delivery_zones   | table | postgres
 lab    | idempotency_keys | table | postgres
 lab    | order_items      | table | postgres
 lab    | orders           | table | postgres
 lab    | outbox           | table | postgres
 lab    | payments         | table | postgres
 lab    | pickup_points    | table | postgres
 lab    | processed_events | table | postgres
 lab    | products         | table | postgres
(10 rows)

\d orders — устройство одной:

lab=# \d orders
                               Table "lab.orders"
     Column      |            Type             | Nullable
-----------------+-----------------------------+----------
 id              | character varying(36)       | not null
 customer_id     | character varying(36)       | not null
 seller_id       | character varying(36)       | not null
 status          | character varying(32)       | not null
 currency        | character varying(3)        | not null
 total_amount    | numeric(15,2)               | not null
 created_at      | timestamp without time zone | not null
 paid_at         | timestamp without time zone |
 parent_order_id | character varying(36)       |
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
    "orders_customer_created" btree (customer_id, created_at DESC)
Foreign-key constraints:
    "orders_parent_order_id_fkey" FOREIGN KEY (parent_order_id) REFERENCES orders(id)

У paid_at нет пометки not null: заказы без времени оплаты законны, и пустая ячейка тут не потеря данных (NULL и типы). orders_customer_created — индекс под «заказы такого-то покупателя, свежие сверху» (медленные запросы). В Foreign-key constraints единственная объявленная ссылка ведёт из заказа на другой заказ — это заказ-замена, а правила на customer_id нет, тот самый случай. В DBeaver то же лежит на вкладках Columns, Keys, Foreign Keys и References.

Важно, чего \dt не показывает: он перечисляет только схемы из search_path. Таблица в чужой схеме в список не попадёт, пока её не спросить явно — \dt имясхемы.*. База, которая с первого взгляда кажется маленькой, часто просто показана не вся.

Как осмотреть незнакомую базу

Карта местности, о которой шла речь, у любой базы уже есть, её надо только открыть. В psql это три команды: \dt показывает таблицы, \d orders колонки таблицы с типами, ключами и индексами, \d+ orders ещё и размер с комментариями. В DBeaver и pgAdmin то же лежит в дереве слева: таблица, колонки, ограничения, внешние ключи.

Из любого клиента, где нет команд psql, карту достают запросами к системным таблицам, они есть в каждой базе:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'orders'
ORDER BY ordinal_position;

Внешние ключи, то есть кто на кого ссылается, видны в выводе \d строками FOREIGN KEY ... REFERENCES. С них и начинают: таблица, на которую ссылаются все, обычно главная сущность системы, и от неё читают остальное. Пятнадцать минут с \d перед первым запросом экономят день догадок о том, что значит колонка status и почему в ней три разных набора значений.

Что можно и чего нельзя на боевой базе

SELECT данные не меняет ни с фильтром, ни без него; меняют INSERT, UPDATE и DELETE (изменение данных). Но «не ломает» — не то же самое, что «можно всё»:

• Доступ выдают не ко всему. На боевой базе заводят отдельного пользователя только на чтение, и видны ему не все таблицы.
• Персональные данные не выгружают. Почты, телефоны, адреса, паспорта защищены законом и правилами компании: посмотреть их при разборе обращения обычно можно, а выгрузить в файл, переслать в переписке или приложить скриншотом к заявке — почти всегда нельзя.
• Тяжёлый запрос мешает всем, и дольше, чем идёт сам. SELECT * без фильтра по таблице в сто миллионов строк заставит сервер прочитать её целиком. Хуже другое: пока запрос выполняется, его снимок держит горизонт очистки, и autovacuum не может убрать мёртвые версии строк — таблица пухнет, а разгребать это придётся уже не вам.

И то, из-за чего чаще всего переделывают выгрузки: одно слово на экране закрывает несколько значений в базе. Деньги за заказ взяты и при PAID, и при SHIPPED, DELIVERED, COMPLETED — фильтр «покажи оплаченные» по одному PAID потеряет большую часть.

Коротко

• Прежде чем опереться на «таких данных не бывает», найдите ограничение: договорённость без NOT NULL, UNIQUE или объявленного внешнего ключа гарантии не даёт, а UNIQUE без NOT NULL не даёт её и на пустых значениях.
• Имя таблицы, её регистр, обязательность столбца и наличие индекса не угадывают: \dt и \d имя отвечают за секунду — с поправкой на то, что \dt показывает не все схемы.
• Заказ в переписке называют по id — это единственное, что указывает на строку без вариантов толкования.
• На боевой базе читать можно, выгружать персональные данные и сканировать таблицу целиком — нет: цена второго остаётся в базе и после того, как запрос отработал.
• Незнакомую базу осматривают до первого запроса: \dt, \d таблица или information_schema.tables и columns; главная сущность та, на которую ссылаются внешние ключи.

Что почитать дальше

• SELECT: выбрать, отфильтровать, отсортировать — первый запрос с фильтром и сортировкой.
• JOIN: собрать данные из нескольких таблиц — пройти по внешнему ключу одним запросом.
• Схема базы данных: таблицы, ключи и представления — подробно про ограничения и создание таблиц.
• NULL и типы данных: где все спотыкаются — почему сравнение с пустой ячейкой не работает.