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)
);