MySQLで同じ列にUNIQUE制約を複数回追加するとどうなる？インデックスの挙動を解説

はじめに

MySQLでは、同一の列に対してUNIQUE制約を複数回追加することが可能です。しかし、そのたびにMySQLは新たなユニークインデックスを作成します。つまり、UNIQUE制約を追加した回数だけ、その列にインデックスが重複して作成されることになります。

本記事では、実際の操作例を通じて、この挙動をわかりやすく解説します。

検証環境の準備：employeeテーブルの確認

まず、empid列にUNIQUE制約が設定されたテーブル「employee」を用意します。DESCRIBE文を使うと、Key欄に「UNI」と表示され、UNIQUE制約が付与されていることが確認できます。

mysql> Describe employee;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| empid      | int(11)     | YES  | UNI | NULL    |       |
| first_name | varchar(20) | YES  |     | NULL    |       |
| last_name  | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.12 sec)

初期状態のインデックスを確認する

SHOW INDEX文を実行すると、現時点で作成されているインデックスを一覧できます。この段階では、empid列には「empid」という名前のインデックスが1つだけ存在しています。

mysql> Show index from employee\G;
*************************** 1. row ***************************
        Table: employee
   Non_unique: 0
     Key_name: empid
 Seq_in_index: 1
  Column_name: empid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

2つ目のUNIQUE制約を追加する

次に、ALTER TABLE文を使って、同じempid列にもう一度UNIQUE制約を追加してみます。

mysql> Alter table employee ADD UNIQUE(empid);
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0

再度SHOW INDEXを実行すると、今度は「empid」と「empid_2」という2つのインデックスが表示されます。MySQLは既存のインデックス名と重複しないよう、自動的に連番のサフィックス（_2）を付けた新しい名前でインデックスを作成しているのがわかります。

mysql> Show index from employee\G;
*************************** 1. row ***************************
        Table: employee
   Non_unique: 0
     Key_name: empid
 Seq_in_index: 1
  Column_name: empid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: employee
   Non_unique: 0
     Key_name: empid_2
 Seq_in_index: 1
  Column_name: empid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

3つ目のUNIQUE制約を追加する

さらに、同じ操作をもう一度実行してみましょう。

mysql> Alter table employee ADD UNIQUE(empid);
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> Show index from employee\G;
*************************** 1. row ***************************
        Table: employee
   Non_unique: 0
     Key_name: empid
 Seq_in_index: 1
  Column_name: empid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: employee
   Non_unique: 0
     Key_name: empid_2
 Seq_in_index: 1
  Column_name: empid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 3. row ***************************
        Table: employee
   Non_unique: 0
     Key_name: empid_3
 Seq_in_index: 1
  Column_name: empid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
3 rows in set (0.00 sec)

結果として、「empid」「empid_2」「empid_3」という3つのユニークインデックスが、同じempid列上に存在することになります。

まとめ

このように、MySQLではUNIQUE制約を追加した回数だけ、対象の列にユニークインデックスが作成されます。エラーにはなりませんが、不要なインデックスが増えると以下のようなデメリットがあります。

• ストレージ容量の無駄な消費
• INSERT・UPDATE・DELETE時のパフォーマンス低下

したがって、実際の開発では、同じ列にUNIQUE制約を重複して追加しないよう注意しましょう。すでにUNIQUE制約が存在するかどうかは、事前にSHOW INDEXやDESCRIBE文で確認するのがおすすめです。