asked on

Is my CREATE TABLE syntax wrong?

Is there a problem with my SQL syntax here, problem is pointing to the Double entry for some reason. I am using MySQL and HeidiSQL to create the table.

CREATE TABLE `wind_data` (
      `ID` INT(10) NOT NULL AUTO_INCREMENT,
      `DATE` DATE NULL DEFAULT NULL,
      `TIME` TIME NULL DEFAULT NULL,
      `DAT1` CHAR(10) NULL DEFAULT NULL,
      `WD` INT(3) NULL DEFAULT NULL,
      `WS` DOUBLE(10) NULL DEFAULT NULL,
      `DAT2` CHAR(10) NULL DEFAULT NULL,
      `DAT3` CHAR(10) NULL DEFAULT NULL,
      `DAT4` CHAR(10) NULL DEFAULT NULL,
      PRIMARY KEY (`ID`)
)
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

ASKER CERTIFIED SOLUTION

dsmile

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

sunny-s

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dsmile

Or you have to specify the number of digits after decimal point also

Try this

CREATE TABLE `wind_data` (
`ID` INT(10) NOT NULL AUTO_INCREMENT,
`DATE` DATE NULL DEFAULT NULL,
`TIME` TIME NULL DEFAULT NULL,
`DAT1` CHAR(10) NULL DEFAULT NULL,
`WD` INT(3) NULL DEFAULT NULL,
`WS` DOUBLE(10,2) NULL DEFAULT NULL,
`DAT2` CHAR(10) NULL DEFAULT NULL,
`DAT3` CHAR(10) NULL DEFAULT NULL,
`DAT4` CHAR(10) NULL DEFAULT NULL,
PRIMARY KEY (`ID`)
)
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

SOLUTION

max-hb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sunny-s

also, note the MySQL needs two parameters after the word DOUBLE, or none, not one like you specified.

MySQL permits a nonstandard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here, “(M,D)” means than values can be stored with up to M digits in total, of which D digits may be after the decimal point. For example, a column defined as FLOAT(7,4) will look like -999.9999 when displayed. MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001.

bhession

ASKER

Great answering guys, you predicted my follow up questions as well.