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 – レクタングル(大)
トップへ戻る