MySQL Bugs: #53543: Forward Engineering Create Script generation adding incorrect default values

Bug #53543	Forward Engineering Create Script generation adding incorrect default values
Submitted:	10 May 2010 16:33	Modified:	17 May 2010 12:26
Reporter:	Brian Pearson	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.19	OS:	MacOS
Assigned to:		CPU Architecture:	Any
Tags:	"NOT NULL DEFAULT NULL", incorrect default values

Description:
When generating a forward engineering create script for columns set to NOT NULL, UNIQUE (likely happens without the UNIQUE setting), with no default value, the script generator is adding DEFAULT NULL to the table create statement.  This is a lookup table and there should not be a DEFAULT value, that is you should never be able to create an entry without specifying a value for the column.

Example:
CREATE  TABLE IF NOT EXISTS `Schema`.`table_access` (
  `id` INT(10) NOT NULL AUTO_INCREMENT ,
  `Access` VARCHAR(50) NOT NULL DEFAULT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `ID` (`id` ASC) ,
  UNIQUE INDEX `Access_UNIQUE` (`Access` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 4
DEFAULT CHARACTER SET = latin1;

How to repeat:
in MySQL Workbench create a schema and add a table with a NOT NULL column, select Forward Engineer SQL CREATE Script...

View the script you will notice the column now states NOT NULL DEFAULT NULL in the table create statement.

Suggested fix:
If there is not DEFAULT don't add one.

Brian, i cannot reproduce the problem. could you maybe provide a demo document (you can mark them as private if you want)

Test MWB file, shows issue

Attachment: Test.mwb (application/octet-stream, text), 41.63 KiB.

Create script generated by Forward Engineering.

Attachment: Test_Create_Script.sql (application/octet-stream, text), 1.92 KiB.

Sorry for the delay, under the gun to get a project done.

I have uploaded a sample file and the resulting forward engineering script that shows the issues namely "NOT NULL DEFAULT NULL" on columns in the create script.

Hope this helps,

BP

Many thanks for writing a bug report.

This is not a bug. This is an expected server behaviour.

Look here:

http://dev.mysql.com/doc/refman/5.1/en/data-type-defaults.html

" If a column definition includes no explicit DEFAULT value, MySQL determines the default value as follows:

If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause. "