Как создать внешний ключ в SQL

Чтобы объяснить принцип работы ключа, приведем пример. В таблице «Заказы» есть поле «id_клиента». В таблице «Клиенты» — первичный ключ id. Именно на него ссылается поле «id_клиента» из таблицы «Заказы». Оно и будет внешним ключом. 

В этом примере Foreign Key помогает связать заказы с клиентами, которые их сделали. Благодаря ему компания может отслеживать соответствующую информацию и поддерживать актуальность сведений в таблице. 

Особенности внешнего ключа: 

• Foreign Key бывает не единственным, если нужно обеспечить связь одной таблицы с несколькими. 

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

• Столбец внешнего ключа может быть nullable (допускать значение NULL). Это определяется при создании таблицы так же, как и для любого другого столбца. Значение NULL в таком столбце интерпретируется как «ссылка отсутствует» и не проверяется на соответствие записям в родительской таблице.

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

Поведение по умолчанию варьируется в зависимости от СУБД:

• В MySQL (с движком InnoDB) индекс для столбцов внешнего ключа создается автоматически, если подходящий индекс уже не существует.

• В PostgreSQL и Microsoft SQL Server индекс не создается автоматически при объявлении ограничения FOREIGN KEY. Разработчик должен создать его явно командой CREATE INDEX. Отсутствие индекса может привести к серьезному падению производительности при работе со связанными данными.

Преимущества использования Foreign Key

Внешний ключ нужен для:

• Обеспечения ссылочной целостности данных. Foreign Key предотвращает появление «висячих» ссылок на несуществующие записи в другой таблице. 

• Декларативное управление целостностью при изменениях. При определении внешнего ключа можно задать правила (ON DELETE и ON UPDATE), которые определят, что произойдет со связанными записями при изменении или удалении родительской записи. Это позволяет автоматизировать поддержку целостности, например, удалить все позиции заказа при удалении самого заказа (CASCADE) или обнулить ссылку на отдел при его расформировании (SET NULL). 

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

• Улучшения производительности SQL-запросов. Для операций соединения таблиц (JOIN), использующих внешний ключ, критически важную роль играет наличие индекса на столбцах этого ключа. Само ограничение FOREIGN KEY лишь гарантирует целостность. Как отмечалось выше, в некоторых СУБД индекс создается автоматически, в других его нужно создавать явно. Правильно проиндексированные внешние ключи обеспечивают быстрый доступ к связанным записям.

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

Есть несколько типов связей с помощью внешнего ключа. «Один к одному» — каждой записи в первой таблице соответствует одна запись во второй. «Один ко многим» — одной записи в первой таблице может соответствовать несколько записей во второй. «Многие ко многим» — записи в обеих таблицах могут быть связаны с нескольким записями другой таблицы. 

Перед созданием выполните следующие условия: 

• • Проверьте, что в таблице, на которую будет ссылаться внешний ключ, есть первичный ключ. 

• • Проверьте совпадение типа данных и размеров столбца внешнего ключа с этими же показателями столбца первичного ключа. 

• • Убедитесь, что записи в таблице соответствуют будущему ограничению. 

• • Продумайте, как будете действовать при удалении или обновлении записей в разных таблицах, которые будут связаны. 

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

Если собираетесь создавать таблицу, можно одновременно объявить Foreign Key. Это позволит сразу определить структуру данных и связи между ними 

Команды для популярных СУБД

MySQL:

PostgreSQL:

Либо:

SQL Server:

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

ALTER TABLE

Синтаксис команды:

Где:

• fk_name — имя Foreign Key;

• column_name — столбец таблицы, который будет ссылаться на запись в другой таблице;

• parent_table(parent_column) — таблица и столбец, с которыми будет связь;

• [ON DELETE action] и [ON UPDATE action] — примеры действий с содержимым таблицы.

Примеры для СУБД

MySQL:

PostgreSQL:

SQL Server:

В удалении поможет команда ALTER TABLE с DROP CONSTRAINT. Синтаксис для MySQL, PostgreSQL и других популярных СУБД:

В MySQL можно использовать:

В PostgreSQL:

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

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

• В банковской системе таблица транзакций ссылается на таблицу счетов. Это обеспечивает привязку операций к конкретным счетам и позволяет избежать путаницы. 

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

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

Рассмотрим связь одной таблицы с несколькими. Допустим, у нас есть три таблицы: 

• Customers (клиенты) с идентификаторами CustomerID (Primary Key) и именами Name. 

• Orders (заказы) с уникальными идентификаторами заказов OrderID (Primary Key) и внешним ключом CustomerID, который ссылается на CustomerID в таблице Customers.

• OrderDetails (детали заказов) с идентификатором OrderDetailID и внешним ключом OrderID, который ссылается на OrderID в Orders. 

К одному клиенту может быть привязано несколько заказов. Получится связь «один ко многим» через CustomerID. Один заказ — это много деталей (название, описание, дата), что тоже формирует связь «один ко многим» через OrderID.

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

• Понятно именуйте внешние ключи. По названиям должно быть ясно, с какими таблицами установлены связи. 

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

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

• Осознанно применяйте каскадные операции. При неправильных настройках они могут привести к потере данных. 

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

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

Использование Foreign Key повышает надежность базы данных за счет контроля связей, быстрого выполнения запросов через индексацию и строгой архитектуры таблиц. При выборе управляемой СУБД важно, чтобы платформа полностью поддерживала эти стандартные механизмы SQL. Например, использование сервиса Evolution Managed PostgreSQL обеспечивает полную совместимость со всеми аспектами работы внешних ключей, включая сложные каскадные операции, а Evolution Managed Redis — предлагает гибкие модели данных для сценариев, где реляционные связи реализуются на уровне логики приложения.