Совет дня №35

В прошлый раз мы говорили об уникальных ограничениях, а еще есть внешние. Это когда значение поля обязано быть первичным ключом другой таблицы. Например, в профиле колонка user_id ссылается на пользователя: таблицу users и ее первичный ключ id.

create table users (
    id serial primary key...
);

create table profiles(
    id serial primary key,
    user_id integer not null references users(id)
);

Ключевое слово references создает это ограничение автоматом. Можно задать его явно таким выражением:

alter table profiles
add constraint fk_profile_user_id
foreign key (user_id)
references users (id);

Первичные ключи бывают составными, и тогда внешний ключ тоже составной.

Когда создано подобное ограничение, профиль не может ссылаться на пользователя, которого не существует. При этом значения NULL допускаются, потому что он не равен другим нуллам. Если поле не должно содержать нуллы, это задается свойством NOT NULL.

Поскольку первичный ключ всегда индексирован, проверка внешнего ключа работает быстро. Например, мы добавляем профиль с полем user_id 100500. Проверка аналогична запросу ниже (обратите внимание, что из него ничего не выбирается):

select from users where id = 100500

Под капотом Postgres проверяет только индекс – нет смысла дергать таблицу — поэтому запроса в логах вы не увидите.

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

select * from profiles where user_id = 100500

Увы, это не так. По умолчанию внешний ключ не индексируется, и запрос выше даст full scan. Повесьте на него btree:

create index idx_profile_user_id
on profiles using btree (user_id);

Теперь обратная ссылка индексирована, и зная родительскую сущность, легко найти дочерние.

Пример с профилем можно сделать еще строже: мы допускаем только один профиль на пользователя, поэтому повесим на user_id уникальный индекс:

create UNIQUE index ...

Индексы на внешние ключи обязательны, если используется join. Скажем, вы строите отчет по всем пользователям системы:

select * from users u
left join profiles p on u.id = p.user_id

Без индекса на profiles.user_id запрос будет очень тяжелым. Зато если поле индексировано и вдобавок обе таблицы отсортированы по коду пользователя, возможно, сработает Merge Join – соединение слиянием. Это самый быстрый алгоритм соединения. Принцип у него такой же, как и у сортировки слиянием.

Внешние ключи вносят в базу ограничения, и желательно следовать им. В противном случае вы рискуете создать “висяки” – ссылки, ведущие в никуда. Как и в случае с дублями, их тяжело расследовать и удалять.

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