LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@yuta-katayama-23(yuta katayama)

MySQLを例にDBの基本を理解する　親子関係のあるテーブルを作成する（外部キー制約を付ける）

Posted at

はじめに

RDBに関して今までなんとなくPKを作成したり、あーIndexを張っているんですねとか言っていたが、ちゃんと理解しているか？と言われると怪しい感じだった。そこで今回はRDB（具体的にはMySQL5.7）を例に理解を深めてみたのでその備忘録を残す。

※外部キー制約についてはFOREIGN KEY制約(外部キー制約)の章。

DBの基礎　～制約～

RDBにおけるテーブルは以下のような表形式のデータであるが、なんでもかんでも表に入れられると困る…1という事で表のカラムには制約をかけられる。その制約で頻出するものについてみていきたいと思う。

id email name
1 tarou.yamada@example.com 山田 太郎
2 hanako.tanaka@example.com 田中 花子
3 jirou.satou@example.com 佐藤 次郎

PRIMARY KEY制約

重複した値を格納できないようにするための制約。ただし、NULLは許容されず、また複数PRIMARY KEY制約を付与する事はできない（複数付与できる重複防止のための制約はUNIQUE制約になる）。実際のテーブル作成時のSQLで指定する場合には、以下のようになる（idカラムにPRIMARY KEY制約を設けている）。

PRIMARY KEY制約を付けるカラムにはIndexを張る必要がある（もし張っていない場合、自動でIndexが張られる）。Indexとは？についてはDBの基礎　～Index(インデックス)～を参照。ちなみに、PRIMARY KEY制約のためのIndex名は変更できない（UNIQUE制約のためのIndex名は変更可）。

