MySQL Cannot add foreign key constraint で怒られた

外部キー設定作業中にハマったのでメモ.
本記事では外部キーやデバッグの過程についても紹介する.

動作環境

  • MySQL 5.7.15

前提条件

今回は次の2テーブル( users, kinds )を例に考える.

  • DB.users (子テーブル)
    • id
    • kind_id (外部キー)
  • DB.kinds (親テーブル)
    • id (参照されるキー)
    • kind

参考

結論

先に結論から.

今回のエラーは参照元, 参照先のキーの型が異なったために発生したエラー.
次のコマンドでそれぞれのキーを見比べれば何が違うか一目瞭然.

show create table テーブル名 \G

型を揃えたらエラーが解消された.

外部キーとは

次のような動作を実現するもの.

  • 一方のテーブルに加えた変更が自動的に他テーブルにも反映する
  • 外部キーを持つテーブル間に親子関係を定義する

今回の例なら次のように言い換えられる.

  • .users から見て
  • .kinds のレコードを特定するためのもの

外部キーを使ってできること

たとえば, テーブル設計時にこんな希望がある.

  • DB.users内のkind_idカラムは, DB.kinds内のidじゃないとダメ
  • DB.kinds内のid を削除したら DB.users内のkind_id も削除される

外部キー制約 を利用するとこれらが実現できる.

現在のテーブル構造を確認する

一先ず現状のテーブル構造を確認する.
次のコマンドで

  • テーブル名
  • フィールド名

が確認可能.

実行結果.
(外部キー設定前)

親テーブル, 子テーブル

ここで少し用語について.

外部キーの

  • 参照元(親テーブル)
  • 参照先(子テーブル)

によってテーブルの呼称が変わるらしい.

今回の例では

  • 親テーブル
    • kinds
  • 子テーブル
    • users

外部キーを使う条件

外部キーを使うためにはいくつか条件がある.

  • 親テーブル, 子テーブルそれぞれが InnoDB であること
  • 外部キーに Unique 制約 があること
  • 参照元, 参照先のキーの型が同じであること


*170206 追記
twitterで指摘を頂きました.
uniqueにする必要なし

MySQLのストレージエンジンは大きく次の2種類.

  • InnoDB
  • MyISAM

確認方法は次の通り.
次の例では .users のストレージエンジン等を確認できる.

外部キー制約の設定

外部キー制約

次の例のように, 複数のテーブルにまたがる値を関連付けたいことがある.

  • DB.users内のkind_idカラムは, DB.kinds内のidじゃないとダメ

そんなときに外部キー制約を利用すると便利.

外部キー制約 設定

制約名(constraint)を指定しないとエラーになる.

constraint を指定して外部キーを設定

↑このSQL文の意味は次の通り.

  • 親テーブル変更: 子テーブルも変更
  • 親テーブル削除: 子テーブルに NULL を格納

外部キー設定時のオプション

  • ON DELETE
  • ON UPDATE

には次の4つの値が指定可能.

意味
RESTRICT 親テーブルに変更を加えた時にエラーを返す.
子テーブルへの変更はok
NO ACTION RESTRICT と一緒
CASCADE 親テーブルの変更がそのまま子テーブルへも反映される.
親テーブルのデータを削除 -> 子テーブルのデータ(レコード)が削除される
親テーブルのデータ変更 -> 子テーブルのデータも変更される
SET NULL 親テーブルの更新・削除 -> 子テーブルへ NULL が設定される

Cannot add foreign key constraint で怒られた

エラー内容は次の場所で確認可能.

  • SHOW ENGINE INNODB STATUS;
    • LATEST FOREIGN KEY ERROR

エラー内容は次の通り.

.users, kinds の該当フィールドを見比べると型が違う事に気付いた.

テーブル名 該当箇所
.users kind_id int(11) DEFAULT NULL
.kinds id int(10) unsigned NOT NULL AUTO_INCREMENT

子テーブルである .users の型に

  • int(10)
  • unsigned

を追加する.

外部キーもユニークである必要があるから, DB.users.kind_idUnique に設定.


*170206 追記
twitterで指摘を頂きました.
uniqueにする必要なし

変更ができたか確認.

再チャレンジ

できた.
実行結果.

まとめ

親テーブルと子テーブルで外部キー制約を設定する時は

を合わせよう.

今回は以上.

スポンサーリンク
336 x 280 – レクタングル(大)
336 x 280 – レクタングル(大)
  • このエントリーをはてなブックマークに追加

この記事が気に入ったら
いいね!しよう

スポンサーリンク
336 x 280 – レクタングル(大)

コメント

  1. ときさば より:

    同じ問題で行き当たったんですが、試してないですが、テーブルを先にドロップしておけばおきないかも…。

    • たきもと より:

      コメントありがとうございます!

      DROPする順番に気を付ければイケると思います。
      カジュアルにdropできる環境(開発環境とか)ならそれでokですが,
      本番環境だと気軽にdropできないと思うので
      transactionまで気を配ってのキー貼り替えがベターかなと思います。

  2. ヤン より:

    私は韓国の学生です。
    このForeign KEYの問題で頭が痛かったんですが、
    日本語で検索してこのサイトを見つけました。

    それで、問題解決しました!
    日本語学んで良かった!と思いました。

    • たきもと より:

      コメントありがとうございます。

      日本語、お上手ですね。
      また何かありましたらお声がけ下さい。

      —-
      Thank you for your comment.

      It’s important to be careful not only the type of columns
      but also the orders.
      For example, migration, seeding the datum.
      If child table has foreign keys, similar problems will be occurred.

      I mean, the order of dropping, migrating and seeding tables is also important.

      You can ask me anytime you get troubles.

      Regards.

トップへ戻る