MySQL 外部キー制約の基本

MySQLで外部キー周りでハマったので基礎からおさらいする.
本記事では そもそも外部キーってなに? からスタート.
最終的には実際にDBをいじりながら, 次の外部キーオプションを確認してゆく.

  • RESTRICT
  • NO ACTION
  • CASCADE
  • SET NULL

概要は次の通り.

参考

動作環境

  • windows10 HOME x64
  • MySQL 5.7.15
  • centos6.8

前提条件

次の条件下で作業を行う.

  • VirtualBox, Vagrantで作成したローカル開発環境
  • ホストマシンがWin, ゲストマシンがcentos
  • tstDB というDBを予め作成しておく

作業方針

次の2テーブルを作成して, 外部キーオプションの違いを確認する方針.

  • DB.users
    • id
    • name
    • created_at
    • modified
  • DB.kinds
    • id
    • user_id #外部キー
    • created_at
    • modified

外部キーとは

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

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

外部キーはテーブルに親子関係を持たせるもの、って認識でok.

ここではもう少し外部キーについて解説.
概要は次の通り.

  • 外部キーを使ってできること
  • 親テーブル, 子テーブル

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

次のような例で考える.
概要は次の通り.

  • テーブルの親子関係を把握
  • テーブル設計時にやりたいことを把握

テーブルの親子関係を把握

次のような2つのテーブルを考える.

  • users (親テーブル)
    • id
    • name
    • password
  • kinds (子テーブル)
    • id
    • user_id # <- ココは DB.usersid と常に同じにしたい
    • name

usersが親テーブル.
kinds が子テーブル.

user_id が外部キー.

テーブル設計時にやりたいことを把握

箇条書きで.

  • DB.kinds内のuser_idカラムは, DB.users内のidじゃないとダメ
  • DB.usersid が変更されたら, kindsuser_id も自動で変更してほしい
  • DB.users テーブル自体が削除されたら, kindsuser_id には NULL を設定したい

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

親テーブル, 子テーブル

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

外部キーの

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

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

今回の例では

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

外部キーを使う条件

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

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

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

  • InnoDB
  • MyISAM

この点は作業に影響がないから深く突っ込まない.

外部キー制約とは

そもそも外部キー制約とは? について.
概要は次の通り.

  • そもそも外部キー制約ってなに
  • 外部キー制約 設定方法

そもそも外部キー制約ってなに

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

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

kindsテーブルについて条件を課している.
つまり, kindsテーブルの user_id カラムには制約があるってこと.

これが 外部キー制約.

外部キー制約 設定方法

次に, 外部キー制約設定方法について.

たとえば, ここでは外部キー制約によって次のようなことを実現する.

  • 親テーブルが変更されたら 子テーブルも変更される
  • 親テーブルが削除されたら 子テーブルの該当カラムに NULL を格納

基本的には次のようなコマンドで実現可能.

  • $ ALTER TABLE 子テーブル ADD CONSTRAINT 外部キーID FOREIGN KEY (外部キー) REFERENCES 親テーブル (id) ON UPDATE オプション ON DELETE オプション;

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

ちなみに, さっきの

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

を実現するコマンドはこれ.

  • $ ALTER TABLE kinds ADD CONSTRAINT users_fk_1 FOREIGN KEY (user_id) REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL;

実行結果

次のコマンドで外部キーの設定確認が可能.

  • $ show create table kinds \G

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

さっきの例でいうところの

  • ON UPDATE
    • 親テーブル(内の, 外部キーに関係するカラム)が更新された時の, 子テーブル 外部キーの挙動設定
  • ON DELETE
    • 親テーブル(内の, 外部キーに関係するカラム)が削除された時の, 子テーブル 外部キーの挙動設定

で指定するオプションについて.

オプションには次の4つの値が指定可能.

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

この表だけ見てても実感がわかないので, 手を動かして確認してみる.

NO ACTIONRESTRICT と一緒らしいので省略.
今回実験するのは次の3つにする.

  • RESTRICT
  • CASCAFD
  • SET NULL

