MySQLのUnique Key変更で外部制約キーが問題になった経験と対処法

本記事は ZOZO Advent Calendar 2023 シリーズ 7 の 13 日目の記事です。

概要

MySQLのUnique Keyの変更時に外部制約が絡んで、手こずった経緯を備忘録としてまとめます。

前提

以下のようなテーブルを想定します

CREATE TABLE module_config (
       id                       BIGINT         AUTO_INCREMENT,
       module_id                BIGINT         NOT NULL,
       gender                   VARCHAR(20)    NOT NULL,
       device                   VARCHAR(20)    NOT NULL,
       position                 INT            NOT NULL,
       created_at               DATETIME       NOT NULL    DEFAULT CURRENT_TIMESTAMP,
       updated_at               DATETIME       NOT NULL    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
       PRIMARY KEY (id),
       FOREIGN KEY (module_id) REFERENCES modules (id) ON DELETE CASCADE ON UPDATE CASCADE,
       UNIQUE (module_id, gender, position)
);

このときUNIQUEの設定を以下のように変更したい

UNIQUE (module_id, gender, device)

やったこと

既存の UNIQUE KEY を制御するために制約名を調査しました。UNIQUE KEYに制約名を指定していなかったことが原因で、変更や調査が複雑になっていました...

mysql> SHOW CREATE TABLE module_config;

| module_config | CREATE TABLE `module_config` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `module_id` bigint NOT NULL,
  `gender` varchar(20) COLLATE utf8mb4_bin NOT NULL,
  `device` varchar(20) COLLATE utf8mb4_bin NOT NULL,
  `position` int NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `module_id` (`module_id`,`gender`,`position`),
  CONSTRAINT `module_config_ibfk_1` FOREIGN KEY (`module_id`) REFERENCES `home_modules` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |

ここで、UNIQUE KEY の制約名は module_id と判明しました。定義していなかった場合は自動でデフォルトでカラム名が利用されるんですね。（知らなかった）
次に、UNIQUE KEY の制約名である module_id を用いてUNIQUEの削除を試みると、module_id は外部制約キーとして扱われているため削除できませんでした

mysql> ALTER TABLE module_config DROP INDEX module_id;

ERROR 1553 (HY000): Cannot drop index 'module_id': needed in a foreign key constraint

この問題の解決には、外部制約キーを一時的に削除する必要があります。以下が対応手順です。

外部制約キーを削除します。

mysql> ALTER TABLE module_config DROP FOREIGN KEY module_config_ibfk_1(外部制約キーの名前);

既存の UNIQUE KEY を削除します。

mysql> ALTER TABLE module_config DROP INDEX module_id(UNIQUEキーの名前);

変更後の UNIQUE KEY を追加します

mysql> ALTER TABLE module_config ADD UNIQUE INDEX unique_module_config (module_id, gender, device);

外部制約キーを再度追加します。

mysql> ALTER TABLE module_config ADD FOREIGN KEY (module_id) REFERENCES modules(id);

これにより、一時的に外部制約を削除し、その後 UNIQUE KEY を削除し、修正した UNIQUE KEY を追加した後、最後に外部制約を再追加することで問題が解決されました。

終わりに

今回はUNIQUE KEYの名前を指定しなかったことから、自動でデフォルトの制約名と外部制約キーが同名になりややこしくなりました。今後はUNIQUE KEYを設定する際はきちんと制約名を定義するよう心がけます。