Добавление столбца и создание внешнего ключа в MySQL

Быстрый ответ

ALTER TABLE `child` ADD `parent_id` INT, ADD FOREIGN KEY (`parent_id`) REFERENCES `parent`(`id`);

Чтобы в одну команду включить добавление столбца и его декларацию как внешнего ключа, используйте выражение ALTER TABLE. Приведенный выше пример демонстрирует добавление в таблицу child столбца parent_id, связанного через внешний ключ с полем id в таблице parent. Такой подход обеспечивает сохранение целостности связей между данными.

Синтаксис шаблона:

ALTER TABLE `дочерняя_таблица` ADD `колонка_внешнего_ключа` ТИП_ДАННЫХ, ADD FOREIGN KEY (`колонка_внешнего_ключа`) REFERENCES `родительская_таблица`(`столбец_родительской_таблицы`);

• дочерняя_таблица: Таблица, где внешний ключ будет добавляться в виде нового столбца.
• колонка_внешнего_ключа: Название нового столбца, содержащего внешний ключ.
• ТИП_ДАННЫХ: Нужно точное соответствие типа данных поля, на которое ссылается внешний ключ.
• родительская_таблица: Таблица, где расположен столбец с первичным ключом, являющимся опорой для внешнего ключа.
• столбец_родительской_таблицы: Поле с первичным ключом в родительская_таблица, для которого будет установлена связь.

Обеспечьте, чтобы ТИП_ДАННЫХ был согласованным с типом данных в столбец_родительской_таблицы и чтобы в родительская_таблица имелся объявленный первичный ключ.

Освоение MySQL-маневров

Управление значениями по умолчанию

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

-- Ловко устанавливаем ноль как значение по умолчанию, чтоб избежать непредвиденных ошибок.
ALTER TABLE `child` ADD `parent_id` INT DEFAULT 0, ADD FOREIGN KEY (`parent_id`) REFERENCES `parent`(`id`);

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

Подробнее об этом расскажет наш спикер на видео

Балансировка действий ON DELETE и ON UPDATE

Столбец с внешним ключом играет функциональную роль, поэтому определите ON DELETE и ON UPDATE, чтобы MySQL знал, как реагировать на изменения:

-- Если удаляется запись из родительской таблицы — удаляем и соответствующие записи в дочерней (CASCADE). 
-- Если родительская запись обновляется — запрещаем обновления, чтобы обеспечить консистентность данных (RESTRICT).
ALTER TABLE `child`
  ADD `parent_id` INT,
  ADD FOREIGN KEY (`parent_id`) REFERENCES `parent`(`id`)
  ON DELETE CASCADE
  ON UPDATE RESTRICT;

CASCADE позволяет автоматически удалять связанные записи, в то время как RESTRICT предотвращает удаление или обновление в случае, если существуют зависимые строки.

Визуализация

Допустим, добавление внешнего ключа и столбца — это построение моста между двумя "городами", символизирующими ваши таблицы:

🏙️ Таблица А (TABLE_A)    : Имеет уже существующие поля.
🏙️ Таблица Б (TABLE_B)    : Имеет уже существующие поля + новое поле 🆕 'new_column'.

Построим этот мост, используя наш надежный инструмент ALTER TABLE:

ALTER TABLE TABLE_B ADD COLUMN new_column INT, ADD FOREIGN KEY (new_column) REFERENCES TABLE_A(id);

🏙️🌉🏙️
# Ура! Столбец 'new_column' в таблице Б теперь связан с 'id' в таблице А, и мы достигли этого всего одним эффективным действием — с помощью команды ALTER TABLE.

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

Предотвращение возможных проблем

Собщетимость типов данных

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

Тестирование перед внедрением

Прежде чем внедрить запрос ALTER TABLE в продакшн, протестируйте его в безопасной среде. Используйте тестовый сервер для оценки поведения установленного внешнего ключа и проведите операции INSERT и DELETE. Это поможет обеспечить сохранение целостности данных.

Консистентность именования

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

Полезные материалы

1. MySQL :: Руководство по MySQL 8.0 :: 15.1.20.5 Ограничения FOREIGN KEY — Официальная документация MySQL.
2. SQL FOREIGN KEY Constraint — Подробная информация о использовании ограничений внешнего ключа в SQL.
3. Внешние ключи – База знаний MariaDB — Ресурс с примерами и полезной информацией по управлению внешними ключами.