MySQLの制約と自動インクリメント

カテゴリ：データベース編

目次
テーブルを作成する際には、意図しない値の登録や変更を防止したり、整合性を保持ためのいくつかの制約があります。ここではよく使用される制約について解説いたします。

Noteなお、以降のではMySQLでのデータベースとテーブルの作成で作成したblogテーブルを基に解説いたします。もし作成していない場合は先に作成してください。

NOT NULL制約

NOT NULL制約はNull値を許可しない制約です。ID値など、空であってはいけないカラムに設定しておきます。

テーブル作成時にNOT NULLキーを設定するには、以下のように指定します。 

CREATE TABLE テーブル名 (
    カラム名 型名 NOT NULL
);

後からNOT NULLキーを設定するには、以下のALTER文を実行します。 

ALTER TABLE テーブル名 MODIFY COLUMN カラム名 型名 NOT NULL;

blogテーブルのtitleとtextカラムにNOT NULL制約を設定してみましょう。

設定前は、全カラムのNull欄の値がYESとなっています。これはNULLを許可する事を表しています。 

mysql> show columns from blog;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| title | varchar(100) | YES  |     | NULL    |       |
| text  | text         | YES  |     | NULL    |       |
| date  | datetime     | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

以下の文を実行します。 

mysql> ALTER TABLE blog MODIFY COLUMN title VARCHAR(100) NOT NULL;
Query OK, 0 rows affected (1.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE blog MODIFY COLUMN text TEXT NOT NULL;
Query OK, 0 rows affected (0.90 sec)
Records: 0  Duplicates: 0  Warnings: 0

すると以下のように、titleとtextカラムのNull欄の値がNOに変わっている事が確認できますね。 

mysql> show columns from blog;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| title | varchar(100) | NO   |     | NULL    |       |
| text  | text         | NO   |     | NULL    |       |
| date  | datetime     | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

試しに、titleの値をnullにして登録が拒否されることを確認してみましょう。「Column 'title' cannot be null」と表示され登録できないことが分かります。 

mysql> INSERT INTO blog (id, title, text, date) VALUES ('2', null, '件名を入力し忘れた！', NOW());
ERROR 1048 (23000): Column 'title' cannot be null

ちなみにNOT NULLはいわゆる空文字（''）は制限できません。これは空文字＝Nullではないからです。 

mysql> INSERT INTO blog (id, title, text, date) VALUES ('2', '', '件名を入力し忘れた！', NOW());
Query OK, 1 row affected (0.09 sec)

一意性制約（UNIQUE KEY）

一意性制約は値の重複を許可させない制約です。記事IDなど値が被ってはいけないカラムに設定します。

注意一意性制約にはNOT NULL制約は含まれないため注意が必要です。

テーブル作成時に一意性キーを設定するには、以下のように指定します。 

CREATE TABLE テーブル名 (
    カラム名 型名 UNIQUE KEY
);

後から一意性キーを設定するには、以下のALTER文を実行します。 

ALTER TABLE テーブル名 MODIFY COLUMN カラム名 型名 UNIQUE KEY;

blogテーブルのidカラムを一意性キーに設定してみましょう。 

mysql> ALTER TABLE blog MODIFY COLUMN id INT UNIQUE KEY;
Query OK, 0 rows affected (0.56 sec)
Records: 0  Duplicates: 0  Warnings: 0

以下のようにidカラムのKey欄の値がUNIが追加されている事が確認できますね。 

mysql> show columns from blog;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  | UNI | NULL    |       |
| title | varchar(100) | NO   |     | NULL    |       |
| text  | text         | NO   |     | NULL    |       |
| date  | datetime     | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

試しにidに重複する値を指定して登録が拒否されるかを確認してみましょう。「Duplicate entry '1' for key 'id'」と表示され挿入が拒否された事が分かります。 

mysql> INSERT INTO blog (id, title, text, date) VALUES ('1', "IDが重複", 'これは挿入できない！', NOW());
ERROR 1062 (23000): Duplicate entry '1' for key 'id'

主キー制約（PRIMARY KEY）

主キー制約は一意性制約に加え、NOT NULL制約も持ち、テーブルに1つのみ設定可能な制約です。一般的にIDのカラムに設定され、テーブル内の1つのレコードのみを抽出、変更、削除する場合などに使用されます。

テーブル作成時に主キーを設定するには、以下のように指定します。 

CREATE TABLE テーブル名 (
    カラム名 型名 PRIMARY KEY
);

後から主キーを設定するには、以下のALTER文を実行します。 

ALTER TABLE テーブル名 MODIFY COLUMN カラム名 型名 PRIMARY KEY;

blogテーブルのidカラムを今度は主キーに設定してみましょう。 

mysql> ALTER TABLE blog MODIFY COLUMN id INT PRIMARY KEY;
Query OK, 0 rows affected (0.99 sec)
Records: 0  Duplicates: 0  Warnings: 0

idカラムのKey欄の値がPRIに変わりましたね。同時にNull欄もNO（NOT NULL）に変わっています。 

mysql> show columns from blog;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| title | varchar(100) | NO   |     | NULL    |       |
| text  | text         | NO   |     | NULL    |       |
| date  | datetime     | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

外部キー制約（FOREIGN KEY）

外部キー制約は他のテーブル（子テーブル）の同じ値を持つカラム（参照キー）を参照し、一致する値のみを許可します。これを外部キー参照と呼びます。例えば都道府県を登録するカラムがある場合、都道府県のデータを登録した子テーブルを作成し、親テーブル側でその子テーブルに対して外部キー参照させるなどです。その場合、都道府県以外の値を登録しようとした場合、登録を拒否します。

テーブル作成時に外部キーを設定するには、以下のように指定します。 

CREATE TABLE テーブル名 (
    FOREIGN KEY (カラム名) REFERENCES 参照テーブル名 (参照キー名) ON DELETE RESTRICT ON UPDATE CASCADE
);

後から外部キーを設定するには、以下のALTER文を実行します。 

ALTER TABLE テーブル名 ADD FOREIGN KEY (カラム名) REFERENCES 参照テーブル名(参照キー名) ON DELETE RESTRICT ON UPDATE CASCADE;

注意参照元と参照先のキーの型は同一である必要があります。

まず、blogテーブルに参照元キーとなるcategoryカラムを追加します。 

mysql> ALTER TABLE blog ADD category VARCHAR(100) NOT NULL;
Query OK, 0 rows affected (1.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

一旦blogテーブルの全レコードを削除します。 

DELETE FROM blog;

次に参照先テーブルとして、主キーとしてidカラム、参照先キーとなるcategoryカラムを持つcategory_listテーブルを作成します。 

mysql> CREATE TABLE category_list (
    id INT PRIMARY KEY,
    category VARCHAR(100) UNIQUE KEY NOT NULL
);
Query OK, 0 rows affected (0.71 sec)

mysql> show columns from category_list;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(11)      | NO   | PRI | NULL    |       |
| category | varchar(100) | NO   | UNI | NULL    |       |
+----------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

そしてblogテーブルのcategoryカラムに外部キー制約を設定します。 

mysql> ALTER TABLE blog ADD FOREIGN KEY (category) REFERENCES category_list (category) ON DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (1.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

テーブルのスキーマを見てみると、categoryカラムのKey欄の値がMULと表示されていますね。MULはMultiple keyの略で、外部キー参照しているカラムはこのように表示されます。 

mysql> show columns from blog;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(11)      | NO   | PRI | NULL    |       |
| title    | varchar(100) | NO   |     | NULL    |       |
| text     | text         | NO   |     | NULL    |       |
| date     | datetime     | YES  |     | NULL    |       |
| category | varchar(100) | NO   | MUL | NULL    |       |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

参照先となるcategory_listテーブルにcategory値が「データベース」のレコードを登録してみます。 

mysql> INSERT INTO category_list (id, category) VALUES ('1', 'データベース');
Query OK, 1 row affected (0.09 sec)

mysql> SELECT * FROM category_list;
+----+--------------------+
| id | category           |
+----+--------------------+
|  1 | データベース       |
+----+--------------------+
1 row in set (0.01 sec)

では試しに、参照先キー（category_listテーブルのcategory）に存在しない値を指定してblogテーブルにレコードを挿入してみます。「Cannot add or update a child row」と表示され、挿入は拒否されましたね。 

mysql> INSERT INTO blog (id, title, text, date, category) VALUES ('1', 'ブログ始めました', '今日はデータベースのお勉強をしました。', NOW(), '参照キーにないカテゴリー');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`blog`.`blog`, CONSTRAINT `blog_ibfk_1` FOREIGN KEY (`category`) REFERENCES `category_list` (`category`) ON UPDATE CASCADE)

今度はcategoryの値に参照先キーに登録済みの値である「データベース」を指定して挿入してみます。はい、今度はちゃんと挿入できましたね。 

mysql> INSERT INTO blog (id, title, text, date, category) VALUES ('1', 'ブログ始めました', '今日はデータベースのお勉強をしました。', NOW(), 'データベース');
Query OK, 1 row affected (0.10 sec)
mysql> SELECT * FROM blog;
+------+--------------------------+-----------------------------------------------------------+---------------------+--------------------+
| id   | title                    | text                                                      | date                | category           |
+------+--------------------------+-----------------------------------------------------------+---------------------+--------------------+
|    1 | ブログ始めました         | 今日はデータベースのお勉強をしました。                    | 2020-05-02 23:37:16 | データベース       |
+------+--------------------------+-----------------------------------------------------------+---------------------+--------------------+
1 row in set (0.00 sec)

参照整合性

上記で指定している、ON DELETE RESTRICT および ON UPDATE RESTRICT はそれぞれ以下の意味を持ちます。

ON DELETE RESTRICT参照先キー値の削除を拒否する
ON UPDATE CASCADE参照先キー値が更新されると参照元も同じ値に変更する

これらは参照先の整合性を保つ（参照整合性）ために指定されます。これは参照しているキーの値が変わってしまったり削除されては、参照元のテーブルデータの整合性が取れなくなる（不正な表になってしまう）ため、このような制約を設定する必要があるのです。

参考先程categoryカラムを外部キー参照させる前に一旦blogテーブルのレコードを削除したのはこのためです。

また、ON UPDATE RESTRICT ではなく ON UPDATE CASCADE としているのは、ブログのカテゴリー名や自治体名など、後から参照先キー値を変更する場合もあるためです。

では試しに参照キー値である、category_listテーブルのcategoryの値を変更してみましょう。 

mysql> UPDATE category_list SET category = "MySQL" WHERE id = 1;
Query OK, 1 row affected (0.20 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM category_list;
+----+----------+
| id | category |
+----+----------+
|  1 | MySQL    |
+----+----------+
1 row in set (0.00 sec)

参照元であるblogテーブル側のcategoryカラムの値も更新されている事が確認できますね。 

mysql> SELECT * FROM blog;
+----+-----------------------------------+-----------------------------------------------------------+---------------------+----------+
| id | title                             | text                                                      | date                | category |
+----+-----------------------------------+-----------------------------------------------------------+---------------------+----------+
|  1 | ブログ始めました                  | 今日はデータベースのお勉強をしました。                    | 2020-05-02 23:37:16 | MySQL    |
+----+-----------------------------------+-----------------------------------------------------------+---------------------+----------+
1 rows in set (0.00 sec)

自動インクリメント（AUTO_INCREMENT）

自動インクリメントはレコードが追加される度に自動で値を増やしていく（連番を作成する）機能です。IDカラムなどで使用されます。

テーブル作成時に自動インクリメントキーを設定するには、以下のように指定します。 

CREATE TABLE テーブル名 (
    カラム名 型名 AUTO_INCREMENT
);

後から自動インクリメントを設定するには、以下のALTER文を実行します。 

ALTER TABLE テーブル名 MODIFY カラム名 型名 AUTO_INCREMENT;

blogテーブルのidカラムに自動インクリメントを設定してみましょう。 

mysql> ALTER TABLE blog MODIFY id INT AUTO_INCREMENT;
Query OK, 1 row affected (1.09 sec)
Records: 1  Duplicates: 0  Warnings: 0

idカラムのExtra欄にauto_incrementが表示されるようになりました。 

mysql> show columns from blog;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| title    | varchar(100) | NO   |     | NULL    |                |
| text     | text         | NO   |     | NULL    |                |
| date     | datetime     | YES  |     | NULL    |                |
| category | varchar(100) | NO   | MUL | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
5 rows in set (0.06 sec)

試しに、idカラムを指定せずに、レコードを挿入してみます。 

mysql> INSERT INTO blog (title, text, date, category) VALUES ('自動インクリメントあり', 'これからはID値の指定は不要！', NOW(), 'MySQL');
Query OK, 1 row affected (0.09 sec)

きちんとid値がインクリメントして挿入されていることが分かりますね。 

mysql> SELECT * FROM blog;
+----+-----------------------------------+-----------------------------------------------------------+---------------------+----------+
| id | title                             | text                                                      | date                | category |
+----+-----------------------------------+-----------------------------------------------------------+---------------------+----------+
|  1 | ブログ始めました                  | 今日はデータベースのお勉強をしました。                    | 2020-05-03 02:43:28 | MySQL    |
|  2 | 自動インクリメントあり            | これからはID値の指定は不要！                              | 2020-05-03 03:04:00 | MySQL    |
+----+-----------------------------------+-----------------------------------------------------------+---------------------+----------+
2 rows in set (0.00 sec)

category_listテーブルのidカラムも自動インクリメントを設定しておきます。 

mysql> ALTER TABLE category_list MODIFY id INT AUTO_INCREMENT;
Query OK, 1 row affected (1.12 sec)
Records: 1  Duplicates: 0  Warnings: 0

まとめ

これまでのいくつかの制約を設定することで、最終的にブログ用テーブルとして以下のような2つのテーブルが作成されました。 

mysql> show columns from blog;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| title    | varchar(100) | NO   |     | NULL    |                |
| text     | text         | NO   |     | NULL    |                |
| date     | datetime     | YES  |     | NULL    |                |
| category | varchar(100) | NO   | MUL | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> show columns from category_list;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| category | varchar(100) | NO   | UNI | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
2 rows in set (0.07 sec)

これらの制約をテーブルの作成段階で設定する場合は、以下のようなCREATE TABLE文になります。 

CREATE TABLE category_list (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category VARCHAR(100) UNIQUE KEY NOT NULL
);

CREATE TABLE blog (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    text TEXT NOT NULL,
    date DATETIME,
    category VARCHAR(100) NOT NULL,
    FOREIGN KEY (category) REFERENCES category_list(category) ON DELETE RESTRICT ON UPDATE CASCADE
);

以上、主なMySQLの制約と自動インクリメントについての解説でした。

公開日時：2020年05月03日 10:40:09

なお、レンタルサーバー選びで迷ったらこちらの記事で主要レンタルサーバーのプランと料金を比較していますので、是非参考にしてみてください。

データベース編に戻る

「データベース編」に関する他の手順
カテゴリ一覧

このページのトップに戻る