[MySQL] キー制約

キー制約について、
調べたことをまとめる。

参考：https://www.shift-the-oracle.com/constraint/

○プライマリーキー

表の各行を完全に一意に識別するためのカラム。

特徴：
・重複するデータを格納できない
・nullは禁止
・各テーブルに1つ(または1組)

○ユニークキー

表のNull以外の行を一意に制約するカラム。

特徴：
・重複するデータを格納できない
・NullはOK
・表に複数あってもいい

○外部キー

関連する表のキーの値に一致するデータのみ、
存在を認められるカラム。

●自分なりの解釈

いずれも”キー”という名前を持ってたり、
似たような特徴を持っているため、
同じ類の物なのかなという印象だったが、
そうではなさそう。

プライマリーキーは、
表の中で、「レコードを一意に識別する」
という明確な役割を持っていて、
特徴は、その役割ゆえに自然と必要になる事柄。

一方、ユニークキー・外部キーは、
そのカラムに格納するデータに制約を与えるのが目的。
つまり、上記の特徴ありきのもの。

というように解釈しました。
要は、プライマリーキーと他2つは、
同列に考えてはならない。

●設定方法

参考：
https://www.dbonline.jp/mysql/table/
https://qiita.com/katsukii/items/992a04aaec5fc87919f3

○プライマリー/ユニークキーの設定

・作成時にプライマリーキー/ユニークキーを設定する。
mysql> create table test1 (id int primary key, name varchar(10) unique);

・テーブル作成後にプライマリー/ユニークキーを追加する
mysql> alter table test1 add primary key (id);
mysql> alter table test1 add unique (name);

・確認
mysql> show columns from test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(10) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+

mysql> show create table test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> show index from test1\G
*************************** 1. row ***************************
        Table: test1
   Non_unique: 0
     Key_name: PRIMARY
Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: test1
   Non_unique: 0
     Key_name: name
Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:

○プライマリー/ユニークキーを削除する

mysql> alter table test1 drop primary key;
mysql> alter table test1 drop index name;

mysql> show columns from test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

・削除後の確認
mysql> show create table test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> show index from test1\G
Empty set (0.00 sec)

プライマリーキーのnot null属性まで削除されない。
削除する場合は、
mysql> alter table test1 change id id int;
mysql> show columns from test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

○外部キーの設定

・テーブル作成時
mysql> create table test2 (
    -> test1_id int, name varchar(20),
    -> foreign key (test1_id) references test1(id));

・テーブル作成後に追加
mysql> alter table test2 add foreign key (test1_id) references test1(id);


・確認
mysql> show columns from test2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| test1_id | int(11)     | YES  | MUL | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

mysql> show create table test2\G
*************************** 1. row ***************************
       Table: test2
Create Table: CREATE TABLE `test2` (
  `test1_id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  KEY `test1_id` (`test1_id`),
  CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`test1_id`) REFERENCES `test1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> show index from test2\G
*************************** 1. row ***************************
        Table: test2
   Non_unique: 1
     Key_name: test1_id
Seq_in_index: 1
  Column_name: test1_id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:

○外部キーを削除

mysql> alter table test2 drop foreign key test2_ibfk_1;
mysql> alter table test2 drop key test1_id;

・削除後の確認
mysql> show columns from test2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| test1_id | int(11)     | YES  |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

mysql> show create table test2\G
*************************** 1. row ***************************
       Table: test2
Create Table: CREATE TABLE `test2` (
  `test1_id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> show index from test2\G
Empty set (0.00 sec)

インデックスについて
https://qiita.com/C058/items/1c9c57f634ebf54d99bb