PostgreSQL · Урок 2
В прошлом уроке мы заметили дыру: две одинаковые строки нечем различить. Сейчас закроем её — дадим каждой строке постоянное «удостоверение личности».
Зачем это для вашей миссии: первичный ключ — это то, на чём держится вся остальная схема. Будете проектировать — без него не свяжете таблицы. Будете читать чужую базу — первое, что вы ищете в незнакомой таблице, это её ключ: он говорит, что такое «одна запись» здесь.
Вернёмся к аналогии из кода. У вас есть объект в памяти. Даже если два объекта имеют одинаковые поля, вы различаете их по ссылке — у каждого свой адрес. В таблице такой «ссылки» у строки нет: таблица — это множество, и если все столбцы совпали, строки неотличимы.
Первичный ключ — это столбец (или набор столбцов), значение которого СУБД обязуется держать уникальным и непустым. Он становится постоянным удостоверением строки: по нему всегда можно указать ровно одну запись.[1]
NULL (у каждой строки ключ есть). Формально PRIMARY KEY = UNIQUE + NOT NULL.[1]Та же таблица books, но теперь с ключевым столбцом id:
| id bigint · PK | title text | author text | year integer |
|---|---|---|---|
| 1 | Чистый код | Роберт Мартин | 2008 |
| 2 | SQL за 10 минут | Бен Форта | 2019 |
| 3 | Чистый код | Роберт Мартин | 2008 |
Строки 1 и 3 — одна и та же книга по содержанию, но это разные записи: у каждой свой id. Теперь «удали книгу с id = 3» — это однозначная команда.
Объявив столбец первичным ключом, вы поручаете СУБД следить за двумя правилами при каждой записи: значение не повторяется и не бывает пустым. Нарушить их не получится — Postgres отвергнет такой INSERT или UPDATE с ошибкой.[1] Это не «совет» и не проверка в коде приложения, которую можно забыть, — это гарантия на уровне базы, общая для всех, кто к ней пишет.
Откуда взять значение ключа? Два пути:
id без смысла, который генерирует сама база (обычно растущее число). Книге всё равно, что её id = 3, — это просто бирка.На практике суррогатный id берут по умолчанию: он короткий, неизменный и всегда под рукой. В Postgres его удобно получать через GENERATED ALWAYS AS IDENTITY — база сама подставит следующее число.[3] Какой из путей выбрать и когда брать UUID вместо числа — это целиком тема урока 3, здесь просто берём рабочий вариант по умолчанию.
Иногда строку опознаёт не один столбец, а пара. В таблице «студент записан на курс» уникальна именно комбинация (student_id, course_id): один студент на один курс — одна запись. Тогда первичный ключ объявляют по нескольким столбцам сразу — и уникальной обязана быть их комбинация, а не каждый по отдельности.[1]
В прошлом уроке мы говорили: у строки есть физический адрес — ctid (номер страницы + номер строки). Возникает вопрос: почему не использовать его как идентификатор? Потому что он нестабилен. При UPDATE Postgres не меняет строку на месте, а создаёт её новую версию в другом месте — ctid меняется. А VACUUM FULL перепаковывает таблицу и переназначает адреса всем строкам.[5] Поэтому нужен логический ключ, который вы задаёте сами и который не зависит от физического хранения.
Как Postgres обеспечивает уникальность ключа? Он автоматически создаёт под него уникальный индекс-B-дерево и помечает столбцы как NOT NULL.[4] При каждой вставке база заглядывает в этот индекс: если такое значение уже есть — отказ. Бонусом тот же индекс делает поиск по ключу очень быстрым — но это уже тема урока про индексы.
Если контейнер из прошлого урока ещё жив — просто откройте консоль. Если нет — поднимите заново:
# если контейнер остановлен — запустить снова docker start pg-learn # открыть консоль psql docker exec -it pg-learn psql -U postgres
Нет Docker под рукой? Подойдёт онлайн-песочница DB Fiddle (выберите PostgreSQL).
Добавим суррогатный id. GENERATED ALWAYS AS IDENTITY поручает базе самой выдавать числа, а PRIMARY KEY объявляет этот столбец ключом.[2] Старую таблицу удаляем — на ней мы уже всё поняли.
DROP TABLE IF EXISTS books; CREATE TABLE books ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title text NOT NULL, author text, year integer );
Посмотрите структуру: \d books. Обратите внимание на строчку Indexes внизу — там появился books_pkey.
id мы не перечисляем — база подставит его сама. Третья книга намеренно дублирует первую, чтобы увидеть: по содержанию они одинаковы, а как записи — различимы.
INSERT INTO books (title, author, year) VALUES ('Чистый код', 'Роберт Мартин', 2008), ('SQL за 10 минут', 'Бен Форта', 2019), ('Чистый код', 'Роберт Мартин', 2008); -- та же книга ещё раз SELECT * FROM books;
Три строки получили id 1, 2, 3. Две «одинаковые» книги теперь различимы по ключу — дыра из урока 1 закрыта.
Заведём маленькую таблицу с естественным ключом и нарочно нарушим оба правила — пусть Postgres покажет, как он защищает ключ.
CREATE TABLE accounts ( login text PRIMARY KEY, -- естественный ключ: сам логин name text ); INSERT INTO accounts VALUES ('neo', 'Томас Андерсон'); -- ок -- обещание 1 (уникальность): тот же login второй раз INSERT INTO accounts VALUES ('neo', 'Кто-то ещё'); -- обещание 2 (NOT NULL): пустой login INSERT INTO accounts VALUES (NULL, 'Аноним');
Обе последние команды должны упасть с ошибкой. Прочитайте текст ошибок — в них прямо названы нарушенное ограничение и его имя.
У каждой строки теперь есть стабильное удостоверение. Вы видели оба способа его задать — суррогатный id (база генерирует) и естественный ключ (login) — и убедились, что СУБД сама стоит на страже уникальности и непустоты. Это та опора, на которую в уроке 4 встанут связи между таблицами.
Впишите ответы — они сохранятся в браузере; кнопкой «Копировать ответы» внизу пришлёте их мне на проверку.
Мгновенная обратная связь — выберите вариант, объяснение появится сразу.
1. Что именно гарантирует первичный ключ?
PRIMARY KEY = UNIQUE + NOT NULL. Уникальность отличает строки друг от друга, а запрет NULL гарантирует, что ключ есть у каждой строки.
2. Сколько первичных ключей может быть у одной таблицы?
Первичный ключ на таблицу — единственный. Если нужно опознавать строку по комбинации столбцов, делают составной ключ — это по-прежнему один ключ из нескольких столбцов. Дополнительную уникальность по другим столбцам дают через UNIQUE.
3. Почему физический адрес строки ctid не годится на роль постоянного идентификатора?
ctid — это физическое местоположение версии строки. При обновлении создаётся новая версия с другим ctid, а VACUUM FULL перепаковывает таблицу. Идентификатор должен быть логическим и стабильным — поэтому ключ задают явно.
4. В таблице books ключом стал столбец id bigint, который генерирует сама база. Это какой ключ?
Сгенерированный id ничего не описывает, он лишь бирка для опознания — это суррогатный ключ. Естественным был бы, например, ISBN книги или login аккаунта.
5. Мы взяли GENERATED ALWAYS AS IDENTITY «по умолчанию». Какой вопрос это оставляет открытым?
Именно так. Что такое ключ, мы поняли. А какой ключ выбирать под задачу — последовательный bigint или UUID, своё число или естественное значение — это и есть тема урока 3.
Ключ у нас есть, но мы молча взяли первый попавшийся вариант — авто-число. А ведь выбор бывает разный: последовательный bigint читается людьми и компактен, но выдаёт порядок и количество записей; UUID уникален без обращения к серверу и удобен в распределённых системах, но длиннее и «случайнее». Урок 3 — «Выбор идентификаторов»: как осознанно выбрать стратегию ключа и не пожалеть об этом через год.