Руководство по созданию эффективных запросов

175
П
равильное
 
и
 
неПравильное
 
исПользование
 
суррогатных
 
ключей
Суррогатные ключи – это уникальные значения, генерируемые системой для 
идентификации объектов, хранящихся в базе данных. В PostgreSQL сурро-
гатные значения могут быть выбраны из последовательности. Когда строка 
вставляется, столбец с псевдотипом 
serial
автоматически получает следую-
щее значение из последовательности, связанной с таблицей.
Суррогатные ключи широко используются. Внутренние стандарты некото-
рых компаний требуют использования суррогатных ключей для любой таб-
лицы. Однако у этих ключей есть не только преимущества, но и недостатки.
Преимущество суррогатов состоит в том, что значения, присвоенные раз-
личным объектам, гарантированно уникальны. Однако значение суррогат-
ного ключа не связано с другими атрибутами и бесполезно при сопоставле-
нии сохраненного объекта с объектом реальным.
Уникальность суррогатных ключей может скрывать определенные ошиб-
ки. Реальный объект может быть представлен в базе данных несколько раз 
с разными суррогатными ключами. Например, если одна покупка регистри-
руется в системе дважды, с карты клиента будет дважды списана оплата 
за один продукт, и проблему трудно будет решить без ручного вмешатель-
ства. При этом, хотя мы рекомендуем использовать какой-нибудь реальный 
уникальный атрибут для первичного ключа, это не всегда возможно. В базе 
данных супермаркета невозможно различить две бутылки колы, проскани-
рованные одним и тем же покупателем на кассе самообслуживания. Случаи 
покупки двух бутылок колы в одной транзакции и дублирования транзакции 
покупки одной колы должны быть различимы в исходной системе. Точно так 
же в больничных системах может быть несколько учетных номеров, связан-
ных с одним пациентом; в этом случае очень важно иметь суррогатный ключ, 
чтобы все клинические данные пациента хранились вместе.
Иногда наличие суррогатного ключа в таблице ошибочно связывают с нор-
мализацией. Внутренние стандарты некоторых компаний требуют наличия 
суррогатного ключа для каждой таблицы. Обычно это объясняется как способ 
нормализовать схему базы данных. И действительно, если каждой строке на-
значен уникальный идентификатор, все будет нормализовано. Но посколь-
ку уникальные идентификаторы не имеют никакого отношения к объектам 
реального мира, одному настоящему объекту может оказаться сопоставлено 
несколько объектов в базе данных. Например, нам встречалась система, ко-
торая каждый раз при вводе покупателем неизвестного города присваивала 
этому городу уникальный идентификатор. Таким образом в системе набра-
лось шесть разных версий Чикаго. Излишне говорить, что это не имеет ниче-
го общего с нормализацией и может поставить под угрозу и точность данных, 
и производительность.
Использование суррогатов может привести к дополнительным соединени-
ям. Таблица 
flight
ссылается на таблицу 
airport
, используя трехсимвольные 
коды, широко применяемые на практике. В этой схеме коды аэропортов 
можно извлечь из таблицы 
flight
:

---

176

Проектирование имеет значение
SELECT departure_airport, arrival_airport, scheduled_departure FROM flight
...
Однако если бы для таблицы 
airport
использовался суррогатный ключ, 
то получение кодов аэропортов потребовало бы двух просмотров таблицы 
airport
:
SELECT d.airport_code,
a.airport_code,
f.scheduled_departure
FROM flight f
JOIN airport d ON d.airport_id = f.departure_airport_id 
JOIN airport a ON a.airport_id = f.arrival_airport_id
Подробнее рассмотрим использование суррогатных ключей в схеме 
post-
gres_air
.
Определение таблицы 
airport
в схеме 
postgres_air
показано в лис тинге 9.8.
Первичный ключ этой таблицы – 
airport_code
. Этот столбец содержит трех-
значные коды, которые используются для идентификации аэропортов во 
всех системах бронирования рейсов по всему миру, и эти коды никогда не 
меняются. Следовательно, они надежны как уникальные идентификаторы, 
и суррогатные ключи не нужны.