MySQL Bugs: #52752: SQL Editor does not check whether a NOT NULL column has a default value of NULL

Bug #52752	SQL Editor does not check whether a NOT NULL column has a default value of NULL
Submitted:	12 Apr 2010 6:15	Modified:	12 Apr 2010 6:40
Reporter:	Nubio Revoredo	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.17 OSS	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	DEFAULT, null, sql editor

Description:
The MySQL Workbench SQL Editor does not check whether a NOT NULL column has a default value of NULL.

It is usual to alter a column which allow NULL values to become NOT NULL. In this case, using the visual editor the user may only click the NN (NOT NULL) checkbox and expect the value of the corresponding "Default" cell to become clear.

If the user forget to check the value of the corresponding "Default" cell he will receive a "ERROR 1067: Invalid default value for 'COLUMN'" message in the message log.

I think it is desirable to avoid displaying errors in the message log that are avoidable by checking in the GUI and showing a more friendly message instead. For instance, the same dialog does not allow invalid values in the "Datatype" cells.

How to repeat:
1. Right click the item "Table" in "Object Browser";
2. Click the "Create Table..." menu item;
3. Click the "Columns" tab;
4. Double click the cell "Default" of the first row (Column Name: "idnew_table");
5. Type NULL in the cell;
6. Click the "Apply" button;
7. Click "Apply SQL" in the "Apply SQL Script to database" dialog;

The message log shows:
ERROR 1067: Invalid default value for 'idnew_table'
SQL Statement:
CREATE TABLE IF NOT EXISTS `fourmeter`.`new_table` (
`idnew_table` INT NOT NULL DEFAULT NULL ,
PRIMARY KEY (`idnew_table`) )

Suggested fix:
The SQL Editor may check if the NN checkbox is checked and the default cell has the value "NULL". In this case it may show a alert dialog. This check may take place when the user click the "Apply" button.

The visual editor dialog

Attachment: Visual Editor.png (image/png, text), 49.83 KiB.

The Apply SQL Script to Database dialog

Attachment: Apply SQL Script to Database.png (image/png, text), 84.78 KiB.

Sounds like a reasonable feature request.