MySQL Bugs: #53918: Change Field to Not Null does not work

Bug #53918	Change Field to Not Null does not work
Submitted:	23 May 2010 0:06	Modified:	11 Jun 2010 15:44
Reporter:	Jesse Castleberry	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.21 RC	OS:	Windows (7)
Assigned to:	Alexander Musienko	CPU Architecture:	Any

Description:
When you have an existing field that does not have the "NN" check box checked, then you go back in later and try to change it to "NN", you cannot.  Even if you right-click the default value and select Clear Default, and check the "NN" check box, when you try to apply the change, you get the error, "Invalid Default Value", because evidentially, even though you've cleared the default value, it puts it back.

How to repeat:
1. Create a table with some fields, and make one of them where NN is not checked.
2. Save the table.
3. Alter the table, clear the default value for that field.
4. Check the NN check box and click the Apply button and follow through.

Suggested fix:
1. Check you check the "NN" check box, automatically clear the default value if the default value is set to "NULL".
2. make sure that if you "clear default value" that it stays clear.

Indeed, the following code is generated:

ALTER TABLE `test`.`tnull` CHANGE COLUMN `c2` `c2` INT(11) NOT NULL DEFAULT NULL  ;

Workbench should do better job and take into account that NULL default is impossible for NOT NULL column.

fix confirmed in repository

An entry has been added to the 5.2.23 changelog:

In the Alter Table dialog of the SQL Editor, if an attempt was made to set the NN flag for a column that did not have it set, an error was generated when the changes are applied.