CREATE TABLE `members` (
  `id` int(10) NOT NULL, -- ←PRIMARY KEY制約を付与するカラムはNULLは許容されないのでNOT NULL制約も付与（NOT NULL制約については次の「NOT NULL制約」の章を参照）
  PRIMARY KEY (`id`) -- ←PRIMARY KEY制約
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

※テーブル設計をする際にはGUIツール（MySQLならMySQLのGUIツールを使いたい　どんなのがある？に書いてあるものなど）を使う事も多いと思うのでGUIからの設定も見てみる。例えば、MySQL Workbenchだと以下のようにチェックを入れるだけでPRIMARY KEY制約を付与できる。

Columns Indexes
image.png image.png

※PRIMARY KEY制約がないテーブルはほとんどないが、一応制約なので別にこの制約が不要であれば指定しないという事もできる。

NOT NULL制約

カラムの値にNULLを許容するか？の制約。実際のテーブル作成時のSQLで指定する場合には、以下のようになる（idカラムにNOT NULL制約を設けている）。

CREATE TABLE `members` (
  `id` int(10) NOT NULL, -- ←NOT NULL制約
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

※テーブル設計をする際にはGUIツール（MySQLならMySQLのGUIツールを使いたい　どんなのがある？に書いてあるものなど）を使う事も多いと思うのでGUIからの設定も見てみる。例えば、MySQL Workbenchだと以下のようにチェックを入れるだけでNOT NULL制約を付与できる。
image.png

※ちなみに、NULLを許容するには本来であれば以下のようなSQLのようにNULLを明示的に指定する必要があるが、NULLはデフォルトで付与されるので省略可能なので、NOT NULLがないカラムはNULLが付いた状態として扱われる。

CREATE TABLE `members` (
  `id` int(10) NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CHECK制約

指定した条件に合う値のみ許可するための制約（※この機能はMySQLだと8.0.16以降からしか使えない（13.1.20.6 CHECK Constraintsを参照））。実際のテーブル作成時のSQLで指定する場合には、以下のようになる（emailカラムにCHECK制約として正規表現でメールアドレスの制約を設けている）。

CREATE TABLE `members` (
  `id` int(10) NULL,
  `email` VARCHAR(128) CHECK (email REGEXP '[\w\-\._]+@[\w\-\._]+\.[A-Za-z]+'), -- ←CHECK制約
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

UNIQUE制約

重複した値を格納できないようにするための制約。ただし、NULLについては重複を許す（UNIQUE制約を付けたカラムでも複数行にわたりNULLが格納されていてもOK）。実際のテーブル作成時のSQLで指定する場合には、以下のようになる（emailカラムにUNIQUE制約を設けている）。

UNIQUE制約を付けるカラムにはIndexを張る必要がある（もし張っていない場合、自動でIndexが張られる）。Indexとは？についてはDBの基礎　～Index(インデックス)～を参照。UNIQUE制約のためのIndex名は変更可。

CREATE TABLE `members` (
  `id` int(10) NOT NULL,
  `email` varchar(128) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `email_idx` (`email`) USING BTREE -- ←UNIQUE制約
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

※テーブル設計をする際にはGUIツール（MySQLならMySQLのGUIツールを使いたい　どんなのがある？に書いてあるものなど）を使う事も多いと思うのでGUIからの設定も見てみる。例えば、MySQL Workbenchだと以下のようにチェックを入れるだけでUNIQUE制約を付与できる。UNIQUE制約を付ける場合、Indexも張る必要があるがそれはIndexesから確認できる。

Columns Indexes
image.png image.png

※制約を付けるカラムは複数も可能だが、その場合複数のカラムの値の組み合わせの重複を許さないという制約になる。例えば、以下のようなグループメンバーテーブルに対し、group_nameとmember_nameの2カラムでUNIQUE制約を設けると、group_nameとmember_nameの組み合わせで重複NGになるので、group_name=B,member_name=田中 太郎はエラーになる（この制約を要件的に言えば、グループには同じ人が参加しているという事はない、という事）。

id group_name member_name ...
1 A 山田 太郎 ...
2 B 田中 太郎 ...
3 B 佐藤 花子 ... 
CREATE TABLE `group_members` (
  `id` int(10) NOT NULL,
  `group_id` int(10) NOT NULL,
  `member_id` int(10) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `group_member_idx` (`group_id`,`member_id`) USING BTREE -- ←複数のカラムでUNIQUE制約を付与
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

※実際にはgroup_name・member_idは、groupテーブルやmemberテーブルという親テーブルがあり、親テーブルの主キーを格納する（つまり外部キー）設計になるだろう。上記は分かりやすくするためにidではなくnameで書いてみた。

FOREIGN KEY制約(外部キー制約)

親テーブルと子テーブルの間で値の整合性を取る事ができるようにする制約（子テーブルの特定のカラムに格納できる値を親テーブル（参照先）カラムに存在する値のみに限定する制約）。実際のテーブル作成時のSQLで指定する場合には、以下のようになる（group_idカラムにUNIQUE制約を設けている）。

FOREIGN KEY制約を付けるカラムにはIndexを張る必要がある（もし張っていない場合、自動でIndexが張られる）。Indexとは？についてはDBの基礎　～Index(インデックス)～を参照。FOREIGN KEY制約のためのIndex名は変更可。

CREATE TABLE `group_members` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `group_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `group_id_idx` (`group_id`),
  CONSTRAINT `group_members_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION -- ←FOREIGN KEY制約
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

※上記のSQLで外部キー(制約)名group_members_ibfk_1や参照アクション（外部キーオプション）ON DELETE NO ACTION ON UPDATE NO ACTIONを省略する事もでき、その場合のSQLは以下。外部キー(制約)名を省略すると外部キー名が、参照アクションを省略するとRESTRICTが、それぞれ自動で設定される（RESTRICTについては以下のRESTRICTを参照）。

CREATE TABLE `group_members` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `group_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `group_id_idx` (`group_id`),
  FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`) -- ←FOREIGN KEY制約（外部キー名・参照アクションの省略パターン）
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

※テーブル設計をする際にはGUIツール（MySQLならMySQLのGUIツールを使いたい　どんなのがある？に書いてあるものなど）を使う事も多いと思うのでGUIからの設定も見てみる。例えば、MySQL Workbenchだと以下のようにForeign Keysを設定するとFOREIGN KEY制約を付与できる（Foreign Key Optionsで設定できる内容については以下のCASCADE等参照アクションに設定できるオプション一覧を参照）。

Foreign Keysの設定① Foreign Keysの設定②
image.png image.png

FOREIGN KEY制約を付ける際のルール

FOREIGN KEY制約についてはいくつかの制限事項があるがそれについてはConditions and Restrictionsを参照。代表的な制限事項を列挙すると以下。

  • 親・子の両テーブルに、FOREIGN KEY制約対象となるカラムに対するIndexが必要（Indexを張っていない場合自動で張られる）
  • 親・子の両テーブルは同じストレージエンジンを使用（MySQLの場合、FOREIGN KEY制約を使用できるストレージエンジンはInnoDB・NDBのみ）
  • 親・子の両テーブルにおいて、カラムのデータ型・条件（unsignedなど）が一致（文字列型の場合は長さが不一致でも可。ただし、非バイナリ型の場合は文字セットと照合順序は同じである必要がある）

FOREIGN KEY制約を付けた際の親テーブル・子テーブルの動きを設定する

FOREIGN KEY制約(外部キー制約)には、親・子の関係ができ、子テーブルが親テーブルを参照しているという状況になるので親テーブルに対する変更を行った時の挙動を設定する必要があり、その設定内容としては以下の5つ。以下でその5つそれぞれを設定した時の動きを見ていく。

  • CASCADE
  • SET NULL
  • RESTRICT
  • NO ACTION
  • SET DEFAULT

※以下を見ていくと分かるが、親テーブルの変更＝更新・削除の2つを指し、それぞれON UPDATE ...・on DELETE ...で設定する。必ず2つとも設定する必要はないが、省略した場合には以下のRESTRICTとして扱われるので注意が必要。

CASCADE

親テーブルに対して削除・更新を行うと、子テーブルで同じ値を持つカラムのデータも同様に削除・更新を行う設定。

具体的には以下のようなgroup_membersテーブルが子テーブルで、group_id・group_nameに外部キー制約を設定していると、親テーブルであるgroupテーブルのgroup_nameを更新するとgroup_membersテーブルのカラムも自動的に変更される（group_idでも同じ動きになる）。
ezgif.com-gif-maker.gif

削除の場合は以下。
ezgif.com-gif-maker (1).gif

-- 親テーブル
CREATE TABLE `groups` (
  `id` int(10) NOT NULL,
  `name` varchar(128) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `name_idx` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 子テーブル
CREATE TABLE `group_members` (
  `id` int(10) NOT NULL,
  `group_id` int(10) NOT NULL,
  `group_name` varchar(128) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `group_id_idx` (`group_id`),
  KEY `group_name_idx` (`group_name`),
  CONSTRAINT `group_members_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `group_members_ibfk_2` FOREIGN KEY (`group_name`) REFERENCES `groups` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
SET NULL

親テーブルに対して削除・更新を行うと、子テーブルで同じ値を持つカラムのデータをNULLに変更する設定。

具体的には以下のようなgroup_membersテーブルが子テーブルで、親テーブルであるgroupテーブルのgroup_nameを更新するとgroup_membersテーブルのカラムも自動的にNULLになる（group_idも同じ動きになる）。
ezgif.com-gif-maker (2).gif

削除の場合は以下。
ezgif.com-gif-maker (3).gif

-- 子テーブル
CREATE TABLE `group_members` (
  `id` int(10) NOT NULL,
  `group_id` int(10) DEFAULT NULL,
  `group_name` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `group_id_idx` (`group_id`),
  KEY `group_name_idx` (`group_name`),
  CONSTRAINT `group_members_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`) ON DELETE SET NULL ON UPDATE SET NULL,
  CONSTRAINT `group_members_ibfk_2` FOREIGN KEY (`group_name`) REFERENCES `groups` (`name`) ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

※（当たり前だが）この設定をするにはNOT NULL制約は付けられない

RESTRICT

親テーブルに対して削除・更新を行うとエラーにする設定。設定を省略した場合はこの設定をしたとみなされる（そのためdumpしたSQLにわざわざON DELETE RESTRICTみたいに書かれる事はないみたい）。

実際に動きを見てみると、以下のように確かにエラーになる事が確認できる。
ezgif.com-gif-maker (4).gif

-- 子テーブル
CREATE TABLE `group_members` (
  `id` int(10) NOT NULL,
  `group_id` int(10) NOT NULL,
  `group_name` varchar(128) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `group_id_idx` (`group_id`),
  KEY `group_name_idx` (`group_name`),
  CONSTRAINT `group_members_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`),
  CONSTRAINT `group_members_ibfk_2` FOREIGN KEY (`group_name`) REFERENCES `groups` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
NO ACTION

これはRESTRICTと全く同じ動きになる。SQLとしては以下のようになる。

-- 子テーブル
CREATE TABLE `group_members` (
  `id` int(10) NOT NULL,
  `group_id` int(10) NOT NULL,
  `group_name` varchar(128) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `group_id_idx` (`group_id`),
  KEY `group_name_idx` (`group_name`),
  CONSTRAINT `group_members_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `group_members_ibfk_2` FOREIGN KEY (`group_name`) REFERENCES `groups` (`name`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
SET DEFAULT

定義としては存在するが、この設定でテーブルを作成する事はできないので実質、存在しない設定といえるかも（以下、公式からの引用）。

but both InnoDB and NDB reject table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.（InnoDBとNDBの両方がON DELETE SET DEFAULTまたはON UPDATE SET DEFAULT句を含むテーブル定義を拒否します）

おまけ

外部キー制約を付ける事で、子テーブルでは親テーブルのカラムにない値は保存できなくなる。これによりデータベースの整合性の維持がしやすくなる。

特に開発環境等ではデータベースに直でデータを挿入する事が多いので、本来は挿入できてはいけないデータなのに挿入できてしまい不整合を起こしてしまう…なんていうことが起きがちだが、そういった人為ミスを防ぐ事ができるようになる。
image.png

DBの基礎　～Index(インデックス)～

本を読んでいていて巻末に索引があってそこから該当のページを開く…みたいなことをする事があるが、データベースも同じで何かを探す時の索引＝インデクスを作成する事で、検索を早くするという事ができる。インデクスはカラム単位で作成（データベースの世界では張ると言ったりする）できる。

実際にインデックスを張ると、以下のようなイメージでデータの並び順が整理されるので検索が速くなる（並び替えられたデータのテーブルそのものが新しく作成されるのではなくデータの並び順を保持される。また、検索が速くなるのは二分探索アルゴリズムなどで検索されるため）。
image.png

※インデックスはむやみやたらに張ると問題もあるが、それはMySQLのIndexをはるコツなどを参照。

複合インデックス

2つ以上のカラムで作成したインデックスの事。複合インデックスの場合、インデックスに指定している1番目のカラムは暗黙的に単体のインデックスとしても利用できる。

具体的には、以下の図のようにgroup_member_idxというインデックス（正確にはユニークインデックス）があるが、このインデックスの設定としては1番目にgruop_idを2番目にmember_idを指定しているので、group_idについては単独でインデックスを利用できる。
image.png

※このため、外部キー制約を設定する際に必須となるインデックスだが、以下のSQLのようにgroup_idのインデックスを明示的に作成しなくても、複合インデックスのgroup_member_idxの1番目がgroup_idなので、group_idにインデックスが張られている状態と同じになる。

CREATE TABLE `group_members` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `group_id` int(10) unsigned NOT NULL,
  `member_id` int(10) unsigned NOT NULL,
  `enabled` tinyint(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `group_member_idx` (`group_id`,`member_id`) USING BTREE,
  KEY `member_id` (`member_id`),
  CONSTRAINT `group_members_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `group_members_ibfk_2` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ER図の書き方

例えば以下のようなSQLで作成するテーブルがあった時に、どのようになるか？を見てみる。

CREATE TABLE `group_members` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `group_id` int(10) unsigned NOT NULL,
  `member_id` int(10) unsigned NOT NULL,
  `enabled` tinyint(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `group_member_idx` (`group_id`,`member_id`) USING BTREE,
  KEY `member_id` (`member_id`),
  CONSTRAINT `group_members_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `group_members_ibfk_2` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `groups` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `members` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(128) NOT NULL,
  `name` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `email_idx` (`email`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ER図としては以下（以下のER図はMySQL Workbenchでデータベースを作成した後にそこからER図を出力している）。

図
全体像 image.png
色が付くので見やすかったり image.png

※ER図作成ツールとしてはSI Object Browser ERなどがある。

SQLに関しての補足

この記事の中で出てきたSQL内で？になりそうな部分を以下でその意味についてみていく。

unsigned

負数が使えなくなる代わりにその分扱える正の値を増やす設定をする構文。

ex) TINYINT型では通常扱える範囲は-128～127だが、unsignedを定義する事で0～255になる

・参考：11.1.1 Numeric Data Type Syntax

AUTO_INCREMENT

自動的に連続した数値をカラムの値にする設定。

※AUTO_INCREMENTを設定する際には以下の制約がある。

  • テーブルに 1つのカラムにしか設定不可
  • AUTO_INCREMENTが設定されたカラムにはインデックスを設定する（張られている）必要がある
  • AUTO_INCREMENTが設定されたカラムにはDEFAULT制約は設定不可
  • 正の数値を格納した場合だけ正しく動作する

上記のような制約があるので、PRIMARY KEYにAUTO_INCREMENTを付与されているものを見る事が多い気がする。

BTREE

インデックスのデータ構造に二分探索木を採用するようにする設定。二分探索木についてはB-treeインデックス入門などを参照。

  1. 例えば、家計簿のためのテーブルがあるとして日付を指定せずに行を作成してしまった…。家計簿なのでその支出の日付がないという事はないので日付は必須にしたい…。 ↩

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?