Bug #68534 Primary Key - NULL Paradox
Submitted: 1 Mar 2013 7:00 Modified: 17 Apr 2013 17:09
Reporter: Sandeep Tetali Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S3 (Non-critical)
Version:MySQL 5.5 OS:Windows
Assigned to: CPU Architecture:Any

[1 Mar 2013 7:00] Sandeep Tetali
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.
[17 Apr 2013 17:09] Ruben Dario Morquecho Valdez
Thank you for taking the time to write to us

This is Mysql Server normal behavior
when removing primary key, it will just remove primary key field (and will avoid change/touch any other values/parameters)
therefore , this behavior is cascaded to Workbench behavior adn its not considered as a bug.

Thanks for interesting on Workbench !