Adding Index when Creating a Table Error 1064

Question

I get the following error:

ERROR 1064 (42000) at line 5: 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 'ADD INDEX idx_latitude (latitude)
ADD INDEX idx_longitude (longitude)
ADD INDEX ' at line 21

My code where I attempt to create these indexes:

CREATE TABLE allCountries ( 
geonameid int(15) PRIMARY KEY, 
name varchar(200), 
asciiname varchar(200), 
alternatenames varchar(4000), 
latitude decimal(11,7), 
longitude decimal(11,7), 
fclass char(1), 
fcode varchar(10), 
country varchar(2), 
cc2 varchar(60), 
admin1 varchar(20), 
admin2 varchar(80), 
admin3 varchar(20), 
admin4 varchar(20), 
population int(15), 
elevation int(15) NULL, 
gtopo30 int(15), 
timezone varchar(40), 
moddate date 
ADD INDEX idx_latitude (latitude),
ADD INDEX idx_longitude (longitude),
ADD INDEX idx_fclass (fclass),
) CHARACTER SET utf8;

Can someone point me in the right direction please?

I have tried to use ; after the brackets, which also did not help.

Sam · Accepted Answer · 2013-11-19 14:45:06Z

2

You missed a comma after moddate, and not sure you need the ADD.....

...
elevation INT(15) NULL, 
gtopo30 INT(15), 
timezone VARCHAR(40), 
moddate DATE, 
INDEX (latitude),
INDEX (longitude),
INDEX (fclass)

Or you could create your index in a separate statement after you've created your table, which would probably be easier.

CREATE INDEX id_latitude ON allCountries(latitude)

answered Nov 19, 2013 at 14:45

Sam

7,3333 gold badges27 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3009207 Over a year ago

Thanks alot Sam I appreciate you quick reply.

user3009207 Over a year ago

I added the CREATE INDEX Statement once the table was created and my SQL dumb ran fine without error :D

Collectives™ on Stack Overflow

Adding Index when Creating a Table Error 1064

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related