MySQLのエラー『Cannot Add Foreign Key Constraint』の解決方法

MySQLで外部キー制約（FOREIGN KEY）を追加しようとした際に、『Cannot Add Foreign Key Constraint』というエラーが発生することがあります。このエラーは、テーブルの設計ミスやデータ型の不一致などが原因で発生します。この記事では、発生条件と解決策について詳しく説明します。

1. エラーの発生条件

『Cannot Add Foreign Key Constraint』エラーは、以下の条件で発生する可能性があります。

• 親テーブルの主キーまたはユニークキーが適切に設定されていない。
• 子テーブルの外部キーのデータ型と親テーブルの参照カラムのデータ型が一致していない。
• 使用するストレージエンジンが異なり、InnoDB以外を使用している。
• 外部キー制約を設定しようとしているカラムにインデックスがない。
• 参照先のテーブルが存在しない、または名前が間違っている。
• 外部キー制約に対応していないカラムの設定（TEXT, BLOB, JSONなど）が含まれている。

2. 発生するSQLとエラーメッセージの例

以下のSQLを実行すると、このエラーが発生する可能性があります。

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

この場合、エラーが発生した原因を確認する必要があります。

3. 解決策1: 親テーブルの主キーまたはユニークキーを確認する

親テーブル（users）の参照カラムが主キーまたはユニークキーであることを確認します。

SHOW CREATE TABLE users;

もし id カラムが PRIMARY KEY または UNIQUE でなければ、次のように修正します。

ALTER TABLE users ADD PRIMARY KEY (id);

4. 解決策2: データ型の不一致を修正する

外部キーのカラム（orders.user_id）と参照カラム（users.id）のデータ型が一致しているかを確認します。

SHOW COLUMNS FROM users;
SHOW COLUMNS FROM orders;

もし型が異なる場合、データ型を統一します。

ALTER TABLE orders MODIFY user_id INT NOT NULL;

5. 解決策3: ストレージエンジンをInnoDBに変更する

外部キー制約はInnoDBでのみ有効です。テーブルのストレージエンジンを確認し、InnoDBでない場合は変更します。

SHOW TABLE STATUS WHERE Name = 'users';
SHOW TABLE STATUS WHERE Name = 'orders';

ストレージエンジンが異なる場合、変更します。

ALTER TABLE users ENGINE=InnoDB;
ALTER TABLE orders ENGINE=InnoDB;

6. 解決策4: 外部キーのカラムにインデックスを追加する

外部キーを設定するカラム（orders.user_id）にはインデックスが必要です。以下のSQLで確認できます。

SHOW INDEX FROM orders;

インデックスがなければ、以下のように追加します。

ALTER TABLE orders ADD INDEX idx_user_id (user_id);

7. 解決策5: 参照先のテーブルが存在することを確認する

親テーブルが存在し、適切な名前が設定されていることを確認します。

SHOW TABLES;

誤ったテーブル名を指定している場合は、正しいテーブル名を使用するように修正します。

8. 解決策6: TEXTやBLOB型のカラムを避ける

外部キーとして TEXT, BLOB, JSON 型のカラムは使用できません。

CREATE TABLE example (
    id TEXT PRIMARY KEY
);

このような設計を避け、適切なデータ型（INT など）を使用します。

9. 解決策7: すべてのテーブルを作成後に外部キーを追加する

テーブル作成時に外部キー制約を適用するのではなく、後から追加すると解決できることがあります。

ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

10. 解決策8: 外部キー制約を無効にして修正後に再適用する

外部キー制約を無効にしてエラーの原因を特定し、修正後に再適用します。

SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE orders DROP FOREIGN KEY fk_user;
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);
SET FOREIGN_KEY_CHECKS = 1;

この方法で、データの整合性を保ちつつ外部キーを適用できます。