MySQL
SQL
DB

MySQLの外部キー制約


TL;DR


  • 参照整合性とは、データを操作しても保証されるテーブルの構成のこと。具体的には以下の3つの事を指す。


    • FOREIGN KEY 制約

    • NOT NULL 制約

    • UNIQUE 制約



  • FOREIGN KEY 制約とはテーブル間にまたがったデータの一貫性を保証する制約。


環境

MySQL Version : 8.0.15-1debian9


FOREIGN KEY 制約

FOREIGN KEY 制約を使ってテーブル間のデータの一貫性を保証することができる。

-- 基本的な構文

FOREIGN KEY
[インデックス名] (カラム名)
REFERENCES テーブル名 (カラム名)
[ON DELETE reference_option]
[ON DELETE reference_option]

reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

ここで、インデックス名とは制約IDのこと。指定しなければ自動生成される。

以下のようにテーブルを作成する時に設定できる。

-- 図書館のデータを扱う例

create database `library`;
use `library`;

-- 利用者
create table USER (
id integer primary key,
name varchar(256) not null
);

-- 本
create table BOOK (
id integer primary key,
title varchar(256) not null
);

-- 貸し出し
create table LENDING_BOOK (
user_id integer,
book_id integer,
foreign key user_id_foreign_key (user_id) references USER (id),
foreign key book_id_foregin_key (book_id) references BOOK (id)
);

INSERT INTO USER VALUES (1, 'Jhon');
INSERT INTO BOOK VALUES (1, 'Essential Scrum');

-- ジョンがEssential Scrumを借りる
INSERT INTO LENDING_BOOK VALUES (1, 1);

-- ジョンが存在しない本を借りようとすると FOREIGN KEY 制約違反で貸し出しできない
INSERT INTO LENDING_BOOK VALUES (1, 2);

FOREIGN KEY 制約違反になるとエラーメッセージが出力される。上記の違反の場合には以下のメッセージが出力される。

14:26:45    INSERT INTO LENDING_BOOK VALUES (1, 2)  Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`library`.`LENDING_BOOK`, CONSTRAINT `book_id_foregin_key` FOREIGN KEY (`book_id`) REFERENCES `BOOK` (`id`))    0.0059 sec


オプション

FOREIGN KEY 制約には UPDATE または DELETE の際にどのような制約をつけるか選択できる。


  • RESTRICT ... 更新、削除を拒否する。何も指定しない場合はこの制約が付与される。

  • CASCADE ... 参照先データが削除、更新されると、参照元も削除、更新する。

  • SET NULL ... 参照先データが削除、更新されると、参照元はnullになる。この時、カラムには NOT NULL 制約を付与できない。

  • NO ACTION ... RESTRICTと同じ。

  • SET DEFAULT ... RESTRICTと同じ。

例えば、ジョンが図書館から退会した場合、借りていた本を全て返却するルールだとすると以下のような制約が考えられる。

create table LENDING_BOOK (

user_id integer not null,
book_id integer,
foreign key user_id_foreign_key (user_id) references USER (id)
ON DELETE CASCADE,
foreign key book_id_foregin_key (book_id) references BOOK (id)
);


参考