Внешний ключ

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

История и стандартизация

Концепция внешнего ключа была разработана в рамках реляционной модели данных, предложенной Эдгаром Коддом в 1970 году. В своей работе «A Relational Model of Data for Large Shared Data Banks» Кодд сформулировал требования к целостности данных, включая целостность сущностей (первичный ключ) и ссылочную целостность (внешний ключ). Практическая реализация внешних ключей появилась в коммерческих системах управления базами данных (СУБД), начиная с 1980-х годов, например, в Oracle, DB2 и Ingres.

Механизм внешнего ключа был стандартизирован в языке SQL (ISO/IEC 9075) начиная с SQL-92, хотя ранние реализации в некоторых СУБД (например, MySQL версии 3.23) поддерживали синтаксис внешнего ключа, но игнорировали его семантику. Современные реляционные СУБД (PostgreSQL, Microsoft SQL Server, MySQL/InnoDB, Oracle, SQLite) в полной мере поддерживают внешние ключи, обеспечивая проверку ограничений на стороне сервера.

Определение и синтаксис

В языке SQL внешний ключ объявляется с помощью ограничения FOREIGN KEY при создании или изменении таблицы. Основной синтаксис выглядит следующим образом:

``sql CREATE TABLE дочерняя_таблица ( id INTEGER PRIMARY KEY, родитель_id INTEGER, FOREIGN KEY (родитель_id) REFERENCES родительская_таблица(ключевой_столбец) ); ``

Здесь:

дочерняя_таблица — таблица, содержащая внешний ключ;
родитель_id — столбец (или набор столбцов) в дочерней таблице;
REFERENCES — указывает на родительскую таблицу и её столбец (обычно первичный или уникальный ключ).

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

Ограничения ссылочной целостности

При объявлении внешнего ключа CУБД автоматически проверяет соблюдение следующих правил:

Запрет вставки или обновления: В дочернюю таблицу нельзя вставить строку, значение внешнего ключа которой отсутствует в родительской таблице (если только оно не равно NULL и это допускается). Аналогично, при обновлении значения внешнего ключа в дочерней таблице новое значение должно существовать в родительской таблице.
Защита при удалении или обновлении в родительской таблице: По умолчанию (в зависимости от поведения, заданного опциями ON DELETE и ON UPDATE) при попытке удалить или изменить ключевое значение в родительской таблице, на которое есть ссылки в дочерней таблице, СУБД либо запрещает операцию, либо автоматически выполняет одно из предопределённых действий.

Поведения при удалении и обновлении

Стандарт SQL определяет несколько опций, которые задаются после REFERENCES:

ON DELETE RESTRICT (или ON DELETE NO ACTION в некоторых СУБД, с некоторыми различиями) — запрещает удаление строки из родительской таблицы, если на неё есть ссылки в дочерней таблице. Операция завершается ошибкой.
ON DELETE CASCADE — при удалении строки из родительской таблицы все строки в дочерней таблице, ссылающиеся на неё, также автоматически удаляются.
ON DELETE SET NULL — при удалении строки из родительской таблицы значения внешнего ключа во всех ссылающихся строках дочерней таблицы устанавливаются в NULL (при условии, что столбец допускает NULL).
ON DELETE SET DEFAULT — при удалении ссылаемые строки получают значение по умолчанию, заданное для столбца внешнего ключа.
ON UPDATE ... — аналогичные опции применяются при изменении ключевого значения в родительской таблице: CASCADE (изменение значения в дочерних строках), SET NULL, SET DEFAULT, RESTRICT.

Выбор подходящего поведения зависит от бизнес-логики приложения. Например, для таблицы «Заказы» и «Позиции заказа» логичным является ON DELETE CASCADE, так как удаление заказа должно приводить к удалению всех его позиций. В то же время для таблицы «Сотрудники» и «Отделы» может использоваться SET NULL при удалении отдела, чтобы сотрудники не терялись, а получали неопределённое значение отдела.

Типы связей и проектирование

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

Один-ко-многим (1:M) — наиболее распространённый тип. Один первичный ключ в родительской таблице может быть связан с несколькими строками в дочерней таблице. Например, таблица «Категории» (один канал) и таблица «Товары» (множество товаров в категории). В дочерней таблице в столбце внешнего ключа хранится значение первичного ключа родительской таблицы.
Многие-ко-многим (M:N) — прямая реализация внешним ключом невозможна; для этого создаётся связующая таблица, в которой хранятся два внешних ключа, ссылающихся на первичные ключи обеих соединяемых таблиц. Например, таблицы «Студенты» и «Курсы» соединяются через таблицу «Записи на курсы», содержащую student_id и course_id.
Один-к-одному (1:1) — реализуется путём объявления уникального ограничения на столбец внешнего ключа в дочерней таблице, что гарантирует, что каждая строка родительской таблицы может иметь не более одной связанной строки.

Внешний ключ может ссылаться на ту же таблицу, где он определён (рекурсивный внешний ключ). Пример: в таблице «Сотрудники» может быть столбец manager_id, являющийся внешним ключом к столбцу employee_id той же таблицы, что позволяет организовать иерархическую структуру подчинения.

Влияние на производительность

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

Для повышения производительности разработчики могут:

индексировать столбцы внешнего ключа (хотя многие СУБД автоматически создают индекс при объявлении внешнего ключа);
использовать подходящие стратегии кэширования;
временно отключать проверку внешних ключей (например, в СУБД MySQL при использовании движка InnoDB с помощью команды SET FOREIGN_KEY_CHECKS=0;), однако это должно делаться с осторожностью, так как может привести к нарушению ссылочной целостности.

Отличия от других типов ограничений

Внешний ключ следует отличать от:

Первичного ключа (PRIMARY KEY) — уникально идентифицирует каждую строку в таблице и не может быть NULL; внешний ключ не требует уникальности и может содержать NULL.
Уникального ограничения (UNIQUE) — гарантирует уникальность значений в столбце или наборе столбцов, в то время как внешний ключ связывает данные между таблицами.
Проверочного ограничения (CHECK) — задаёт произвольное логическое условие для значений столбца, не обязательно связанное с другой таблицей.

Примеры использования в реальных системах

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

В единой системе межведомственного электронного взаимодействия (СМЭВ) при обмене данными между ведомствами используются реляционные базы данных с внешними ключами, связывающими таблицы заявок, ответов и справочников.
В системах учёта персонала (например, в «1С:Зарплата и управление персоналом») штатное расписание связано внешним ключом с таблицей подразделений, а записи сотрудников — с таблицей должностей.
В интернет-магазинах или электронных каталогах заказы и их позиции связываются через внешние ключи, что позволяет при удалении заказа удалять все его товары (поведение CASCADE).

Критика и ограничения

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

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

Источники

Кодд Э. Ф. «A Relational Model of Data for Large Shared Data Banks» (1970)
ISO/IEC 9075:2016 «Information technology — Database languages — SQL»
Гарсиа-Молина Г., Ульман Дж., Уидом Дж. «Системы баз данных. Полный курс»
Документация СУБД PostgreSQL, MySQL, Microsoft SQL Server, Oracle

BFOmetr — база данных и аналитика по компаниям России.

На главную BFOmetr →