[SQL][初心者] FOREIGN KEY- 外部キー、CASCADEオプションについて

そもそも外部キーってなに？

テーブルのある列に、別のテーブルの特定の列に存在する値しか入力できないようにする制約です。

例えば、ユーザーと住所情報のテーブルがあったときに、住所情報のユーザーIDはユーザーテーブルに存在するIDしか指定できないようにするといった感じです。

外部キーのある2つのテーブル具体例

ユーザー情報テーブルuserとユーザー権限テーブルroleを例にするとこうなります。

ユーザーの権限IDは、権限テーブルに存在するIDしか指定できないように指定しています。

CREATE TABLE IF NOT EXISTS role (
  -- 権限のID
	id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, 
  -- 権限の名前。adminやゲストなどの値が入る
	role_name VARCHAR(20) NOT NULL
);

CREATE TABLE IF NOT EXISTS user (
  -- ユーザーのID。自動で付与される
	id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  -- ユーザー名
	name VARCHAR(20) NOT NULL,
  -- パスワード
	password VARCHAR(256) NOT NULL,
  -- データ作成日時
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  -- データ更新日時
	updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE current_timestamp,
	-- ユーザーが持っている権限のID
  role_id INT NOT NULL,
  -- ユーザーが持っている権限IDを、権限テーブルに紐づける
	FOREIGN KEY (role_id) REFERENCES role(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);

データはこんな感じです。

外部キー文法

文法は以下の通りです。外部キーにしたいカラム名と参照先のテーブル・カラムを指定します。

最後に外部キー制約です。ここはあとで説明します。

  <外部キーにしたいカラム名> <データ型> <属性・オプションなど...>,
  ...,
  FOREIGN KEY (外部キーにしたいカラム名>) REFERENCES <参照先テーブル名>(参照先テーブルの紐づけたいカラム)  <外部キー制約>

先ほどの例で説明してみましょう。

FOREIGN KEY (role_id)でrole_idというINT型のカラムを外部キーに指定しています。

REFERENCE role(id)で、role_idをroleテーブルのidカラムに紐づけるように設定しています。

	-- ユーザーが持っている権限のID
  role_id INT NOT NULL,
  -- ユーザーが持っている権限IDを、権限テーブルに紐づける
	FOREIGN KEY (role_id) REFERENCES role(id) ON DELETE RESTRICT ON UPDATE RESTRICT

外部キー制約とは？

先ほどの例の最後にON DELETE RESTRICTという記述がついていました。

これは外部キー制約といって、参照先のデータが更新・削除された場合どういう挙動になるのかを設定できます。

	-- ユーザーが持っている権限のID
  role_id INT NOT NULL,
  -- ユーザーが持っている権限IDを、権限テーブルに紐づける
	FOREIGN KEY (role_id) REFERENCES role(id) ON DELETE RESTRICT ON UPDATE RESTRICT

基本的な文法は後回しとして、先ほどのテーブルの例で外部キー制約を加えるとどういう挙動になるか見てみましょう。

以下のようなデータがあった場合に、roleからmemberの行を削除しようとするとエラーになります。

ON DELETE RESTRICTにしているため、memberの権限(role_idが2)のユーザーが存在しているので削除はできない設定になっています。

idの更新も同様にエラーが発生します。これもON UPDATE RETRICTにしているためです。

外部キー制約の文法

以下のようにFOREIGN KEYの最後に指定するようにします。

 FOREIGN KEY (外部キーにしたいカラム名>) REFERENCES <参照先テーブル名>(参照先テーブルの紐づけたいカラム)  <外部キー制約>

制約部分は以下のような文法です。

ON DELETEは参照先が削除された時の挙動, ON UPDATEは参照先のカラムが更新された場合の挙動を指定します。

片方でも両方でも可能で、両方なにも指定しないこともできます。

指定しない場合の挙動についてはこのあと説明します。

... ON DELETE <制約の種類> ON UPDATE <制約の種類>

注意点

参照しているカラム以外が更新された場合は何も起こらないです。

例えば先ほどのテーブル・データの例だと、roleテーブルのidカラムを参照しているため、「member」のroleテーブルのidを変更したらエラーが起きるがrole_nameである「member」を別の値に変更しても何も起こりません。

また、参照されていないデータの削除はどの制約にしていても問題なく削除できます。

今回の例だとroleテーブルのrole_nameがguestになっているレコードを削除してもエラーになりません。

外部キー制約の種類

基本的なものを抜粋しました。これらが分かれば十分かと思います。

ON UPDATEにした場合のCASCADEってどんな挙動？

「参照先の変更が外部キーにも適用される」とありますがよくわからない方いると思います。

先ほどの例のテーブル・データだと、roleテーブルのmemberレコードのidを2 -> 5にした場合、userテーブルのrole_idが2になっているレコードも5になるということです。

まとめ

基本的なテーブル作成とデータ型、外部キー周りが分かればテーブルの作成は一通り問題なくできると思います。

基本的なテーブル作成やデータ型は別記事でまとめています。

参考サイト

• MySQL公式
  • 外部キー制約
    • https://dev.mysql.com/doc/refman/5.6/ja/create-table-foreign-keys.html