Composite keys и foreign keys: составные и внешние ключи

В первых двух уроках модуля мы разобрали иерархию ключей и выбор между natural и surrogate. Все примеры там были про ключ из одного столбца. Но ключи бывают составными — из нескольких столбцов. И отдельно существует особый вид ключа, который не идентифицирует строку, а связывает таблицы — foreign key. Эти два понятия — composite key и foreign key — разберём в этом уроке. Они часто встречаются вместе, и вместе они делают возможной junction-таблицу для связей M:N (следующий модуль).

Composite key: ключ из нескольких столбцов

Composite key (составной ключ) — это ключ, состоящий из двух или более атрибутов. Это не отдельный вид ключа в иерархии — это любой ключ (superkey, candidate key, primary key), у которого больше одного столбца.

Зачем нужен составной ключ? Когда ни один отдельный столбец не идентифицирует строку уникально, а их комбинация — идентифицирует.

Классический пример — таблица позиций заказа order_items:

order_items:
order_id | product_id | quantity | price
---------+------------+----------+-------
  1001   |    50      |    2     |  100
  1001   |    51      |    1     |  250
  1001   |    52      |    3     |   70
  1002   |    50      |    1     |  100

Что здесь уникально идентифицирует строку?

• order_id сам по себе — нет: заказ 1001 встречается в трёх строках.
• product_id сам по себе — нет: товар 50 встречается в двух заказах.
• А вот пара (order_id, product_id) — да: в одном заказе один товар не может быть позицией дважды.

Значит, primary key этой таблицы — composite key (order_id, product_id):

CREATE TABLE order_items (
    order_id   INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity   INTEGER NOT NULL CHECK (quantity > 0),
    price      NUMERIC(12,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)   -- составной ключ из двух столбцов
);

Обратите внимание на синтаксис: составной PRIMARY KEY объявляется отдельной строкой в CREATE TABLE, перечислением столбцов в скобках. Так же, как и составной UNIQUE или составной FOREIGN KEY.

Минимальность остаётся обязательной

Composite key — это всё ещё ключ, и для candidate key требование минимальности никуда не делось. Пара (order_id, product_id) — candidate key, только если обе части необходимы: убрать любую — уникальность теряется. Если бы оказалось, что order_id уникален и сам по себе, то (order_id, product_id) был бы superkey, но не candidate key — product_id оказался бы лишним.

Порядок столбцов в составном ключе

В реляционной модели порядок атрибутов в ключе значения не имеет — ключ это множество. Но в физической реализации порядок столбцов составного ключа важен: по составному ключу строится составной B-tree индекс, и он сортирует данные сначала по первому столбцу, затем по второму. Индекс по (order_id, product_id) эффективен для поиска по order_id и по (order_id, product_id), но НЕ для поиска только по product_id. Это влияет на производительность запросов; детально про устройство B-tree — в последнем уроке модуля.

Foreign key: ключ, связывающий таблицы

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

Принципиальное отличие от всех ключей из прошлых уроков: superkey, candidate key, primary key, alternate key идентифицируют строку внутри своей таблицы. Foreign key — нет. Foreign key ничего не идентифицирует; его задача — ссылаться. Он реализует связь между таблицами и обеспечивает referential integrity (мы вводили это понятие в модуле про реляционную модель — теперь смотрим на него со стороны ключей).

Таблица users (родительская):     Таблица orders (дочерняя):
user_id (PK) | name               order_id (PK) | user_id (FK) | amount
-------------+------               -------------+--------------+-------
     1       | Ann                     101      |      1       |  250
     2       | Bob                     102      |      1       |   70
                                       103      |      2       |  500

orders.user_id — foreign key. Каждое его значение (1, 1, 2)
обязано существовать в users.user_id. Это и есть связь 1:N.

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    user_id  INTEGER NOT NULL REFERENCES users(user_id),  -- foreign key
    amount   NUMERIC(12,2) NOT NULL
);

Терминология: parent и child

В связи через foreign key две стороны:

• Parent (родительская) таблица — та, на чей ключ ссылаются. Здесь users. Её ещё называют referenced table.
• Child (дочерняя) таблица — та, что содержит foreign key и ссылается. Здесь orders. Её называют referencing table.

