Lyricrime.com
Blog
Profile
2024/11/16

データベースのマイグレーション時にインデックスを削除する場合に注意すべき外部キー制約とインデックスの依存関係

はじめに

今回は業務のなかで少しつまづいたポイントについて自分の備忘録としてまとめたいと思います。

MySQLにおける外部キー制約作成時の既存インデックスの利用についてです。

対象システム

  • MySQL 8.0
  • Laravel 11

TL;DR

  • 特定のインデックスを削除する場合、特定の外部キー制約がインデックスを必要としている場合にエラーになる
  • 外部キー制約の作成時には自動的にインデックスが作成される
  • 外部キー制約の作成時に、既存のインデックスが存在する場合はインデックスの作成をせず、既存のインデックスを利用する。条件は以下の通り
    • 外部キーとして利用するカラムが、既存のインデックスの1つ目のカラムと一致している場合
      • これは複合インデックスの場合でも最初のカラムであれば利用される

背景

担当しているプロジェクトにおいて、開発した機能をステージング環境にデプロイする際に、マイグレーションファイルを実行するとエラーが発生しました。

SQLSTATE[HY000]: General error: 1553 Cannot drop index 'unique_index_name': needed in a foreign key constraint (Connection: mysql, SQL: alter table `table_name` drop index `unique_index_name`)

このエラー自体は、特定のインデックスを削除しようとした際にそのインデックスが外部キー制約によって必要とされているために発生します。

つまり、ステージング環境には複合外部キー制約Aと複合外部キー制約B、ユニークインデックスBが存在していました。

この複合外部キー制約BとユニークインデックスBは同時に作られたものです。
複合外部キー制約Bの作成時には自動的にユニークインデックスBが作成されていました。
スキーマを確認し、複合外部キー制約Bを消してからユニークインデックスBを削除する必要があると判断しました。

しかし、実際には外部キー制約Bが存在しない状態で、外部キー制約Aのみが存在する状態でユニークインデックスBを削除しようとするとエラーが発生しました。

外部キー制約AもユニークインデックスBに依存していることが原因でした。

仕様

試行錯誤の結果たどり着いた仮説でしたが、MySQLにおいては外部キー制約の作成時に既存のインデックスを利用する仕様がありました。

MySQLのInnoDBストレージエンジンにおいて、外部キー制約を設定する際に、参照元テーブルのカラムに対してインデックスが必要です。
このインデックスは自動的に作成されますが、特定の条件下では既存のインデックスを利用することがあります。
今回のケースではこの仕様を正しく理解できておらず、一見無関係に見える外部キー制約Aを考慮していないことが原因でした。

特に、複合ユニークインデックスの場合に気付きにくい仕様です。

複合ユニークインデックスとは

複合ユニークインデックスとは、複数のカラムに対してユニーク制約を持たせるインデックスのことです。

複数のカラムに対してユニークな組み合わせを保証します。例えば、(A, B)というインデックスはカラムAとカラムBの組み合わせがユニークであることを保証します。

MySQLが既存の複合ユニークインデックスを外部キー制約に再利用するための条件は次のようになります。

  • 外部キー列がインデックスの先頭列であること
    • 外部キーとして指定する列が、既存のインデックスの先頭（最も左側）に位置している必要があります。つまり、外部キー列がインデックスの最初のカラムである場合、そのインデックスを外部キー制約に利用できます。

公式ドキュメント内の記述は以下の通りです。

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

例： 外部キー列が既存の複合インデックスの先頭にある場合

CREATE TABLE parent_table (
    id BIGINT UNSIGNED PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE child_table (
    child_id BIGINT UNSIGNED PRIMARY KEY,
    parent_id BIGINT UNSIGNED,
    other_column VARCHAR(255),
    UNIQUE KEY unique_parent_other (parent_id, other_column),
    FOREIGN KEY (parent_id) REFERENCES parent_table(id)
);

この場合、child_table の parent_id 列は既に (parent_id, other_column) という複合ユニークインデックスの先頭に位置しています。したがって、MySQLはこの既存のインデックスを外部キー制約のために再利用します。新たなインデックスを作成する必要はありません。

一見関係のないカラムの外部キー制約を削除しなければならない理由

今回の事象で発生した「一見関係のないカラムの外部キー制約を削除しなければならない」という問題は、インデックスの依存関係と外部キー制約の共有によるものです。具体的には、以下のような状況が考えられます：

  • 複合インデックスの共有利用
    複数の外部キー制約が同じ複合インデックスを共有している場合、インデックスを削除する前に、すべての外部キー制約を削除する必要があります。これは、インデックスが依然として外部キー制約によって使用されているためです。

  • インデックスの再利用による依存関係
    外部キー制約が複数の列に対して設定されている場合、これらの制約が同じインデックスを利用している可能性があります。特に、外部キー制約が複数の列を対象としている場合、それぞれの制約が同じ複合インデックスを使用することがあります。

まとめ

データベースマイグレーションにおいて、外部キー制約とインデックスの依存関係の理解は非常に重要です。 今回のように複合ユニークインデックスを使用する場合、外部キー制約がインデックスの先頭列に依存しているかどうかを確認する必要があります。

  • 外部キー制約を設定する前に、外部キー列がインデックスの先頭に位置している場合は既存のインデックスに依存する
  • インデックスを削除する際は、そのインデックスに依存するすべての外部キー制約を事前に削除する。
©︎ 2024 taisei miyaji
github