PRIMARY KEY и FOREIGN KEY

PRIMARY KEY и FOREIGN KEY

В модуле 6 вы узнали, что первичные и внешние ключи — это основа связей между таблицами. Тогда мы работали с уже готовой схемой. Теперь разберём, как объявить PRIMARY KEY и FOREIGN KEY при создании таблицы, что происходит «под капотом» и как настроить поведение при удалении связанных записей.

PRIMARY KEY: уникальный идентификатор строки

PRIMARY KEY гарантирует, что каждая строка в таблице имеет уникальный идентификатор. Ограничение делает сразу три вещи:

  1. Запрещает NULL
  2. Гарантирует уникальность значения
  3. Автоматически создаёт индекс (для быстрого поиска)

Простой первичный ключ (на одной колонке):

-- Объявление в строке колонки
CREATE TABLE customers (
    id    SERIAL PRIMARY KEY,
    name  TEXT NOT NULL
);

-- Объявление в конце (уровень таблицы) — эквивалентно
CREATE TABLE customers (
    id    SERIAL,
    name  TEXT NOT NULL,
    PRIMARY KEY (id)
);

Оба варианта синтаксически эквивалентны. Первый короче для одиночного PK.

Составной первичный ключ (на нескольких колонках):

CREATE TABLE student_courses (
    student_id INTEGER NOT NULL,
    course_id  INTEGER NOT NULL,
    enrolled_at DATE   DEFAULT CURRENT_DATE,
    PRIMARY KEY (student_id, course_id)
);

Составной PK создаётся только на уровне таблицы. Пара (student_id, course_id) должна быть уникальной — студент не может дважды записаться на один курс. Каждое значение по отдельности не уникально.

Естественные vs суррогатные ключи

Суррогатный ключ — искусственный идентификатор (обычно SERIAL или UUID), не имеющий бизнес-смысла:

id SERIAL PRIMARY KEY  -- суррогатный

Естественный ключ — колонка с реальным бизнес-значением: email, ISBN, налоговый номер:

email VARCHAR(255) PRIMARY KEY  -- естественный

На практике суррогатные ключи предпочтительнее: они не меняются при изменении бизнес-данных, всегда уникальны, не зависят от внешних источников.

Проверь себя: почему email — плохой выбор для первичного ключа, если клиент захочет его изменить?

FOREIGN KEY: ссылочная целостность

FOREIGN KEY — ограничение, которое говорит: «значение этой колонки должно существовать в указанной таблице».

CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    amount      NUMERIC(10, 2) NOT NULL,
    status      TEXT DEFAULT 'pending'
);

REFERENCES customers(id) — краткая запись внешнего ключа. СУБД не позволит вставить orders с несуществующим customer_id. Это ссылочная целостность (referential integrity).

Полный синтаксис через именованное ограничение:

CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Именованное ограничение (CONSTRAINT fk_orders_customer) удобно: при ошибке PostgreSQL сообщит имя нарушенного ограничения, и сообщение об ошибке будет понятным.

ON DELETE: что делать при удалении родительской строки

По умолчанию удаление строки из customers, на которую ссылается строка из orders, вызовет ошибку. Поведение можно изменить через ON DELETE:

RESTRICT (по умолчанию): запрещает удаление, если есть зависимые строки:

REFERENCES customers(id) ON DELETE RESTRICT

CASCADE: автоматически удаляет все зависимые строки:

REFERENCES customers(id) ON DELETE CASCADE
-- При DELETE FROM customers WHERE id=1: все заказы клиента тоже удалятся

SET NULL: устанавливает NULL в колонке FK при удалении родительской строки:

REFERENCES customers(id) ON DELETE SET NULL
-- При DELETE FROM customers WHERE id=1: в orders.customer_id = NULL

SET DEFAULT: устанавливает значение по умолчанию:

REFERENCES categories(id) ON DELETE SET DEFAULT
-- При DELETE FROM categories: products.category_id = DEFAULT (например, id=0 — «Без категории»)

NO ACTION: похоже на RESTRICT, но проверка откладывается до конца транзакции. В большинстве случаев неотличим от RESTRICT.

ON UPDATE: что делать при изменении первичного ключа

Аналогично ON DELETE, можно настроить поведение при изменении первичного ключа:

REFERENCES customers(id) ON DELETE CASCADE ON UPDATE CASCADE

Изменение id родительской строки автоматически обновит все ссылки. На практике первичные ключи редко меняются, поэтому ON UPDATE нужен редко.

Индекс для внешнего ключа

PRIMARY KEY автоматически создаёт индекс. FOREIGN KEY — нет. Это важно для производительности JOIN:

-- Хорошо: явно создать индекс на колонке FK
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Без индекса на customer_id поиск заказов по клиенту требует полного сканирования таблицы orders. С индексом — мгновенный поиск. Правило: всегда создавайте индексы на колонках внешних ключей.