Правило размещения foreign key для связи 1:N простое: FK ставится на стороне «многих». У одного пользователя много заказов — значит FK user_id лежит в orders, а не наоборот. Это правило мы детально разберём в модуле про кардинальность; здесь зафиксируем как факт.

Что foreign key физически проверяет

Foreign key — это не пометка, а активный constraint. СУБД проверяет его в двух точках:

При вставке/изменении строки в child-таблице. Вставляем заказ с user_id = 5 — СУБД делает поиск: есть ли в users строка с user_id = 5? Нет — INSERT отклоняется. Есть — проходит.

INSERT INTO orders VALUES (104, 99, 100);
-- ERROR: insert or update on table "orders" violates foreign key constraint
-- пользователя 99 не существует

При удалении/изменении строки в parent-таблице. Удаляем пользователя — СУБД проверяет: не ссылается ли на него кто-то из orders? Поведение задаётся опциями ON DELETE / ON UPDATE (RESTRICT, CASCADE, SET NULL) — мы разбирали их в уроке про constraints.

Важная практическая деталь: чтобы проверка FK была быстрой, столбец foreign key почти всегда нужно проиндексировать. СУБД индексирует primary key автоматически — но НЕ foreign key. Без индекса на orders.user_id каждая проверка при удалении пользователя и каждый JOIN по user_id будет полным сканированием таблицы заказов.

CREATE INDEX idx_orders_user_id ON orders(user_id);
-- без этого индекса JOIN orders с users и каскадные операции медленны

Foreign key может быть составным

Если parent-таблица имеет составной primary key, то foreign key на неё тоже обязан быть составным — из стольких же столбцов. Это прямое следствие: ссылка должна указывать на полный ключ.

-- parent: составной primary key
CREATE TABLE order_items (
    order_id   INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

-- child: составной foreign key из двух столбцов
CREATE TABLE shipments (
    shipment_id INTEGER PRIMARY KEY,
    order_id    INTEGER NOT NULL,
    product_id  INTEGER NOT NULL,
    FOREIGN KEY (order_id, product_id) REFERENCES order_items(order_id, product_id)
);

Это, кстати, ещё один аргумент за surrogate primary key: ссылаться на один surrogate столбец проще, чем тащить составной FK.

Foreign key может ссылаться на ту же таблицу

Foreign key не обязан указывать на другую таблицу — он может ссылаться на ту же самую, в которой находится. Это называется self-referencing связь, и так моделируются иерархии: сотрудник -> его руководитель, категория -> родительская категория, комментарий -> родительский комментарий.

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    manager_id  INTEGER REFERENCES employees(employee_id)  -- FK на эту же таблицу
);
-- manager_id указывает на employee_id той же таблицы.
-- У директора manager_id = NULL (руководителя нет).

Self-referencing связям и рекурсивным иерархиям посвящён отдельный урок следующего модуля — здесь важно зафиксировать саму возможность.

Эта диаграмма — анонс следующего модуля: junction-таблица enrollments имеет составной primary key (student_id, course_id), и каждый его столбец одновременно является foreign key. Composite key и foreign key, соединённые вместе, — это и есть способ реализовать связь «многие-ко-многим».

Попробуй сам

Спроектируйте схему для библиотеки с тремя таблицами: books, members (читатели), loans (выдачи книг).

1. У loans подумайте над ключом. Один читатель берёт много книг; одна книга за свою жизнь выдаётся многим. Может ли быть primary key (book_id, member_id)? Подсказка: один и тот же читатель может взять одну и ту же книгу дважды в разное время — значит, в composite key нужен ещё и loan_date, либо surrogate loan_id. Выберите вариант и обоснуйте.
2. Объявите foreign keys: loans.book_id -> books, loans.member_id -> members. Выберите опцию ON DELETE для каждого: что должно случиться с записями о выдачах, если удаляют книгу? если удаляют читателя?
3. Создайте индексы на оба столбца foreign key в loans. Объясните, почему без них JOIN loans с books и с members будет медленным.
4. Добавьте в books self-referencing foreign key original_edition_id (ссылка на книгу, переизданием которой эта книга является; у первого издания — NULL). Проверьте, что СУБД принимает такую ссылку на ту же таблицу.