Create Table Syntax Error

Question

I received a MySQL data dump and am trying to insert the data into a set of temporary tables. The creation statement for the first table is shown below. When I run this I receive 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 ''temp_books'( 'ID'int( 11 ) NOT NULL AUTO_INCREMENT, 'start'varchar( 20 ) ' at line 1". I've checked the documentation for MySQL syntax, and I don't see that the problem is.

CREATE TABLE 'temp_books' (
  'ID' int(11) NOT NULL AUTO_INCREMENT,
  'start' varchar(20) NOT NULL,
  'customer_id' int(11) NOT NULL DEFAULT '0',
  'total_num' int(11) NOT NULL,
  'amount' double(5,2) NOT NULL DEFAULT '0.00',
  'changed' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY ('ID'),
  UNIQUE KEY 'start' ('start')
) ENGINE=MyISAM AUTO_INCREMENT=4853 DEFAULT CHARSET=latin1;

Turnkey · Accepted Answer · 2009-01-18 03:24:05Z

4

You shouldn't put single-quotes on your identifiers. If you're going to quote them use the "back tick" character (“`”). You can also use double-quotes but you have to specify that mode:

SET sql_mode='ANSI_QUOTES';

http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

edited Jan 18, 2009 at 3:24

answered Jan 18, 2009 at 3:09

Turnkey

9,4463 gold badges30 silver badges36 bronze badges

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

Comments

Sampson · Accepted Answer · 2009-01-18 02:53:44Z

2

I've ALWAYS had issues with CREATE TABLE. Not sure why. Takes some trial-and-error.

Try this:

CREATE TABLE temp_books (
  ID int(11) NOT NULL AUTO_INCREMENT,
  start varchar(20) NOT NULL,
  customer_id int(11) NOT NULL DEFAULT '0',
  total_num int(11) NOT NULL,
  amount double(5,2) NOT NULL DEFAULT '0.00',
  changed timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (ID),
  UNIQUE KEY start (start)
) ENGINE=MyISAM AUTO_INCREMENT=4853 DEFAULT CHARSET=latin1;

answered Jan 18, 2009 at 2:53

Sampson

269k76 gold badges546 silver badges570 bronze badges

2 Comments

Federico A. Ramponi Over a year ago

Does MySQL allow something like int(11)?

Sampson Over a year ago

Yes. But I think it converts the declaration to an unsigned integer.

Elie · Accepted Answer · 2009-01-18 03:06:47Z

0

I had to delete the quote marks, as well as the default for the changed field, as well as the default charset. Hopefully that won't affect the data.

answered Jan 18, 2009 at 3:06

Elie

13.9k27 gold badges80 silver badges128 bronze badges

Comments

MJL · Accepted Answer · 2020-04-18 10:43:16Z

0

Here's another way of writing it that might work for some: (left away most of the columns for brevity)

create table temp_books
(
id int not null,
start varchar(255) null,
constraint six_cb_datasource_pk
    primary key (id)
);

answered Apr 18, 2020 at 10:43

MJL

4005 silver badges11 bronze badges

Collectives™ on Stack Overflow

Create Table Syntax Error

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related