早速実験.

RESTRICT

これは ON DELETE, ON UPDATE を指定しない場合のデフォルト設定.

RESTRICT: 親テーブルに対する削除または更新操作を拒否します。RESTRICT (または NO ACTION) を指定することは、ON DELETE または ON UPDATE 句を省略することと同じです。

引用: 外部キー制約の使用 | dev.mysql.com

実際にテーブルを作成しながら試してみる.

概要は次の通り.

  • テーブル作成
  • 外部キー設定
  • 親テーブル更新
  • 親テーブル削除

テーブル作成

一先ず次の2テーブルを作成する.

  • users
  • kinds

まずは users.
次のコマンドで作成.

次に kinds.
この時点ではテーブルを作るだけ.
外部キー設定はこの後の手順で行う.

次のコマンドで kinds を作成.

ちゃんとテーブルができているかどうかは次のコマンドで確認可能.

  • $ desc users
  • $ desc kids

たとえば, users はこんな感じ.

このままだとデータがない空のテーブルなので, いくつかデータを挿入する.

まずは users にダミーデータを挿入.

確認.

次に, kinds にデータ挿入.

確認

外部キー設定

次に外部キーの設定.
今回は kindsテーブルの user_id が外部キー.

次のコマンドで外部キーを RESTRICT として設定する.

  • $ ALTER TABLE kinds ADD CONSTRAINT users_fk_1 FOREIGN KEY (user_id) REFERENCES users (id) ON UPDATE RESTRICT ON DELETE RESTRICT;

ちゃんと設定されているか確認.

親テーブル更新

親テーブルである usersid を更新してみる.

次コマンドで id=1id=10 に変更してみる.

  • $ UPDATE users SET id=id*10 WHERE id=1;

ちゃんと RESTRICT の設定が機能している.

ちなみに, users の他カラムは自由に変更可能.
たとえば, id=1takamototmp に変更してみる.

  • $ update users set name="tmp" where id=1;

kindsテーブルで指定された外部キーのみが制約を受けていることが分かる.

親テーブル削除

次に, 親テーブルを削除してみる.

  • drop table users;

RESTRICT で外部キーを設定したテーブルを削除しようとした場合, エラーを返す.

外部キー制約を RESTRICT にした場合の挙動については以上.

続いて, CASCADE を指定した場合の挙動について見てゆく.

CASCADE

テーブル作成方法は省略.
さっきと同じなので.

ここでは次の点を紹介.

  • 外部キー設定
  • 親テーブル更新
  • 親テーブル削除

外部キー設定

次の手順で外部キーを設定しなおす.

  • 先程設定した外部キーを削除
  • 外部キーを再設定

先程設定した外部キーを削除

まず, 次のコマンドで指定した外部キーを削除する.

まず, 次のコマンドで設定された外部キーの状態を確認.

  • $ show create table kinds \G

続いて, 次のコマンドで外部キーの削除

  • $ alter table 外部キーを含むテーブル drop foreign key 外部キーID;

ここでは kinds テーブルに設定した外部キーを削除する.

  • $ alter table kinds drop foreign key users_fk_1;

再度テーブルの状態を確認.

削除前と比べると, 次の一文がなくなっているのが分かる.

CONSTRAINTusers_fk_1FOREIGN KEY (user_id) REFERENCESusers(id)

ここまでで外部キーの削除はok.

外部キーを再設定

続いて外部キーの再設定.
ここでは CASCADE を設定する.

  • $ ALTER TABLE kinds ADD CONSTRAINT users_fk_1 FOREIGN KEY (user_id) REFERENCES users (id) ON UPDATE CASCADE ON DELETE CASCADE;

実行結果

次の一文があるので, 期待通りに外部キーの設定が完了していることが分かる.

  • CONSTRAINTusers_fk_1FOREIGN KEY (user_id) REFERENCESusers(id) ON DELETE CASCADE ON

親テーブル更新

