Первичные и внешние ключи в SQL: короткое руководство

За 20–30 минут вы создадите в PostgreSQL 16 две связанные таблицы: покупателей и заказов, добавите ограничения целостности и проверите, как база не пропускает дубликаты и ссылки на несуществующие записи. Пример рассчитан на новичка, который уже подключён к базе через psql или графический клиент.

Что нужно иметь до начала

Нужен работающий PostgreSQL 16 и доступ к любой базе, где у вашей роли есть право создавать таблицы. Команды ниже используют обычный SQL, поэтому их можно выполнить в psql, DBeaver, DataGrip или другом клиенте. Синтаксис рассчитан на PostgreSQL 16; в других СУБД имена типов и поведение автоинкремента могут отличаться.

• подключение к базе PostgreSQL 16;
• клиент для выполнения SQL-запросов;
• пустая схема или возможность выбрать отдельные имена таблиц, чтобы не столкнуться с конфликтом существующих объектов;
• понимание, что тестовая база безопаснее рабочей: команды с удалением таблиц здесь не используются.

Первичный и внешний ключ: что именно они проверяют

Первичный ключ (primary key, PK) однозначно идентифицирует строку. В PostgreSQL ограничение PRIMARY KEY требует, чтобы значение было уникальным и не было NULL. У таблицы может быть один первичный ключ, но он может состоять из нескольких столбцов.

Внешний ключ (foreign key, FK) связывает столбец одной таблицы с уникальным ключом другой. Например, orders.customer_id может ссылаться на customers.customer_id. Тогда заказ нельзя привязать к покупателю, которого нет в родительской таблице.

Термин «ограничение целостности» означает правило, которое сама СУБД проверяет при вставке, изменении или удалении данных.

Ключи нужны не только для удобных JOIN-запросов. Они переносят часть проверки данных из приложения в базу — туда, где правило нельзя случайно обойти вторым сервисом или ручным SQL-запросом.

Пошаговая настройка в PostgreSQL 16

Шаг 1. Создайте родительскую таблицу

Сначала создайте таблицу, на записи которой будут ссылаться другие таблицы. В примере это customers. Тип bigint подходит для идентификатора с большим запасом, а GENERATED ALWAYS AS IDENTITY поручает PostgreSQL выдавать новые значения.

CREATE TABLE customers (
    customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name text NOT NULL,
    email text NOT NULL UNIQUE
);

Проверка: запрос \d customers в psql должен показать столбец customer_id с первичным ключом, а также уникальное ограничение для email. В графическом клиенте откройте раздел Constraints у таблицы.

Шаг 2. Создайте таблицу со ссылкой

Теперь добавьте orders. Ограничение FOREIGN KEY явно указывает, что значение customer_id должно существовать в customers.customer_id.

CREATE TABLE orders (
    order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL,
    order_total numeric(12, 2) NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT orders_customer_fk
        FOREIGN KEY (customer_id)
        REFERENCES customers (customer_id)
);

Проверка: команда \d orders в psql должна показать первичный ключ orders_pkey и внешний ключ orders_customer_fk. Если вы открываете схему в GUI, между таблицами должна быть видна связь.

Шаг 3. Добавьте данные в правильном порядке

Сначала вставляют родительскую запись, затем дочернюю. PostgreSQL вернёт сгенерированный идентификатор покупателя, а этот идентификатор можно использовать в заказе.

INSERT INTO customers (full_name, email)
VALUES ('Анна Петрова', 'anna@example.com')
RETURNING customer_id;

INSERT INTO orders (customer_id, order_total)
VALUES (1, 2490.00)
RETURNING order_id, customer_id, order_total;

Проверка: первый запрос должен вернуть customer_id, а второй — созданный заказ с тем же customer_id. Если идентификатор оказался не равен 1, подставьте реально возвращённое значение, а не число из примера.

Шаг 4. Проверьте связь запросом JOIN

Внешний ключ не объединяет строки автоматически: он только проверяет допустимость ссылки. Для чтения данных используйте обычный JOIN.

SELECT
    orders.order_id,
    customers.full_name,
    customers.email,
    orders.order_total,
    orders.created_at
FROM orders
JOIN customers
    ON customers.customer_id = orders.customer_id
ORDER BY orders.order_id;

Проверка: в результате должна появиться строка заказа вместе с именем и email покупателя. Если заказ есть, но строки нет, проверьте условие ON и значения идентификаторов.

Шаг 5. Намеренно вызовите две ошибки

Проверка ограничений полезнее, чем простое создание таблиц. Первый запрос пытается создать заказ для покупателя, которого нет; второй повторяет уникальный email.

INSERT INTO orders (customer_id, order_total)
VALUES (999999, 100.00);

INSERT INTO customers (full_name, email)
VALUES ('Другой покупатель', 'anna@example.com');

Проверка: первый запрос должен завершиться ошибкой внешнего ключа, обычно с текстом violates foreign key constraint. Второй должен сообщить о нарушении уникальности, обычно duplicate key value violates unique constraint.

Если не получилось

Таблица уже существует

Сообщение ERROR: relation "customers" already exists означает, что имя занято. Не удаляйте рабочую таблицу вслепую: выберите другое имя, например customers_demo, или найдите владельца существующей схемы.

Внешний ключ не создаётся

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

Вставка заказа отклонена

insert or update on table "orders" violates foreign key constraint означает, что в customer_id передано значение без соответствующей строки в customers. Сначала найдите покупателя, затем используйте его фактический идентификатор.

Не удаётся удалить покупателя

Если у покупателя есть заказы, PostgreSQL по умолчанию не даст удалить его и сообщит о нарушении внешнего ключа. Это защита от «осиротевших» заказов. Каскадное удаление через ON DELETE CASCADE меняет смысл данных и должно приниматься отдельно: удаление покупателя тогда удалит и связанные заказы.

Что важно не перепутать

Внешний ключ не обязан называться так же, как родительский столбец, но одинаковые имена уменьшают количество ошибок. Он также не делает значение уникальным: у одного покупателя может быть много заказов. Если нужно запретить повторную связь, добавьте отдельное ограничение UNIQUE — но только после проверки бизнес-правила.

NOT NULL и внешний ключ решают разные задачи. Внешний ключ допускает NULL, если столбец не объявлен как NOT NULL. Такое значение означает «ссылка не указана», а не «ссылка ведёт на отсутствующую запись».

Составной ключ оформляют перечислением столбцов: PRIMARY KEY (part_a, part_b). Для него внешний ключ тоже должен содержать соответствующую пару столбцов. Для первого проекта лучше начать с одного числового идентификатора, если предметная область не требует другого.

Что делать дальше

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