Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

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関係の本は以下の2冊。両方とも非常に読み易かった。

スッキリわかるSQL入門 ドリル215問付き!
基礎からのMySQL 第3版

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

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

CHECK

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

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

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

まとめ

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

今回はここまで。

yukibe
2018年8月よりWebエンジニア。主にPHP。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away