MYSQL [USER TABLE modification brings Errors]

Question

I am trying to follow a simple lesson in web tourist. I tried to modify the code given for my needs. And it spits error with modification:

#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'), UNIQUE KEY ('email'), KEY ('email', 'pass') ) ENGINE = MYISAM DEFAULT ' at line 13

ORIG CODE:

CREATE TABLE `users` ( 
`id` int(11) NOT NULL auto_increment, 
`username` varchar(30) NOT NULL default '', 
`password` varchar(255) NOT NULL default '', 
`email` varchar(40) NOT NULL default '', 
`msn` varchar(250) NOT NULL default 'Not Specified', 
`aim` varchar(250) NOT NULL default 'Not Specified', 
`location` varchar(36) NOT NULL default 'Not Specified', 
PRIMARY KEY (`id`) 
) TYPE=MyISAM;

MY EDIT:

CREATE TABLE `users` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
type ENUM( 'member' , 'admin' ) NOT NULL ,
`username` varchar(30) NOT NULL default '',
`password` varchar(255) NOT NULL default '',
`first_name` varchar (15) NOT NULL,
`last_name` varchar (30) NOT NULL,
`gender` ENUM('male',  'female') NOT NULL default 'male',
`email` varchar(50) NOT NULL default '',
`skype` varchar(50) NOT NULL default 'Not Specified',
`facebook` varchar(150) NOT NULL default 'Not Specified',
`location` varchar(100) NOT NULL default 'Not Specified',
PRIMARY KEY ('id'),
UNIQUE KEY ('email'),
KEY ('email', 'pass')
) ENGINE = MYISAM DEFAULT CHARSET = utf8;

Perception · Accepted Answer · 2013-04-14 17:28:34Z

2

You are mixing up back-ticks and quotes. This is the general rule to live by: back-ticks are used to enclose table structure (table names, table columns, constraint names etc). Quotation marks are used to enclose row values - aka, the actual data that is being entered into the table. You won't use quotation marks in a DDL statement unless you are specifying default values.

Your error is on primary, unique and index keys in your statement.

PRIMARY KEY ('id'),
UNIQUE KEY ('email'),
KEY ('email', 'pass')

Should be (notice the back-ticks):

PRIMARY KEY (`id`),
UNIQUE KEY (`email`),
KEY (`email`, `password`)

Note also that your index is referring to a non-existent column pass - I've changed it to the correct column password.

answered Apr 14, 2013 at 17:28

Perception

80.8k19 gold badges190 silver badges197 bronze badges

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

Comments

Tony Hopkinson · Accepted Answer · 2013-04-14 11:29:17Z

1

You gone mad with the backticks

default '',

Two single quotes is empty string BackTicks are for column names and such e.g.

My Strange column name

answered Apr 14, 2013 at 11:29

Tony Hopkinson

20.4k3 gold badges35 silver badges40 bronze badges

2 Comments

Borislav Jordanov Over a year ago

Edited. Please check the description again!

Tony Hopkinson Over a year ago

So you fixed the backticks round the defaults, but you now have single quotes round the field names isn your keys as mentioned by @Perception. Strings are single quotes, objects (columns , tables etc) are backticks.

Collectives™ on Stack Overflow

MYSQL [USER TABLE modification brings Errors]

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related