Cant Create SQL Table [SQL]

Question

Im not sure what is wrong with my create table here. Keeps giving me error

ERROR 1064 (42000): 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 'character
(
character_id VarChar(15) PRIMARY KEY,
name VarChar(30) NOT NULL,
sex' at line 1

I dont see any errors in it?

CREATE TABLE character
(
character_id VarChar(15) PRIMARY KEY,
name VarChar(30) NOT NULL,
sex VarChar(7) NOT NULL,
age INT NOT NULL,
hair VarChar(10) NOT NULL,
eye VarChar(10) NOT NULL,
skin VarChar(12) NOT NULL,
body VarChar(12) NOT NULL,
from VarChar(20) NOT NULL
) engine=innobd;

Marc B · Accepted Answer · 2012-03-05 17:50:33Z

3

character and from are reserved words in MySQL: http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

To use a reserved word as a field/table identifier, you'll have to quote it with backticks:

create table `character` (
...
body VarChar(12) NOT NULL,
`from` VarChar(20) NOT NULL
...

answered Mar 5, 2012 at 17:50

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

1 Comment

John Ayers Over a year ago

Thank you. I knew that as soon as you said I just thought fail.

christophmccann · Accepted Answer · 2012-03-05 17:51:01Z

0

Both from and character are special words reserved by the database vendor. Try, for example, naming a column "limit" and you will get the same result. Re-name the table and the column from and your problem will go away.

answered Mar 5, 2012 at 17:51

christophmccann

4,2218 gold badges44 silver badges66 bronze badges

Comments

Pedro Teran · Accepted Answer · 2012-03-05 17:53:31Z

0

CREATE TABLE 'character'
(
character_id VarChar(15) PRIMARY KEY,
name VarChar(30) NOT NULL,
sex VarChar(7) NOT NULL,
age INT NOT NULL,
hair VarChar(10) NOT NULL,
eye VarChar(10) NOT NULL,
skin VarChar(12) NOT NULL,
body VarChar(12) NOT NULL,
from VarChar(20) NOT NULL
) engine=innobd;

you're trying to use a reserved word so you need to add backtick to the table name, My suggestion is to change the table name, and avoid using reserved words

answered Mar 5, 2012 at 17:53

Pedro Teran

1,2003 gold badges18 silver badges43 bronze badges

Comments

Matteo Melani · Accepted Answer · 2012-03-05 17:57:17Z

0

Two problems:

The table name "character" should be characters, character is a reserved word
The field name "from" should be different than the SQL keyword FROM

This should work:

CREATE TABLE characters
(
character_id VarChar(15) PRIMARY KEY,
name VarChar(30) NOT NULL,
sex VarChar(7) NOT NULL,
age INT NOT NULL,
hair VarChar(10) NOT NULL,
eye VarChar(10) NOT NULL,
skin VarChar(12) NOT NULL,
body VarChar(12) NOT NULL,
fromX VarChar(20) NOT NULL
) engine=innobd;

Hope this help.

answered Mar 5, 2012 at 17:57

Matteo Melani

2,7362 gold badges25 silver badges30 bronze badges

Collectives™ on Stack Overflow

Cant Create SQL Table [SQL]

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related