Description:
As part of a large database design, the MySQL validation tool (Model >> Validation - MySQL) reported several design errors, such as "NULL default value for not-null column".
So we fixed such error designs changing all default values for not-null columns.
However, the specified values (DEFAULT '', or DEFAULT 'ABC') were not properly exported by any of the available tools, and were always reported as "DEFAULT NULL".
We attach an .mwb file containing only one of our tables, yet the error persists.
How to repeat:
1. open the attached test.mwb file
2. go to Model >> Validation (MySQL) >> Validate all
3. The output panel states the following 2 errors:
NULL default value for not-null column 'companyId' in table 'tblAgent'.
NULL default value for not-null column 'creditLimit' in table 'tblAgent'.
4. However if you check both column definitions, you can see that default value for tblAgent.companyId is 'ABC', and that default value for tblAgent.creditLimit is '0'
You can also follow the following steps:
1. open the attached test.mwb file
2. go to File >> Export >> Forward Engineer SQL CREATE Script
3. proceed with default selections
4. the last step shows the following error:
Error 1067: Invalid default value for 'companyId'
-- -----------------------------------------------------
-- Table `oc`.`tblAgent`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `oc`.`tblAgent` (
`agentId` CHAR(32) NOT NULL ,
`companyId` CHAR(32) NOT NULL DEFAULT NULL ,
`bankAccount` VARCHAR(30) NULL DEFAULT NULL ,
`active` TINYINT(1) NULL DEFAULT 1 ,
`deleted` TINYINT(1) NULL DEFAULT 0 ,
`creditLimit` INT(10) NOT NULL DEFAULT NULL ,
`paymentType` VARCHAR(20) NULL DEFAULT NULL ,
PRIMARY KEY (`agentId`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
SQL script execution finished: statements: 8 succeeded, 1 failed
As you can see, the 'companyId' field has a DEFAULT NULL clause.
