MySQL UNIQUE KEYの変更

概要

MySQLのUNIQUE KEYの変更がすんなりいかないということだけは記憶に残っていましたが、方法が記憶に残っていなかったので、備忘録的に記載します。

前提

以下のようなTableを想定します。

mysql> SHOW CREATE TABLE articles;

CREATE TABLE `articles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tab_id` int(10) unsigned NOT NULL,
  `articles` mediumtext NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_tab_id` (`tab_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4

mysql> ALTER TABLE articles ADD COLUMN experiment_sign varchar(64) AFTER tab_id;

Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

このarticles Tableにexperiment_sign Columnを追加したとします。

mysql> SHOW CREATE TABLE articles

CREATE TABLE `articles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tab_id` int(10) unsigned NOT NULL,
  `experiment_sign` varchar(64) NOT NULL,
  `articles` mediumtext NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_tab_id` (`tab_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4

experiment_sign Columnがvarchar(64)で追加されているのが確認できると思います。
そして、UNIQUE KEYがtab_idのみになっていますが、これにexperiment_signを追加して、下記のような変更を行いたいと仮定します。

UNIQUE KEY `idx_tab_id (`tab_id`)

↓

UNIQUE KEY `idx_tab_id_experiment_sign` (`tab_id`, `experiment_sign`)

さあ、このときどうするかというのが、今回のお話です。

失敗例

何も考えず、とりあえずUNIQUE KEY追加すればええやろと、安易に考えると、下記のようなALTER文を書いてしまいます。私です。

ALTER TABLE articles ADD UNIQUE `idx_tab_id_experiment_sign (`tab_id`, `experiment_sign`)

こうすると、どうなるかというと

CREATE TABLE `articles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tab_id` int(10) unsigned NOT NULL,
  `experiment_sign` varchar(64) NOT NULL,
  `articles` mediumtext NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_tab_id` (`tab_id`)
  UNIQUE KEY `idx_tab_id_experiment_sign` (`tab_id`, `experiment_sign`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4

とまあ、UNIQUE KEYが二つつきますね。はい。
これでは、わけわからんことになりますね。見た目も、無駄に誤解を招きます。

正解例

ちゃんと既存UNIQUE KEYを潰しましょう。

mysql> ALTER TABLE articles ADD UNIQUE `idx_tab_id_experiment_sign (`tab_id`, `experiment_sign`);
mysql> ALTER TABLE articles DELETE INDEX `idx_tab_id;

こうすると、理想の形ができあがります。

CREATE TABLE `articles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tab_id` int(10) unsigned NOT NULL,
  `experiment_sign` varchar(64) NOT NULL,
  `articles` mediumtext NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_tab_id_experiment_sign` (`tab_id`, `experiment_sign`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4

終わり

ちゃんと、理想の形になっているか確認しましょう。以上です。
ああ、もちろん、UNIQUE KEYはNOT NULL制約をつけましょう。