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

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

UNIQUE

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

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

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でも挿入できてしまう。

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

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

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

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

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

NOT NULL

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

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

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文を実行した。

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

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になっている。

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

PRIMARY KEY

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

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

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制約のように値の重複を許さず、

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

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

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

DEFAULT

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

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

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が挿入される。

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と表記される。

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を指定する、もしくは値を入力しない場合に自動で番号が割り振られる。

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となる。

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について色々と調べてみた。

連番のリセット

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

【SQLに慣れる】データの挿入・表示・更新・削除

FOREIGN KEY

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

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

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

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

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

MySQL 5.6 リファレンスマニュアル〜外部キー制約の使用

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であればレコードが挿入されるが、存在しなければエラーが発生する。

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問付き！
基礎からのMySQL 第3版

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

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

CHECK

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

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

MySQL 5.7で生成カラムを使ってCHECK制約もどきを実装する

まとめ

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

今回はここまで。