Первичные и внешние ключи: что знать до работы с базой

В PostgreSQL 16 первичный и внешний ключи помогают не допускать дублей и ссылок на отсутствующие данные. За несколько минут вы создадите две связанные таблицы, добавите запись и проверите, как база отклоняет некорректную связь. Примеры рассчитаны на новую учебную базу или схему, где таблиц с такими именами ещё нет.

Что подготовить

Понадобится PostgreSQL 16 и клиент для выполнения SQL: например, psql или графическая программа для работы с базой. Нужны права создавать таблицы в выбранной схеме. Команды ниже — обычный SQL; названия типов и поведение внешних ключей соответствуют PostgreSQL 16.

Первичный ключ (primary key, PK) — столбец или набор столбцов, по которым база однозначно находит строку. Значения такого ключа должны быть уникальными и не могут быть NULL. Внешний ключ (foreign key, FK) — ограничение, которое требует, чтобы значение в одной таблице соответствовало ключу существующей строки в другой.

Создаём таблицы и проверяем связь

1. Создайте таблицу категорий. Здесь отдельный столбец id будет первичным ключом.

   CREATE TABLE categories (
       id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
       name text NOT NULL UNIQUE
   );

   Проверка: команда завершается без ошибки. В таблице появились ограничения первичного ключа для id и уникальности для name. IDENTITY поручает PostgreSQL выдавать новые значения автоматически.

2. Добавьте таблицу товаров с внешним ключом на категории.

   CREATE TABLE products (
       id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
       name text NOT NULL,
       category_id bigint NOT NULL,
       CONSTRAINT products_category_fk
           FOREIGN KEY (category_id)
           REFERENCES categories (id)
   );

   Проверка: таблица создана. Тип category_id совпадает с типом categories.id — bigint. Имя ограничения задано явно, поэтому его проще найти в сообщении об ошибке и документации.

3. Вставьте категорию, а затем товар, который на неё ссылается.

   INSERT INTO categories (name)
   VALUES ('Книги')
   RETURNING id;
   
   INSERT INTO products (name, category_id)
   VALUES ('Атлас', 1);
   
   SELECT products.name, categories.name AS category
   FROM products
   JOIN categories ON categories.id = products.category_id;

   Проверка: запрос вернёт товар «Атлас» и категорию «Книги», если таблицы были пустыми и новой категории выдан id 1. Если в таблице уже были записи, используйте id, который вернул первый INSERT, а не предполагайте конкретное значение.

4. Проверьте защиту от ссылки на отсутствующую категорию. Выполняйте этот запрос отдельно: он должен завершиться ошибкой.

   INSERT INTO products (name, category_id)
   VALUES ('Несуществующая категория', 999999);

   Проверка: PostgreSQL сообщит о нарушении ограничения внешнего ключа products_category_fk. Точный текст ошибки зависит от клиента и локали, но операция не должна добавить строку.

Ключ — не просто способ найти строку. Это правило, которое база данных проверяет при каждой записи, даже если данные поступили не из вашего приложения.

Как выбрать первичный ключ

Для большинства новых таблиц удобен отдельный числовой идентификатор. Он не зависит от имени, адреса или другого значения, которое может измениться. В примере таким идентификатором служит id, а PostgreSQL выдаёт его при вставке.

Первичный ключ может состоять из нескольких столбцов. Например, в таблице связи между студентами и курсами пара student_id, course_id может быть составным ключом: одна и та же пара не повторится, хотя каждый столбец отдельно может повторяться. Такой вариант уместен, когда именно сочетание значений определяет уникальность записи. Если потом на эту запись часто ссылаются другие таблицы, отдельный id может сделать связи удобнее.

Не превращайте изменяемое значение вроде адреса электронной почты в первичный ключ только потому, что сейчас оно уникально. Если значение меняется, придётся менять и все связанные ссылки либо отдельно продумывать правила их обновления.

Что задаёт внешний ключ

Внешний ключ проверяет наличие родительской строки, но сам по себе не делает связь обязательной: если столбец допускает NULL, пустое значение обычно допустимо. В примере category_id объявлен NOT NULL, поэтому каждый товар обязан ссылаться на категорию.

Правило удаления по умолчанию — не разрешать удалить категорию, на которую ещё ссылается товар. Это защищает от потери связи, но может мешать удалению. Для других сценариев PostgreSQL поддерживает действия, например ON DELETE CASCADE — удаление зависимых строк вместе с родительской. Используйте каскад только если такое удаление соответствует смыслу данных: ошибка в одной команде может затронуть сразу много строк.

Внешний ключ не создаёт индекс на столбце, который ссылается на другую таблицу. Если часто ищете товары по category_id или удаляете категории, проверьте план запросов и нагрузку; индекс на таком столбце может понадобиться отдельно.

Типичные ошибки и границы

• Несовпадающие типы. Например, внешний ключ имеет тип integer, а целевой ключ — bigint. Для связи используйте совместимые типы и, как в примере, одинаковое объявление.

• Сначала вставили зависимую строку. Товар ссылается на категорию, которой ещё нет. Сначала создайте родительскую запись, затем дочернюю.

• Удаление запрещено. Это ожидаемое поведение, если на запись ссылаются. Найдите зависимые строки и решите, нужно ли переносить их, удалять или менять правило связи.

• Ограничение не добавляется к заполненной таблице. В уже существующих данных могут быть дубли, NULL или ссылки на отсутствующие строки. Сначала найдите и исправьте такие записи; не отключайте проверку ради успешного изменения схемы.

Эти примеры показывают связь «одна категория — много товаров». Связь многие-ко-многим обычно строят через отдельную таблицу-связку с двумя внешними ключами; здесь этот вариант не рассматривается.

Что проверить дальше

Перед переносом схемы в приложение решите, что должно происходить при удалении родительской записи, и проверьте ожидаемое поведение на тестовых данных. Затем добавьте индекс на внешний ключ только при необходимости, ориентируясь на реальные запросы и планы выполнения. Для следующего шага сверяйтесь с разделом PostgreSQL 16 о первичных и внешних ключах: там описаны ограничения и действия при удалении и обновлении.