Why is the MySQL query giving a syntax error

Question

I have the following query which I am running in MySQL:

CREATE TABLE BookingGuide (
    'ID_INT' INTEGER NOT NULL primary key AUTO_INCREMENT,
    'NAME' VARCHAR(60),
    'SPECIALTY' VARCHAR(60)
    ) ENGINE=INNODB;

I am receiving the following error:

#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 ''ID_INT' INTEGER NOT NULL primary key AUTO_INCREMENT,
     'NAME' VARCHAR(60),
 ' at line 2

How can I resolve the issue?

This might be helpful for you: [Do different databases use different name quote?](http://stackoverflow.com/questions/214309/do-different-databases-use-different-name-quote/214344#214344) — Bill Karwin, May 13 '14 at 21:42

score 4 · Accepted Answer · answered May 13 '14 at 20:59

4

Get rid of the quotes around your column identifiers. Use ticks or nothing at all.

CREATE TABLE BookingGuide (
    ID_INT INTEGER NOT NULL primary key AUTO_INCREMENT,
    NAME VARCHAR(60),
    SPECIALTY VARCHAR(60)
) ENGINE=INNODB;

or

CREATE TABLE BookingGuide (
    `ID_INT` INTEGER NOT NULL primary key AUTO_INCREMENT,
    `NAME` VARCHAR(60),
    `SPECIALTY` VARCHAR(60)
) ENGINE=INNODB;

answered May 13 '14 at 20:59

John Conde

217,595
99
455
496

I am used to SQL programming and wasn't aware of the ` instead of the '. Thanks. – Si8 May 13 '14 at 21:01
1

@SiKni8 yes in mysql single quotes are not supported for col names , if you wish you can use backtick else leave it without anything, but beware mysql has set of reserved keywords and if you by any chance use them then you have to backtick them else you will get error. – Abhik Chakraborty May 13 '14 at 21:04
@AbhikChakraborty I am guessing it's the same in SQL as well but thank you for sharing. – Si8 May 13 '14 at 21:05

score 2 · Answer 2 · answered May 13 '14 at 20:59

2

try this

     CREATE TABLE BookingGuide (
     ID_INT INTEGER NOT NULL primary key AUTO_INCREMENT,
     NAME VARCHAR(60),
     SPECIALTY VARCHAR(60)
     ) ENGINE=INNODB;

instead of single quote you need backticks.

answered May 13 '14 at 20:59

echo_Me

37,078
5
58
78

I am used to SQL programming and wasn't aware of the ` instead of the '. Thanks. – Si8 May 13 '14 at 21:02

Why is the MySQL query giving a syntax error

2 Answers2