Первичный и внешний ключи PostgreSQL

Статья основана на третьем видео из 30 тем курса SQL c 0 от Аристова Евгения. Ссылки на видео на платформах RUTUBE и VK видео.

В данной статье разберем первичные и внешние ключи в PostgreSQL.

Первичный ключ

Для чего он нужен?

1. Уникальность: Первичный ключ гарантирует уникальность каждой записи в таблице. Каждая строка должна иметь уникальное значение первичного ключа, что помогает идентифицировать конкретную запись однозначно.
2. Упорядоченность: Первичный ключ также определяет упорядоченность записей в таблице. Это может быть полезно при выполнении запросов или сортировке данных по первичному ключу.
3. Индексация: По умолчанию в большинстве СУБД создается индекс для первичного ключа.

Если говорить про искусственный ключ (1, 2, 3 и т.д.), то по номеру примерно понятно, когда была создана та или иная запись.

Что касается UUID, то обычно это не так, однако существует UUID 7 версии, который нумерует записи относительно по порядку. UUID по умолчанию обеспечивает уникальность, но он больше по размеру, поэтому нужно больше времени на его генерацию (снижается производительность), также увеличиваются внешние ключи (больше индексы).

Плюсы UUID – уникальность и безопасность. Минусы – скорость работы с ним и объем хранимой информации.

UUID можно заменить классическим справочником. Можно использовать в расчетных данных и master-master репликации (но лучше генерировать последовательность со сдвигом)

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

Рассмотрим таблицы с товарами и продажами. В таблицу с продажами записывается не название товара, а внешний ключ (ссылка на товар).

Во-первых, внешний ключ занимает значительно меньше места, так как является числом.

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

В-третьих, в таблицу Продажи нельзя добавить продажу несуществующего товара (того, которого нет в таблице Товары). Номера во внешнем ключи всегда должны содержаться в родительской таблице.

Также существует каскадное удаление и обновление данных. При изменении ключа у яблок с “1” на “5” (таблица Товары) в дочерней таблице Продажи ключ поменяется автоматически. Аналогично, при удалении яблок из списка товаров, удалятся все продажи с ними. Рекомендуется ставить distinct на каскадное удаление, так как оно может привести к потере данных. В данном примере, если магазин больше не продает яблоки, то это не означает, что все данные с ними должны быть удалены из финансовых отчетов.

Естественный и искусственный ключи

Первичный ключ может быть искусственным – sequence – последовательность. Работа с ней будет обсуждаться в следующей лекции. Также может быть и естественным, например, ИНН, государственный номер машины и т. д.

Обычно искусственный ключ весит 4 байта (int от -2 млрд. до +2 млрд.), а естественный 8 байт (текстовая переменная).

Естественный ключ

Из плюсов можно отметить, что человеку легче с работать с естественным ключом, так как используются уже существующие данные (атрибуты), дающие смысловую информацию.

Но есть и ряд недостатков. Естественный ключ может повториться по ошибке оператора. Также естественный ключ может изменить владельца (например, номер телефона). Изменение естественного ключа тоже вызывает проблемы (из-за каскадного обновления). Поэтому использовать его не рекомендуется.

Искусственный ключ

Обычно искусственным ключом является автоинкрементируемое поле “ID”.

Искусственный ключ генерируется самим PostgreSQL. В то же время генерацией UUID занимается операционная система, поэтому это требует больше времени.

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

Очевидный недостаток – отсутствие связи с данными. Искусственный ключ не информативен для пользователя из-за отсутствия смысловой информации. Могут потребоваться дополнительные усилия при установлении связей между таблицами.

Последовательность ключей (стандартный int) начинается с единицы, заканчивается двумя миллиардами (так как в PostgreSQL нет типа данных unsigned int). Если планируется больше записей, то нужно поменять тип первичного ключа и типы всех внешних ключей, ссылающихся на него, на big integer . В противном случае произойдет ошибка из-за дублирования записей, так как после 2 млрд. последовательность снова начнется с единицы.

Есть ещё два варианта решения проблемы с нехваткой первичных ключей. Во-первых, можно поискать “дырки” в последовательности. Они возникают, когда ключи резервируются под данные (с помощью транзакций), но записи по итогу не создаются (rollback).

Во-вторых, можно начать последовательность ключей не с 1, а с -2 млрд. В данном случае нужно обратить внимание, нормально ли обрабатываются такие ключи в вашем софте.

Внешний ключ

Внешний ключ всегда ссылается на первичный.

Зачем он нужен?

1. Сохранение целостности данных: Внешние ключи гарантируют, что данные, хранящиеся в столбце, связанном с внешним ключом, всегда ссылаются на существующие корректные записи в другой таблице.
2. Связи между таблицами: Внешний ключ устанавливает связь между двумя таблицами, позволяя создавать связи один-к-одному, один-ко-многим или многие-ко-многим между данными в разных таблицах. Подробнее про связи здесь.
3. Улучшение производительности: Индексы, создаваемые для внешних ключей, помогают ускорить выполнение операций соединения таблиц (join) и поиска данных (select).
4. Обеспечение согласованности операций: Использование внешних ключей помогает гарантировать согласованность операций между связанными таблицами. Например, при удалении записи из родительской таблицы, можно автоматически удалить или обновить связанные записи в дочерней таблице (выше рекомендовалось запрещать каскадное удаление и обновление).

Обычно на внешний ключ также создается индекс.

Правила работы с внешним ключом

Рекомендуется создавать такого же типа данных как и в родительской таблице. Vожет быть и отличающегося, но приводимого типа (например, integer -> biginteger). Главное, чтобы соответствовал диапазон и сохранялась уникальность.

Существуют правила именования ключей:

• В родительской таблице создается поле id

• В дочерней fk_имя_родительской_таблицы (fk – сокращение от foreigh key)

Пример на основе таблиц с товарами и продажами:

Обычно в литературе рекомендуют именовать goodID и goodID_fk.

Способ менее удобный, так как:

• не сразу видно, что поле является внешним ключом, увидим про это только в конце поля

• при полном указании в запросе с использованием имени таблицы получится масло масляное – goods.goodID, вместо goods.id, также при соединении – sales.goodID_fk = goods.goodID, в моём варианте sales.fk_goods = goods.id – более кратко и очевидно

Категорически не рекомендуется именовать внешний ключ без fk.

Обратите внимание, что я допустил неточность при именовании таблиц – в чем она заключается, рассмотрим в 11 теме.

Презентация к статье здесь.

Четвертая из 30 тем будет скоро доступна. Если вы хотите быстрее получить доступ – присоединяйтесь к онлайн группе, ссылка доступна в описании курса.