【SQLに慣れる】MySQLでよく使用する制約

MySQLについて、よく使用される制約をまとめてみた。

# UNIQUE

カラムの値を重複させない（一意性）制約。

tb1テーブル作成後にカラム構造を見ると、Key欄に``UNI``と表記されている。

```sql
mysql> CREATE TABLE tb1(
    -> id INT UNIQUE,
    -> name VARCHAR(10));
Query OK, 0 rows affected (0.02 sec)

mysql> DESC tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | UNI | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
```

しかし、値がNULLでも挿入できてしまう。

```sql
mysql> INSERT INTO tb1 VALUES();
Query OK, 1 row affected (0.00 sec)
```

NULLの値が複数あっても重複とみなされないので、以下の状態にすることもできる。

```sql
mysql> SELECT * FROM tb1;
+------+------+
| id   | name |
+------+------+
| NULL | NULL |
| NULL | NULL |
|    1 | taro |
+------+------+
3 rows in set (0.00 sec)
```

値がNULLでなければ、重複した値を挿入しようとするとエラーが発生する。

```sql
mysql> INSERT INTO tb1 VALUES(1, 'hanako');
ERROR 1062 (23000): Duplicate entry '1' for key 'id'
```

# NOT NULL

その名の通り、カラムの値をNULLにさせない制約。

先ほど作成したtb1テーブルのカラム構造を変更し・・・と思ったが、エラーが出てしまった。

```sql
mysql> ALTER TABLE tb1 MODIFY id INT NOT NULL;
ERROR 1138 (22004): Invalid use of NULL value
```

テーブルに``id=NULL``のレコードが存在する状態では、NOT NULL制約を付与することができないようだ。

そのためid=NULLのレコードを削除し、その後でALTER TABLE文を実行した。

```sql
mysql> DELETE FROM tb1 WHERE id IS NULL;
Query OK, 2 rows affected (0.00 sec)
```

```sql
mysql> ALTER TABLE tb1 MODIFY id INT NOT NULL;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
```

カラム構造を確認すると、Null欄の値が``NO``になっている。

```sql
mysql> DESC tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
```

同様の質疑応答がstackoverflowにあったので紹介する。（全て英語です。）こちらはNOT NULL制約を付与したいカラムについて、UPDATE文でNULLから別の値に変更している。

