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. Без них придётся вручную обновлять или удалять все связанные записи, что увеличивает количество необходимых операций и риск ошибок.

Увеличение сложности запросов

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

Проблемы с производительностью

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