Error in SQL Syntax (MYSQL 5.0)

Question

Does anybody know what is wrong in this MYSQL 5.0 syntax?

CREATE TABLE IF NOT EXISTS target (
  _id int(11) NOT NULL AUTO_INCREMENT,
  time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  map_id int(11) DEFAULT NULL,
  left int(11) DEFAULT NULL,
  top int(11) DEFAULT NULL,
  status tinyint(1) NOT NULL,
  temperature int(11) DEFAULT NULL,
  humidity float DEFAULT NULL,
  lum int(11) DEFAULT NULL,
  PRIMARY KEY (_id),
  FOREIGN KEY (map_id) REFERENCES map(id) ON DELETE CASCADE
)

I'll show you the error:

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 'left INTEGER DEFAULT NULL, top INTEGER DEFAULT NULL, status tinyint(1) NOT' at line 5

possible duplicate of How can I write SQL for a table that shares the same name as a protected keyword in MySql? — Jocelyn
– Jocelyn, Commented May 6, 2013 at 6:39

WWW · Accepted Answer · 2011-12-29 17:44:27Z

2

Because left is a MySQL 5.0 reserved word. Also, even though you can escape the field name, it's never a great idea to use reserved words in a table definition.

edited Dec 29, 2011 at 17:44

answered Dec 29, 2011 at 17:32

WWW

9,8682 gold badges32 silver badges33 bronze badges

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

Comments

macjohn · Accepted Answer · 2011-12-29 17:47:47Z

2

you must write it like this:

CREATE TABLE IF NOT EXISTS target (
  _id int(11) NOT NULL AUTO_INCREMENT,
  time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  map_id int(11) DEFAULT NULL,
  `left` int(11) DEFAULT NULL,
  top int(11) DEFAULT NULL,
  status tinyint(1) NOT NULL,
  temperature int(11) DEFAULT NULL,
  humidity float DEFAULT NULL,
  lum int(11) DEFAULT NULL,
  PRIMARY KEY (_id),
  FOREIGN KEY (map_id) REFERENCES map(id) ON DELETE CASCADE
)

look at the `` (backticks) characters in left row !

edited Dec 29, 2011 at 17:47

answered Dec 29, 2011 at 17:34

macjohn

1,80315 silver badges18 bronze badges

Comments

Bjoern · Accepted Answer · 2011-12-29 17:35:16Z

0

You're using reserved words as field names. You can do that, but then you have to properly escape them, like this:

CREATE TABLE IF NOT EXISTS target (
  `_id` int(11) NOT NULL AUTO_INCREMENT,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `map_id` int(11) DEFAULT NULL,
  `left` int(11) DEFAULT NULL,
  `top` int(11) DEFAULT NULL,
  `status` tinyint(1) NOT NULL,
  `temperature` int(11) DEFAULT NULL,
  `humidity` decimal(13,2) DEFAULT NULL,
  `lum` int(11) DEFAULT NULL,
  PRIMARY KEY (_id),
  FOREIGN KEY (map_id) REFERENCES map(id) ON DELETE CASCADE
)

My advise would be to avoid reserved names.

answered Dec 29, 2011 at 17:35

Bjoern

16.3k4 gold badges47 silver badges50 bronze badges

Collectives™ on Stack Overflow

Error in SQL Syntax (MYSQL 5.0)

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related