MySQL で一意制約が削除できない

基本: 外部キーに使われているインデックスは削除出来ない

MySQL でインデックスを削除するときに、以下のようなエラーメッセージが出る事があります。

Cannot drop index 'some_index': needed in a foreign key constraint

普通に読めば、そのインデックスが外部キーによって使用されているので削除出来ない、という話です。解決方法としては

• インデックスはそのまま残す
• 外部キーを削除してからインデックスを削除する

といったところだと思います。

ここまでは当たり前の話なのですが、なぜこう言う状態になるのか分かりにくいケースがあったので、説明します。

分かりにくいケース

実例: 後から一意制約を追加した場合

以下の通りテーブルを2つ作成します。

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t1_id` int(11) NOT NULL,
  `col1` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_t2_t1_id` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

後から、t2 の t1_id と col1 にユニークインデックスを貼ります。

ALTER TABLE `t2` ADD CONSTRAINT `uq_t2_t1_id_col1` UNIQUE (`t1_id`, `col1`);

貼った後で、やっぱりユニークインデックスが不要になったので、削除します。

ALTER TABLE `t2` DROP INDEX `uq_t2_t1_id_col1`;

すると、以下のエラーが出ます。

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

「え？後から貼ったユニークインデックスって外部キーと関係無いでしょ？」

と思ってしまうと思います。（私は思いました。）

現象の説明

具体的には、以下のような事が起こっています。

1. テーブル t2 作成時に t2.t1_id から t1.id への外部キーを作成すると、自動的に t2.t1_id にインデックスが作成される
2. t2.t1_id と t2.col1 に対するユニークインデックスを作成すると、外部キーがそのユニークインデックスを使うようになるので、 t2.t1_id に対して自動的に作成されたインデックスが不要となり、自動的に削除される
3. ユニークインデックスを削除しようとすると、外部キーによって使われているので、エラーとなる

具体的に、SQL を見ていきます。

t1, t2 作成の SQL は上に書いた通りですが、t2 作成後に SHOW CREATE TABLE t2 をしてみると、

CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t1_id` int(11) NOT NULL,
  `col1` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_t2_t1_id` (`t1_id`), -- 自動的に作成されたインデックス
  CONSTRAINT `fk_t2_t1_id` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

ユニークインデックスを作成した後で、再度見てみると以下の通りになっています。

CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t1_id` int(11) NOT NULL,
  `col1` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_t2_t1_id_col1` (`t1_id`,`col1`),
  -- ↑が作成されたため、 KEY `fk_t2_t1_id` (`t1_id`), が削除されている
  CONSTRAINT `fk_t2_t1_id` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

自動的にインデックスが作成されたり削除されたりするので、それがこのケースを分かりにくくしています。

解決策

以下の通り、一度外部キーを削除した上でユニークインデックスを削除し、その上で再度外部キーをつければ解決します。

ALTER TABLE `t2` DROP FOREIGN KEY `fk_t2_t1_id`, DROP INDEX `uq_t2_t1_id_col1`;
ALTER TABLE `t2` ADD CONSTRAINT `fk_t2_t1_id` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`);

なぜこうなるのか、詳しい解説

挙動についての公式の説明

ドキュメントに記載されてます。

MySQL では、外部キーチェックを高速に実行でき、かつテーブルスキャンが必要なくなるように、外部キーおよび参照されるキーに関するインデックスが必要です。参照しているテーブルには、外部キーカラムが同じ順序で最初のカラムとしてリストされているインデックスが存在する必要があります。このようなインデックスが存在しない場合は、参照しているテーブル上に自動的に作成されます。このインデックスは、外部キー制約を適用するために使用できる別のインデックスを作成した場合、あとで暗黙のうちに削除される可能性があります。index_name (指定されている場合) は、前に説明したとおりに使用されます。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.17.2 外部キー制約の使用

インデックスが外部キーに使われる条件

先ほどのドキュメントには、以下の記載があります。

「外部キーカラムが同じ順序で最初のカラムとしてリストされているインデックスが存在する必要があります。」

上の例ですと、t2.t1_id から t1.id に対する外部キーなので、t1_id が最初のカラムとなっているインデックスが必要です。以下のユニークインデックスは、この条件を満たす（t1.id が最初のカラム）ため、このユニークインデックスが外部キーによって使われます。

ALTER TABLE `t2` ADD CONSTRAINT `uq_t2_t1_id_col1` UNIQUE (`t1_id`, `col1`);

逆に、同じテーブルに対して以下のようなユニークインデックスを追加してみます。

ALTER TABLE `t2` ADD CONSTRAINT `uq_t2_col1_t1_id` UNIQUE (`col1`, `t1_id`);

その後、SHOW CREATE TABLE t2 をしてみると、以下の通りになります。

CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t1_id` int(11) NOT NULL,
  `col1` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_t2_col1_t1_id` (`col1`,`t1_id`), -- これは外部キーによっては使われない
  KEY `fk_t2_t1_id` (`t1_id`), -- そのため、こちらのキーは削除されない
  CONSTRAINT `fk_t2_t1_id` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

複合キーの一部が使われる条件

では、t1_id に対する検索に対して、なぜ (col1,t1_id) のインデックスは使用されなくて、 (t1_id, col1) のインデックスは使用されるのでしょうか。

挙動に関してはドキュメントに記載があります。

テーブルにマルチカラムインデックスがある場合、オプティマイザは、インデックスの左端のプリフィクスを使用して行をルックアップできます。たとえば、(col1, col2, col3) に 3 カラムのインデックスがある場合、(col1)、(col1, col2)、および (col1, col2, col3) に対して、インデックス検索機能を使用できます。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.3.5 マルチカラムインデックス

なぜこういう挙動になるかは、インデックスの内部構造に関する理解が必要です。MySQL, B-tree, B+ tree 辺りで検索すると、多くの解説記事が見つかりますので、ここでは省略します。

まとめ

MySQL の外部キーを作成すると（使用可能なインデックスがない場合）自動的にインデックスが作成されます。そこまでは知っている方も割と多いと思いますが、その後に別のインデックスを作成すると、最初に作成されたインデックスが自動的に削除される場合があります。

なぜこういう挙動になっているかについては、MySQL のインデックスの仕組みを理解する必要があると思います。

参考

• MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.17.2 外部キー制約の使用
• MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.3.5 マルチカラムインデックス
• Foreign key subsumed by unique constraint in MariaDB/MySQL – Database Administrators Stack Exchange