CODEWITHSUNDEEP
mysqlsqldatabasedouble
progdoc
progdoc

Reputation: 609

#1064 mysql error - DOUBLE NOT NULL

I'm trying to create an sql table called 'cartable'. I'm currently using phpmyadmin via MAMP(Mac Version of WAMP).

When I click on the save button this error appears:

    SQL query:

CREATE TABLE `cardatabase`.`CARTABLE` (
`ID` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`SLOTNUMBER` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`STATUS` ENUM( 1 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`LATITUDE` DECIMAL( 20 ) NOT NULL ,
`LONGITUDE` DECIMAL( 20 ) NOT NULL ,
`LOCATION` VARCHAR( 40 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`DEVICEID` VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `LATITUDE` DECIMAL(20) N' at line 1

It seems to be a problem with the double data type as when I change it to varchar the table is created. Is there any other way around it? Thanks in advance, any help is much appreciated.

Upvotes: 0

Views: 6388

Answers (2)

paxdiablo
paxdiablo

Reputation: 881513

Your problem lies here:

`STATUS` ENUM( 1 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,

An enum type is supposed to be a string object, not a numeric one.

From the MySQL doco,

An ENUM is a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time.

An enumeration value must be a quoted string literal; it may not be an expression, even one that evaluates to a string value.

Upvotes: 3

user987346
user987346

Reputation:

you have error in enum line, you need to predefine the enum values as below:

STATUS ENUM('ravi', 'kotwani') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,

Full query:

CREATE TABLE cardatabase.CARTABLE ( ID VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , SLOTNUMBER VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , STATUS ENUM('ravi', 'kotwani') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , LATITUDE DECIMAL( 20 ) NOT NULL , LONGITUDE DECIMAL( 20 ) NOT NULL , LOCATION VARCHAR( 40 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , DEVICEID VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

Upvotes: 2

Related Questions