SQL: Внешние ключи (FOREIGN KEY)
Создание внешних ключей в MySQL важно для обеспечения целостности данных между таблицами. Внешние ключи позволяют автоматически поддерживать соответствие между связанными таблицами, например, чтобы убедиться, что для каждого заказа существует клиент.
Пример: Предположим, есть две таблицы: clients (клиенты) и orders (заказы). Мы хотим создать внешний ключ в таблице orders, который будет ссылаться на первичный ключ в таблице clients. Это гарантирует, что каждый заказ может быть связан только с существующим клиентом.
Шаг 1: Определение первичного ключа
Сначала убедимся, что в таблице clients есть первичный ключ. Обычно это столбец clientID.
CREATE TABLE clients (
clientID INT AUTO_INCREMENT,
clientName VARCHAR(255),
PRIMARY KEY (clientID)
);
Шаг 2: Создание таблицы с внешним ключом
Создадим таблицу orders, в которой будет внешний ключ, указывающий на clientID в таблице clients.
# В этом определении FOREIGN KEY (clientID) создает внешний ключ,
# который ссылается на clientID в таблице clients.
# REFERENCES clients(clientID) указывает,
# что внешний ключ должен соответствовать значению clientID в таблице clients.
CREATE TABLE orders (
orderID INT AUTO_INCREMENT,
orderDate DATE,
clientID INT,
PRIMARY KEY (orderID),
FOREIGN KEY (clientID) REFERENCES clients(clientID)
);
Ограничения внешнего ключа
Можно управлять поведением внешних ключей при удалении или обновлении связанных данных с помощью опций ON DELETE и ON UPDATE.
ON DELETE
ON DELETE CASCADE: если строка в родительской таблице (clients) удаляется, то все связанные строки в дочерней таблице (orders) также удаляются.
ON DELETE SET NULL: при удалении строки в родительской таблице соответствующие значения внешних ключей в дочерней таблице устанавливаются в NULL. Для этого столбец в дочерней таблице должен допускать NULL.
ON DELETE RESTRICT: этот режим запрещает удаление строки из родительской таблицы, если существуют связанные строки в дочерней таблице. Это помогает предотвратить удаление данных, на которые есть ссылки из других таблиц, обеспечивая тем самым целостность данных.
ON DELETE NO ACTION: по сути, похож на RESTRICT. Стандарт SQL предполагает, что NO ACTION означает проверку целостности данных в конце транзакции, а не немедленно после удаления или изменения. Однако, на практике во многих СУБД NO ACTION работает так же, как RESTRICT, немедленно запрещая операцию, нарушающую целостность.
ON UPDATE
ON UPDATE CASCADE: аналогично ON DELETE CASCADE, если первичный ключ (или уникальный ключ) в родительской таблице изменяется, то все соответствующие значения внешних ключей в дочерней таблице будут автоматически обновлены. Это полезно для поддержания актуальных связей между таблицами при изменении идентификаторов.
ON UPDATE SET NULL: если первичный ключ в родительской таблице изменяется, соответствующие значения внешних ключей в дочерней таблице устанавливаются в NULL. Как и в случае с ON DELETE SET NULL, для использования этой опции столбец внешнего ключа в дочерней таблице должен допускать NULL.
ON UPDATE RESTRICT: запрещает изменение ключа в родительской таблице, если существуют зависимые строки в дочерней таблице. Это помогает избегать ситуаций, когда изменение ключа в родительской таблице могло бы нарушить связи с дочерними таблицами.
ON UPDATE NO ACTION: как и ON DELETE NO ACTION, официально предполагает проверку целостности в конце транзакции, но на практике часто ведет себя как RESTRICT, предотвращая немедленное нарушение целостности данных.
Пример с ON DELETE CASCADE:
CREATE TABLE orders (
orderID INT AUTO_INCREMENT,
orderDate DATE,
clientID INT,
PRIMARY KEY (orderID),
FOREIGN KEY (clientID) REFERENCES clients(clientID) ON DELETE CASCADE
);
Остутсвие внешних ключей
Остутсвие внешних ключей может повлиять на управление данными и их целостность. В зависимости от требований к проекту и специфики работы с данными, можно обойтись без внешних ключей, но важно понимать возможные риски и быть готовым к дополнительной работе по поддержанию целостности и связности данных.
Отсутствие автоматической проверки целостности данных
Без внешних ключей система управления базами данных (СУБД) не будет автоматически проверять связи между таблицами. Это значит, что можно случайно вставить в дочернюю таблицу идентификатор, который не существует в родительской таблице, что приведёт к нарушению целостности данных.
Нужно вручную обрабатывать связи между данными
Придётся самостоятельно управлять связями между таблицами при вставке, обновлении и удалении записей. Это увеличивает риск ошибок и усложняет код приложения.
Сложность поддержки
При отсутствии внешних ключей поддерживать целостность данных становится сложнее, особенно в больших и сложных базах данных с множеством связей между таблицами. Разработчики должны внимательно следить за каждой операцией, чтобы не допустить нарушения целостности.
Отсутствие каскадного обновления и удаления
Внешние ключи позволяют автоматически обновлять или удалять связанные записи с помощью параметров ON UPDATE CASCADE и ON DELETE CASCADE. Без них придётся вручную обновлять или удалять все связанные записи, что увеличивает количество необходимых операций и риск ошибок.
Увеличение сложности запросов
Для обеспечения целостности данных без внешних ключей, возможно, придётся использовать более сложные запросы и дополнительные проверки на стороне приложения или в хранимых процедурах.
Проблемы с производительностью
Хотя внешние ключи могут незначительно снизить производительность из-за дополнительных проверок целостности, правильное использование индексов и внешних ключей часто улучшает общую производительность за счёт оптимизации запросов.
Комментарии