Why does MySQL Workbench produce incompatible syntax for simple table create on export?

Question

Using MySQL Workbench 5.0.30 OSS, I try to produce a simple SQL script to create tables, yet what it produces doesn't seem to be the least bit compatible with MySQL Ver 14.14 Distrib 5.1.42, for Win32 (ia32).

Update: Here is the SQL generated from MySQL Workbench:

CREATE  TABLE IF NOT EXISTS `mydb`.`Table1` (
  `idClaimNum`  NOT NULL ,
  `URI` VARCHAR(150) NOT NULL ,
  PRIMARY KEY (`idClaimNum`) )
ENGINE = InnoDB;

Error recieved:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the ma nual that corresponds to your MySQL server version for the right syntax to use n ear 'NOT NULL , URI VARCHAR(150) NOT NULL , PRIMARY KEY (idClaimNum) ) ENGINE' at line 2

Answer 1

Well, since you didn't post the SQL that was created, why don't you upgrade Workbench? The latest stable is 5.1.18

Answer 2

Looks to me like you should have checked auto_increment on idClaimNum. I don't think you can have column without a type and a NOT NULL column without a default value.

I suspect if you tried to execute this from MySQL Workbench it would give you an error as well. (I have noticed that the table editor in MySQL Workbench is really just a SQL creator and not a validator.)

I think if you make idClaimNum an auto_increment column you will see MySQL Workbench creates SQL more like this (and it will run successfully):

CREATE  TABLE IF NOT EXISTS `mydb`.`Table1` (
  `idClaimNum` INTEGER NOT NULL AUTO_INCREMENT,
  `URI` VARCHAR(150) NOT NULL ,
  PRIMARY KEY (`idClaimNum`) )
ENGINE = InnoDB;