Внешние ключи в базах данных

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

Внешний ключ — важный атрибут реляционных баз данных (БД)

Он связывает информацию, которая хранится в разных таблицах БД MySQL, PostgreSQL или Oracle, и обеспечивает целостность данных — принцип, который мы подробно раскрыли в статье «Виды баз данных».

Чтобы понять, что такое внешний ключ в базе данных, надо рассмотреть две таблицы. Первая — «Клиенты». Здесь столбец ID содержит уникальные номера для каждого покупателя, а остальные поля фиксируют имя и адрес электронной почты.

Столбец ID — это первичный ключ, уникальный идентификатор, который однозначно определяет каждую запись.

Клиенты

Теперь разберемся, как определить внешний ключ в базе данных. Для этого рассмотрим вторую таблицу — «Заказы». Она содержит столбец «Клиент_ID», который указывает на соответствующий ID из таблицы «Клиенты». Это и будет внешний ключ, который сопоставит заказы с конкретными покупателями.

Заказы

Благодаря такому механизму заказ с номером 1001 связывается с заказчиком, чей идентификатор равен 1. Это не только упрощает поиск информации, но и помогает обновлять ее при изменении записей.

Функции атрибута

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

• Сохранить целостность данных. Когда вы добавляете новую запись о заказе, система проверяет, существует ли покупатель с указанным номером в таблице клиентов. Если нет, то заказ не сохраняется, что предотвращает ошибки.

• Убрать «осиротевшие» записи. Когда вы удаляете сведения о клиенте, система либо автоматически удаляет все заказы, связанные с ним, либо наоборот — не позволяет удалить его, пока заказы остаются в БД. Это гарантирует, что все данные всегда будут связаны между собой.

• Упростить работу со статистикой. Если вам нужно узнать, какие заказы относятся к конкретному покупателю, внешний ключ поможет быстро отфильтровать записи в таблице заказов по номеру этого клиента, что сэкономит время при поиске.

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

Проводить диагностику системы, оптимизировать запросы и масштабировать приложение с внешними ключами намного проще. Значит, сокращаются затраты на поддержку и развитие IT-инфраструктуры.

Организация взаимосвязей

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

Один ко многим

В этой модели одна запись в главной таблице может быть связана сразу с несколькими записями в зависимой. Например, в табличке «Клиенты» у каждого покупателя есть уникальный идентификатор, который затем используется в табличке «Заказы». 

Клиенты

Заказы

Здесь покупатель с ID = 1 (Иван Иванов) имеет два заказа (1001 и 1003), а покупатель с ID = 2 (Мария Петрова) — один заказ (1002).

Один к одному

Здесь каждая запись из одной таблицы соответствует ровно одной записи в другой. Это можно использовать, например, для хранения дополнительной информации о сотрудниках: основная информация находится в перечне «Сотрудники», а расширенные сведения — в списке «Паспорта». 

Сотрудники

Паспорта

Здесь каждый сотрудник имеет ровно один паспорт, и наоборот.

Многие ко многим

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

Как пример, база данных EdgeЦентр, где одна таблица хранит список клиентов, а другая — перечень предлагаемых сервисов (Облако, CDN, DNS, Стриминг, Защита и так далее). Вспомогательная таблица фиксирует, какими именно сервисами пользуется каждый клиент.

Выглядит это так:

Клиенты

Сервисы

Записи использования сервисов

Здесь клиент с ID = 1 (Иван Иванов) использует два сервиса (Облако и DNS), а клиент с ID = 2 (Мария Петрова) — только Облако.

Специальные опции

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

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

• ON UPDATE CASCADE. Позволяет системе автоматически обновлять внешние ключи, если меняется значение первичного ключа в главной таблице. Например, если покупателю присваивается новый идентификатор в списке «Клиенты», то все записи в «Заказах», которые ссылаются на этот идентификатор, автоматически получают новое значение.

• ON DELETE CASCADE. Если вы уберете запись из главной таблицы, то все записи, связанные с ней в зависимой, удалятся автоматически. Например, если из «Клиентов» удаляют информацию о покупателе, все его покупки из «Заказов» тоже будут убраны. 

• ON DELETE SET NULL. В этом случае при удалении записи в главной таблице связанные внешние ключи отключаются, но сами записи в зависимой таблице сохраняются. Например, если клиент удаляется из «Клиентов», покупки останутся в «Заказах», а поле «Клиент_ID» для этих записей будет пустым. Это позволяет сохранить историю заказов и одновременно уведомить о том, что связь с удаленным клиентом утрачена.

С этими настройками БД становится еще более структурированной и надежной.

Как используют внешние ключи в реальных проектах

Рассмотрим несколько примеров из практики, чтобы понять, как этот инструмент помогает структурировать информацию и упрощает работу с БД.

• В системах управления контентом внешние ключи связывают статьи с категориями. Например, получится в один клик найти все статьи в категории «Технологии». 

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

• В интернет-магазинах ключи помогают точно определить, кто оформил покупку и какие товары купил. Например, чтобы найти все заказы клиента с ID = 1, достаточно выполнить запрос, который использует связь через внешний ключ.

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

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

Как внешний ключ влияет на производительность приложений

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

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

Чтобы минимизировать возможное негативное влияние на производительность, рекомендуем:

• Индексировать столбцы, связанные с ключами. Например, если в «Заказах» колонка «Клиент_ID» используется для связи с «Клиентами», то создание индекса на этой колонке ускорит поисковые запросы по заказам конкретного покупателя. 

• Настраивать внешние ключи с помощью подходящих опций. Правильное проектирование позволяет избежать ситуаций, когда избыточные проверки или ненужные связи приводят к задержкам в выполнении запросов. Оптимизация структуры и выбор нужных опций, например выбор между каскадным удалением и установкой NULL, помогают снизить количество ненужных операций. А чтобы разработчику не пришлось удалять и обновлять связанные данные вручную, пригодятся опции ON DELETE CASCADE и ON UPDATE CASCADE — он это делают автоматически. 

• Обеспечить совместимость типов данных. Это поможет исключить ошибки при формировании запросов, ускорить проверку и минимизировать риск сбоев при обновлении или добавлении информации. Например, если первичный ключ в таблице «Клиенты» имеет тип INTEGER, то внешний ключ в таблице «Заказы» также должен быть INTEGER.

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