MySQLで複数カラムのUNIQUEインデックスを作成する方法

MySQLで複数カラムのUNIQUEインデックスを作成する方法

複数のカラム（列）を組み合わせたUNIQUEインデックス（複合ユニークインデックス）を作成する場合は、CREATE UNIQUE INDEX文で、複数のカラムに対してひとつのインデックス名を指定します。これにより、各カラム単体ではなく、指定したカラムの値の組み合わせ全体が一意であることが保証されます。

基本構文

CREATE UNIQUE INDEX インデックス名 ON テーブル名(カラム名1, カラム名2, ...);

実行例

次の例では、「employee」テーブルの「empid」「first_name」「last_name」という3つのカラムに対して、「id_fname_lname」という名前の複合UNIQUEインデックスを作成しています。

mysql> CREATE UNIQUE INDEX id_fname_lname ON employee(empid, first_name, last_name);
Query OK, 0 rows affected (0.41 sec)
Records: 0 Duplicates: 0 Warnings: 0

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

上記クエリの結果から、テーブルに複合インデックスが正しく定義されたことが確認できます。

作成したインデックスの詳細を確認する

作成済みインデックスの詳細情報を取得したい場合は、SHOW INDEX文を使用します。

mysql> SHOW INDEX FROM employee\G
*************************** 1. row ***************************
Table: employee
Non_unique: 0
Key_name: id_fname_lname
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: id_fname_lname
Seq_in_index: 2
Column_name: first_name
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: id_fname_lname
Seq_in_index: 3
Column_name: last_name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
3 rows in set (0.00 sec)

結果の見方

上記の結果セットでは、すべての行でKey_nameフィールドの値が同じ「id_fname_lname」になっていることがわかります。これは、テーブルの全カラムに対してひとつの複合インデックスを作成したためです。

また、Non_uniqueが0になっていることから、このインデックスがUNIQUE（一意）制約を持つことも確認できます。Seq_in_indexは、インデックス内でのカラムの順序（empidが1番目、first_nameが2番目、last_nameが3番目）を表しています。

なお、複合UNIQUEインデックスでは、単一カラム内の値の重複は許容されますが、指定した全カラムの値の組み合わせが完全に一致するレコードは挿入できなくなる点に注意してください。