> [MySQL Alter table causes Error: Invalid use of NULL value](https://stackoverflow.com/questions/22971586/mysql-alter-table-causes-error-invalid-use-of-null-value)

# PRIMARY KEY

PRIMARY KEY制約は``主キー制約``とも呼ばれ、``レコードの識別に利用されるカラムに指定する制約``だ。``UNIQUE制約とNOT NULL制約を合わせた制約``とも言える。

指定すると、該当カラムのNull欄が``NO``になり、Key欄が``PRI``になる。

```sql
mysql> ALTER TABLE tb1 MODIFY id INT PRIMARY KEY;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
```

UNIQUE制約のように値の重複を許さず、

```sql
mysql> INSERT INTO tb1 (id) VALUES(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
```

NOT NULL制約のようにNULLを弾く。

```sql
mysql> INSERT INTO tb1 VALUES();
ERROR 1364 (HY000): Field 'id' doesn't have a default value
```

# DEFAULT

カラムの値を指定しなかった場合、デフォルトの値を指定できる制約。

指定後は、Default欄が``デフォルト値``として指定した``no_name``になっている。

```sql
mysql> ALTER TABLE tb1 MODIFY name VARCHAR(10) DEFAULT 'no_name';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(10) | YES  |     | no_name |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
```

値を指定しなければ、デフォルト値であるno_nameが挿入される。

```sql
mysql> INSERT INTO tb1 (id) VALUES(2);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tb1;
+----+---------+
| id | name    |
+----+---------+
|  1 | taro    |
|  2 | no_name |
+----+---------+
2 rows in set (0.00 sec)
```

# AUTO_INCREMENT

番号を手入力するのは手間がかかる。さらに、手入力では番号が飛んでしまったり、重複したりする可能性がある。

そこで``AUTO_INCREMENT``を指定すれば、自動で番号が入力される。PRIMARY KEY制約とセットで使用されることが多い機能だ。

ちなみにAUTO_INCREMENTは制約ではなく、``属性``になる。

この属性は連番が自動で付与されるため、該当カラムのデータ型は``整数型（INTなど）``でなければ、指定することができない。

指定すると、Extra欄に``auto_increment``と表記される。

```sql
mysql> ALTER TABLE tb1 MODIFY id INT AUTO_INCREMENT;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> DESC tb1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | no_name |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
```

指定したidカラムに``0を指定する、もしくは値を入力しない場合``に自動で番号が割り振られる。

```sql
mysql> INSERT INTO tb1 (id, name) VALUES(0, 'hanako');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tb1;
+----+---------+
| id | name    |
+----+---------+
|  1 | taro    |
|  2 | no_name |
|  3 | hanako  |
+----+---------+
3 rows in set (0.00 sec)
```

手動で番号を指定することも可能だ。番号10を手動で入力した場合、次のレコードは自動的に番号11となる。

```sql
mysql> INSERT INTO tb1 (id, name) VALUES(10, 'jiro');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tb1 (id, name) VALUES(0, 'saburo');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tb1;
+----+---------+
| id | name    |
+----+---------+
|  1 | taro    |
|  2 | no_name |
|  3 | hanako  |
| 10 | jiro    |
| 11 | saburo  |
+----+---------+
5 rows in set (0.00 sec)
```

さらに深く追求した記事を見つけた。連番の割り振り方について様々な実験（？）をされている。

>[MySQLのAUTO_INCREMENTについて色々と調べてみた。](https://qiita.com/sakuraya/items/0dd0bb4114e56f42556d)


## 連番のリセット

前回の記事で紹介しているので、参考に。

> [【SQLに慣れる】データの挿入・表示・更新・削除](https://qiita.com/33yuki/items/49ee89e0234f1ab223f9#連番のリセット)

# FOREIGN KEY

FOREIGN KEY制約は``外部キー制約``とも呼ばれている。``参照整合性``を保つための仕組みだ。

例えば、``社員テーブル``と``売上テーブル``があったとする。売上テーブルには、どの社員がいくら売り上げたかを記録している。しかしこの場合、売上テーブルにおける``どの社員が``の情報が社員テーブルに存在しなければ、記録が矛盾していることになる。FOREIGN KEY制約は、そのような状態を防ぐ機能だ。

ここでテーブルtb2を作成した。FOREIGN KEY制約は``member_id``に設定し、tb1のレコードに存在する``id``の値のみ受け付けるようにした。

公式サイトで紹介されている書き方は、これより複雑だった。今回は一番簡単な書き方でテーブルを作成した。

設定すると、Key欄が``MUL``となる。

>[MySQL 5.6 リファレンスマニュアル〜外部キー制約の使用](https://dev.mysql.com/doc/refman/5.6/ja/create-table-foreign-keys.html)

```sql
mysql> CREATE TABLE tb2(
    -> id INT AUTO_INCREMENT PRIMARY KEY,
    -> member_id INT NOT NULL,
    -> sales INT NOT NULL,
    -> FOREIGN KEY(member_id)
    -> REFERENCES tb1(id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> DESC tb2;
+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| member_id | int(11) | NO   | MUL | NULL    |                |
| sales     | int(11) | NO   |     | NULL    |                |
+-----------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
```

``member_id``に指定した値が``tb1に存在するid``であればレコードが挿入されるが、存在しなければエラーが発生する。

```sql
mysql> INSERT INTO tb2 VALUES(0, 1, 10000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tb2 VALUES(0, 5, 10000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`sample_db`.`tb2`, CONSTRAINT `tb2_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `tb1` (`id`))
```

人間側の操作は常に間違いが付きまとう。それを防ぐのに根性論は通じない。しかし、プログラム側の設定が正しければそれを防ぐことができる。人間側の配慮も必要ない。

制約は、データベースのバリデーションとも言い換えられると思う。

# その他

これまで読んだSQL関係の本は以下の２冊。両方とも非常に読み易かった。

>[スッキリわかるSQL入門 ドリル215問付き！](https://book.impress.co.jp/books/1111101167)
>[基礎からのMySQL 第3版](http://www.sbcr.jp/products/4797393118.html)

SQLの機能は各DBMSで共通で使用できるものが大半だが、一部機能についてはそうではないらしい。SQLの基本書で紹介されていても、MySQLでは使用できない機能もあった。

以下の``CHECK制約``がそうだった。

## CHECK

CHECK制約は、挿入するレコードの値に条件を指定し、それに合致する値のみ挿入させる制約だ。

MySQLでは提供されていない制約だが、よく似た機能を作成する手段があるようだ。

>[MySQL 5.7で生成カラムを使ってCHECK制約もどきを実装する](https://qiita.com/hmatsu47/items/ddf0fc74f2dc3bb7556f)

# まとめ

制約は後で追加すると、これまでのレコードの値と矛盾した場合にエラーが発生してしまう。データベース設計時に土台を整えておかないと後で苦労しそうだ・・・。

今回はここまで。