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

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

※外部キー制約については[FOREIGN KEY制約(外部キー制約)](#foreign-key制約外部キー制約)の章。

## DBの基礎　～制約～
RDBにおけるテーブルは以下のような表形式のデータであるが、なんでもかんでも表に入れられると困る…[^勝手に入れる]という事で表のカラムには制約をかけられる。その制約で頻出するものについてみていきたいと思う。
[^勝手に入れる]: 例えば、家計簿のためのテーブルがあるとして日付を指定せずに行を作成してしまった…。家計簿なのでその支出の日付がないという事はないので日付は必須にしたい…。

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

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

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

```sql
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ツールを使いたい　どんなのがある？](https://qiita.com/yuta-katayama-23/items/d95f753f9f8345f29933)に書いてあるものなど）を使う事も多いと思うのでGUIからの設定も見てみる。例えば、MySQL Workbenchだと以下のようにチェックを入れるだけでPRIMARY KEY制約を付与できる。

| Columns | Indexes |
|:-:|:-:|
| ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1372684/922f4ea1-81fd-0391-c525-a5a4d1f91d35.png) | ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1372684/5ccec139-58d1-6e9b-b418-efd49dc9a9b9.png) |

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

- [PRIMARY KEY制約(主キー/プライマリキーを設定する)](https://www.dbonline.jp/mysql/table/index8.html)

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

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

※テーブル設計をする際にはGUIツール（MySQLなら[MySQLのGUIツールを使いたい　どんなのがある？](https://qiita.com/yuta-katayama-23/items/d95f753f9f8345f29933)に書いてあるものなど）を使う事も多いと思うのでGUIからの設定も見てみる。例えば、[MySQL Workbench](https://dev.mysql.com/downloads/installer/)だと以下のようにチェックを入れるだけでNOT NULL制約を付与できる。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1372684/3e06101e-f7fd-3bfd-0b6f-747532fcac1e.png)

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

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

- 参考：[NOT NULL制約(カラムにNULLの格納を許可するかどうか)](https://www.dbonline.jp/mysql/table/index5.html)

### CHECK制約
指定した条件に合う値のみ許可するための制約（※この機能はMySQLだと8.0.16以降からしか使えない（[13.1.20.6 CHECK Constraints](https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html)を参照））。実際のテーブル作成時のSQLで指定する場合には、以下のようになる（`email`カラムにCHECK制約として正規表現でメールアドレスの制約を設けている）。

```sql
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;
```

- 参考：[MySQL8.0.16で追加されたCHECK制約を試してみる。](https://www.ritolab.com/entry/197)

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

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

```sql
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ツールを使いたい　どんなのがある？](https://qiita.com/yuta-katayama-23/items/d95f753f9f8345f29933)に書いてあるものなど）を使う事も多いと思うのでGUIからの設定も見てみる。例えば、MySQL Workbenchだと以下のようにチェックを入れるだけでUNIQUE制約を付与できる。UNIQUE制約を付ける場合、Indexも張る必要があるがそれは`Indexes`から確認できる。

| Columns | Indexes |
|:-:|:-:|
| ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1372684/5575fce4-b3c0-6da7-03cf-4ea7e887d631.png) | ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1372684/c3e228cc-6489-e02a-c39b-0cb11bb250fa.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 | 佐藤 花子 | ... |