親テーブルの変更で期待する子テーブルの動作は次の通り.

意味
CASCADE 親テーブルの変更がそのまま子テーブルへも反映される.
親テーブルのデータを削除 -> 子テーブルのデータ(レコード)が削除される
親テーブルのデータ変更 -> 子テーブルのデータも変更される

まずは親テーブルを更新.

現状の users, kinds は次の通り.

  • users

  • kinds

次コマンドで usersid=1id=10 に変更してみる.

  • $ UPDATE users SET id=id*10 WHERE id=1;

データの確認.

  • users

  • kinds

期待通りに変わっている.

親テーブルの特定レコード削除

次のコマンドで usersid=3 を削除してみる.

  • $ delete from users where id=3;

それぞれのテーブルを確認する.

  • users

  • kinds

親テーブルから特定のレコードを削除すると, 子テーブルからも同様に削除されていることが確認できた.

親テーブル削除

今度は親テーブルを削除しようとしたらどうなるかを確認.

次のコマンドで users を削除してみる.

  • $ drop table users;

実行結果

怒られた.
外部キーを設定している親テーブルは勝手に削除しちゃだめなんだね.

子テーブル削除

外部キーが貼られている状態で子テーブルは削除できるのか?
実験.

次のコマンドで kinds を削除してみる.

  • $ drop table kinds;

あ、できた.

外部キーが設定されている 子テーブル (kinds) は削除可能 なんだね.

SET NULL

最後は SET NULL を設定した場合について.

期待する動作は次の通り.

意味
SET NULL 親テーブルの更新・削除 -> 子テーブルへ NULL が設定される

概要は次の通り.

  • テーブル作成
  • 外部キー設定
  • 親テーブル更新
  • 親テーブル削除

テーブル作成

改めてテーブルを作成し直す.
現時点で存在するテーブルは次の通り.

  • $ show tables;

users テーブルのみが存在している.
これを削除する.

  • $ drop table users;

改て users, kinds を作成し直す.
手順はこれまでと同様.

  • users

  • kinds

作成されたテーブルを確認.

  • $ show tables;

さらに, ダミーデータを追加.

  • users

  • kinds

ここまでテーブル生成は完了.

外部キー設定

今回は外部キーオプションとして SET NULL を指定.
ただ, 親テーブルの更新時・削除時で異なる挙動にしたい.

実現したい挙動は次の通り.

  • 親テーブル更新時
    • 子テーブルには親テーブルと同じ変更を適用
  • 親テーブル 特定レコードを削除時
    • 子テーブルには NULL を格納

これを実現するために次のコードを実行する.

  • $ ALTER TABLE kinds ADD CONSTRAINT users_fk_1 FOREIGN KEY (user_id) REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL;

設定確認

親テーブル更新

親テーブルである users の id を更新してみる.

次のコマンドで id=1 を id=10 に変更.

  • $ UPDATE users SET id=id*10 WHERE id=1;

確認.

  • users

  • kinds

親テーブルの挙動に合わせて, 子テーブルも変化している.
これは期待通りの動作.

親テーブルの特定レコード削除

次のコマンドで users の id=3 を削除してみる.

  • $ delete from users where id=3;

データを確認してみる.

  • users

  • kinds

親テーブルの特定レコードが削除されると, 子テーブルには NULL が格納された.
期待通り.

親テーブル削除

じゃあ親テーブルを削除したらどうなるのか?
実験.

  • $ drop table users;

怒られた.

親子関係のある 親テーブル は外部キー制約のある状態では削除できない.

じゃあ, 外部キーを外せば削除できるのか?
実験.

まず, 次のコマンドで外部キーを外す.

  • $ alter table kinds drop foreign key users_fk_1;

次に親テーブルである users を削除してみる.

  • $ drop table users;

お、できた.

まとめ

外部キーの基本的な操作を紹介した.

個人的な発見は次の通り.

  • 外部キーが貼られている親テーブルは, 外部キーを外さない限り削除できない

今回は以上.

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

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

トップへ戻る