MySQLにおいてNullableなカラムを含むUNIQUE制約の注意点
Nullableなカラムを含むUNIQUE制約を定義した際に起きた問題のメモ。
MySQLでは、Nullableなカラムを含むUNIQUE制約を定義した場合、NULLのinsert時には一意制約エラーとならないので注意する必要がある。
code:status
mysql> \s
--------------
mysql Ver 8.0.20-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
:
Server version: 5.7.25-log MySQL Community Server (GPL)
--------------
code:create_table.sql
create table unique_test (
id integer,
name varchar(128), -- nullable column
role varchar(128), -- nullable column
primary key (id),
unique key nullable (name, role)
);
code:insert_test.sql
insert into unique_test (id, name, role) values ('1', 'tom', 'programmer');
Query OK, 1 row affected (0.01 sec)
insert into unique_test (id, name, role) values ('2', 'tom', null);
Query OK, 1 row affected (0.01 sec)
insert into unique_test (id, name, role) values ('3', null, 'programmer');
Query OK, 1 row affected (0.01 sec)
insert into unique_test (id, name, role) values ('4', null, null);
Query OK, 1 row affected (0.01 sec)
insert into unique_test (id, name, role) values ('5', 'tom', 'designer');
Query OK, 1 row affected (0.00 sec)
insert into unique_test (id, name, role) values ('6', 'tom', 'designer');
ERROR 1062 (23000): Duplicate entry 'tom-designer' for key 'nullable'
insert into unique_test (id, name, role) values ('6', NULL, NULL);
Query OK, 1 row affected (0.00 sec)
select * from unique_test order by id;
+----+------+------------+
| id | name | role |
+----+------+------------+
| 1 | tom | programmer |
| 2 | tom | NULL |
| 3 | NULL | programmer |
| 4 | NULL | NULL |
| 5 | tom | designer |
| 6 | NULL | NULL |
+----+------+------------+
6 rows in set (0.00 sec)
参考