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:
Category:MySQL Workbench: SQL Editor Severity:S3 (Non-critical)
Version:5.2.17 OSS OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: DEFAULT, null, sql editor
Triage: Needs Triage: D5 (Feature request)

[12 Apr 2010 6:15] Nubio Revoredo
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.
[12 Apr 2010 6:20] Nubio Revoredo
The visual editor dialog

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

[12 Apr 2010 6:21] Nubio Revoredo
The Apply SQL Script to Database dialog

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

[12 Apr 2010 6:40] Valeriy Kravchuk
Sounds like a reasonable feature request.