UNIQUE インデックスは、インデックス内の全ての値は明確でなければいけないというような制限を作成します。
既存行とマッチするキー値の新しい行を追加しようとするとエラーが発生します。
全てのエンジンに対して、UNIQUE インデックスは NULL を含む事ができるカラムの複数 NULL 値を許容します


mysql> CREATE TABLE `A1` (
-> `comment` varchar(10) DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> desc A1;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(10) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> CREATE UNIQUE INDEX IDX_UQ_A1 ON A1(comment);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc A1;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(10) | YES | UNI | NULL | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql>

unique_idx

※ UNIQUE index will be UNI or PRI if the index does not allow NULL values.
※ MUL if the index does allow NULL values because NULL in a UNIQUE index is a special case:
Multiple NULL values are allowed, unlike any other value.


mysql> desc A1;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(10) | YES | UNI | NULL | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO A1 VALUES (NULL),(NULL),('data'),('test'),(NULL);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from A1;
+---------+
| comment |
+---------+
| NULL |
| NULL |
| NULL |
| data |
| test |
+---------+
5 rows in set (0.00 sec)

mysql> INSERT INTO A1 VALUES('data');
ERROR 1062 (23000): Duplicate entry 'data' for key 'IDX_UQ_A1'
mysql>

unique_idx_null

参考サイト
12.1.7. CREATE INDEX 構文

Comments are closed.

Post Navigation