```sql
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で書いてみた。

- [UNIQUE制約(ユニーク制約を設定する)](https://www.dbonline.jp/mysql/table/index9.html)

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

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

```sql
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](#restrict)を参照）。

```sql
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ツールを使いたい　どんなのがある？](https://qiita.com/yuta-katayama-23/items/d95f753f9f8345f29933)に書いてあるものなど）を使う事も多いと思うのでGUIからの設定も見てみる。例えば、MySQL Workbenchだと以下のように`Foreign Keys`を設定するとFOREIGN KEY制約を付与できる（`Foreign Key Options`で設定できる内容については以下の[CASCADE](#cascade)等参照アクションに設定できるオプション一覧を参照）。

| Foreign Keysの設定① | Foreign Keysの設定② |
|:-:|:-:|
| ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1372684/4a3a6c0b-e7c3-785e-dccb-5114d0f246e2.png) | ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1372684/2ad89d3a-947a-7f93-fb51-ddecd9e183d5.png) |

- [FOREIGN KEY制約(外部キー制約を設定する)](https://www.dbonline.jp/mysql/table/index11.html)
- [13.1.18.5 FOREIGN KEY Constraints](https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html)

#### FOREIGN KEY制約を付ける際のルール
FOREIGN KEY制約についてはいくつかの制限事項があるがそれについては[Conditions and Restrictions](https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html#foreign-key-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](#restrict)として扱われるので注意が必要。

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

具体的には以下のような`group_members`テーブルが子テーブルで、`group_id`・`group_name`に外部キー制約を設定していると、親テーブルである`group`テーブルの`group_name`を更新すると`group_members`テーブルのカラムも自動的に変更される（`group_id`でも同じ動きになる）。
![ezgif.com-gif-maker.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1372684/6fd64fa9-48f4-d8c5-67ff-2165f71c01f8.gif)

削除の場合は以下。
![ezgif.com-gif-maker (1).gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1372684/9111164d-00c0-4eb8-32ad-85154bd5a8f6.gif)

```sql
-- 親テーブル
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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1372684/088cd017-3dab-3269-5eec-52e4b33208ba.gif)

削除の場合は以下。
![ezgif.com-gif-maker (3).gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1372684/953be05f-b481-df00-fcae-ec550081cff2.gif)

```sql
-- 子テーブル
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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1372684/b4ad6c07-67bf-487c-9712-351b81b844fa.gif)

```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`),
  CONSTRAINT `group_members_ibfk_2` FOREIGN KEY (`group_name`) REFERENCES `groups` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

##### NO ACTION
これは[RESTRICT](#restrict)と全く同じ動きになる。SQLとしては以下のようになる。

```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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1372684/17d784b2-edee-7b18-86bc-103cef6654db.png)

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

実際にインデックスを張ると、以下のようなイメージでデータの並び順が整理されるので検索が速くなる（並び替えられたデータのテーブルそのものが新しく作成されるのではなくデータの並び順を保持される。また、検索が速くなるのは二分探索アルゴリズムなどで検索されるため）。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1372684/fdd50a8e-35db-9456-5abd-c756cd5938e2.png)

※インデックスはむやみやたらに張ると問題もあるが、それは[MySQLのIndexをはるコツ](https://qiita.com/katsukii/items/3409e3c3c96580d37c2b)などを参照。

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

具体的には、以下の図のように`group_member_idx`というインデックス（正確にはユニークインデックス）があるが、このインデックスの設定としては1番目に`gruop_id`を2番目に`member_id`を指定しているので、`group_id`については単独でインデックスを利用できる。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1372684/1e86f006-1948-06cf-874e-c3ac89282f53.png)

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

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

- 参考：[DBのインデックスと複合インデックス](https://qiita.com/towtow/items/4089dad004b7c25985e3)

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

```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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1372684/3e9f7e62-5e1a-e77e-9bcb-cb2e9ec11143.png) 
| 色が付くので見やすかったり | ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1372684/291d943b-5a6c-82d6-dee0-fb5415a12e48.png) |

※ER図作成ツールとしては[SI Object Browser ER](https://products.sint.co.jp/ober)などがある。

- 参考：[若手プログラマー必読！５分で理解できるER図の書き方５ステップ](https://it-koala.com/entity-relationship-diagram-1897)
- 参考：[【MySQL Workbench】既存DBからER図を作成する](https://it-blue-collar-dairy.com/generate_er_diagram_from_db_on_mysqlworkbench/)

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

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

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

・参考：[11.1.1 Numeric Data Type Syntax](https://dev.mysql.com/doc/refman/5.7/en/numeric-type-syntax.html)

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

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

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

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

- 参考：[3.6.9 Using AUTO_INCREMENT](https://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html)
- 参考：[AUTO_INCREMENTを設定する(連続した数値を自動でカラムに格納する)](https://www.dbonline.jp/mysql/table/index7.html)

### BTREE
インデックスのデータ構造に二分探索木を採用するようにする設定。二分探索木については[B-treeインデックス入門](https://qiita.com/kiyodori/items/f66a545a47dc59dd8839)などを参照。