not null制約がついたカラムにunique key 制約つけるとnot null制約がはずれる

mysql> create table a(col1 int);
mysql> alter table a modify col1 not null;
mysql> desc a;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1  | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> alter table a modify col1 int unique;
mysql> desc a;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1  | int(11) | YES  | UNI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

uniqueキーを貼るときは上記を踏まえた上で、nullを許容しないのであればnot null制約も同時につけること。

mysql> create table a (col1 int);
mysql> alter table a modify col1 int not null;
mysql> show columns from a;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1  | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.04 sec)

alter table a modify col1 int not null unique;
mysql> desc a;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1  | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.06 sec)