Практический пример: схема с несколькими связями

CREATE TABLE categories (
    id    SERIAL PRIMARY KEY,
    name  TEXT NOT NULL,
    parent_id INTEGER REFERENCES categories(id)  -- самоссылка
);

CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    title       TEXT    NOT NULL,
    category_id INTEGER NOT NULL REFERENCES categories(id) ON DELETE RESTRICT,
    price       NUMERIC(10, 2) NOT NULL CHECK (price >= 0)
);

CREATE TABLE cart_items (
    id         SERIAL PRIMARY KEY,
    session_id UUID    NOT NULL,
    product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    quantity   INTEGER NOT NULL DEFAULT 1 CHECK (quantity > 0),
    added_at   TIMESTAMPTZ DEFAULT NOW()
);

-- Индексы на FK
CREATE INDEX idx_products_category   ON products(category_id);
CREATE INDEX idx_cart_session_product ON cart_items(session_id, product_id);

categories самоссылается — дерево категорий. products ссылается на categories (нельзя удалить категорию с товарами). cart_items ссылается на products с CASCADE (удаление товара очищает корзины).

Типичные ошибки

1. FK без индекса:

-- Медленные JOIN и проверка целостности
FOREIGN KEY (order_id) REFERENCES orders(id)
-- Добавить:
CREATE INDEX idx_items_order_id ON order_items(order_id);

2. CASCADE везде «на всякий случай»: ON DELETE CASCADE удобен, но опасен — легко случайно удалить важные данные. Используйте CASCADE только когда зависимые строки действительно не имеют смысла без родительской.

3. Нарушение порядка создания таблиц: Родительская таблица должна существовать до создания дочерней с FK.

Ссылочная целостность: что проверяет СУБД

При каждой DML-операции PostgreSQL проверяет все FK-ограничения:

  • INSERT в дочернюю таблицу: значение FK должно существовать в родительской
  • UPDATE FK-колонки в дочерней: новое значение должно существовать в родительской
  • DELETE из родительской таблицы: если есть дочерние строки — действие зависит от ON DELETE
  • UPDATE PK в родительской таблице: если есть дочерние строки — действие зависит от ON UPDATE

Эти проверки выполняются автоматически. Именно поэтому реляционные базы называют надёжным хранилищем: невозможно сохранить данные, нарушающие объявленные правила.

Просмотр ограничений в PostgreSQL

Проверить существующие ограничения таблицы можно через \d в psql:

\d orders
-- Покажет таблицу с типами колонок, ограничениями, индексами и внешними ключами

Или через SQL:

SELECT constraint_name, constraint_type
FROM   information_schema.table_constraints
WHERE  table_name = 'orders';

Это полезно для понимания схемы чужой базы данных или проверки правильности созданных ограничений.

Отложенная проверка FK (DEFERRABLE)

По умолчанию PostgreSQL проверяет внешние ключи немедленно при каждой операции. Но при работе с циклическими зависимостями (A ссылается на B, B на A) это создаёт проблему — нельзя вставить ни A, ни B без другой.

Решение — отложенная проверка:

CONSTRAINT fk_example FOREIGN KEY (parent_id) REFERENCES table(id)
    DEFERRABLE INITIALLY DEFERRED

Проверка откладывается до конца транзакции. Это редкая, но иногда необходимая возможность.

Краткий итог

  • PRIMARY KEY — уникальный идентификатор строки; запрещает NULL, гарантирует уникальность, создаёт индекс
  • Составной PK задаётся на уровне таблицы: PRIMARY KEY (col1, col2)
  • FOREIGN KEY — ссылочная целостность: значение должно существовать в родительской таблице
  • ON DELETE CASCADE/RESTRICT/SET NULL — поведение при удалении родительской строки
  • Всегда создавайте индексы на колонках внешних ключей

PRIMARY KEY vs UNIQUE + NOT NULL

Технически PRIMARY KEY эквивалентен UNIQUE NOT NULL с добавлением семантики «это идентификатор строки». Разница:

  • PRIMARY KEY — один на таблицу; семантически — главный идентификатор
  • UNIQUE NOT NULL — можно несколько; семантически — альтернативный уникальный ключ

Например, таблица users может иметь:

id    SERIAL  PRIMARY KEY,        -- суррогатный PK
email VARCHAR(255) UNIQUE NOT NULL  -- альтернативный ключ

Внешние ключи в других таблицах обычно ссылаются на id (PK), а email используется для поиска и аутентификации.

Что дальше

Вы умеете объявлять ключевые ограничения. Следующий урок — остальные ограничения: NOT NULL, UNIQUE, CHECK и DEFAULT. Они дополняют PK/FK и защищают данные от некорректных значений.

Попробуйте интерактивную версию

Практические задачи, квизы и AI-наставник — бесплатный старт без карты

Перейти к практике