Description:
When i create a table without a primary key (or with doesn't really matter) then using alter command add a primary key to one of the attributes and then again using alter command we drop the primary key of the specific attribute.
NOTICE that before the addition of primary key NULL was YES after adding the primary key NULL turned to NO along with Key to PRI (on the specific attribute) but on altering it and dropping the primary key Key would NULL would remain as NO which might effect my alteration idea.
How to repeat:
mysql> create database march1;
Query OK, 1 row affected (0.00 sec)
mysql> use march1;
Database changed
mysql> create table abc (a int, b int, c int);
Query OK, 0 rows affected (0.07 sec)
mysql> desc abc;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | int(11) | YES | | NULL | |
| b | int(11) | YES | | NULL | |
| c | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> alter table abc add primary key(a);
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc abc;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | int(11) | NO | PRI | 0 | |
| b | int(11) | YES | | NULL | |
| c | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> alter table abc drop primary key;
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc abc;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | int(11) | NO | | 0 | |
| b | int(11) | YES | | NULL | |
| c | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.01 sec)
Suggested fix:
Once i alter the primary key and drop it from a table its value of not being null should turn to YES so that the idea of altering and dropping the primary key is preserved.