SQL syntax error

Question

CREATE TABLE  `users` (

 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
 `type` ENUM(  `member` ,  `admin` ) NOT NULL ,
 `username` VARCHAR( 30 ) NOT NULL ,
 `email` VARCHAR( 80 ) NOT NULL ,
 `pass` VARBINARY( 32 ) NOT NULL , 
 `first_name` VARCHAR( 20 ) NOT NULL , 
 `last_name` VARCHAR( 40 ) NOT NULL , 
 `date_expires` DATE NOT NULL , 
 `date_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
 `date_modified` TIMESTAMP NOT NULL DEFAULT `0000-00-00 00:00:00` ,
PRIMARY KEY (  `id` ) , 
UNIQUE KEY  `username` (  `username` ) ,
UNIQUE KEY  `email` (  `email` ) 
) ENGINE = MYISAM DEFAULT CHARSET = utf8;

MySQL said:

#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 'member,admin) NOT NULL, username VARCHAR(30) NOT NULL, email VARCHAR(80)' at line 3

The shared server I am using, uses 4.4. Thanks for reading. I am an absolute novice, having only been learning php/mysql for one month, so please speak in layman's terms.

Don't use markup (eg <br/>) when posting. Instead use the code section toggle in the edit toolbar. — Jon Egerton
– Jon Egerton, Commented Feb 27, 2012 at 16:36
@Paul Bellora: that's SO's formatting getting confused by # character — Mchl
– Mchl, Commented Feb 27, 2012 at 16:40
@Mchl - I meant that his last syntax error was using single-quote for table/column names, and it looks like he switched to back-ticks for everything, including enums. — Paul Bellora
– Paul Bellora, Commented Feb 27, 2012 at 16:50

Mchl · Accepted Answer · 2012-02-27 16:35:59Z

5

You need to quote ENUM values

type ENUM( 'member' , 'admin' ) NOT NULL ,

not backtick them as you do now

Same thing for this line

`date_modified` TIMESTAMP NOT NULL DEFAULT `0000-00-00 00:00:00`

should be

`date_modified` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'

answered Feb 27, 2012 at 16:35

Mchl

62.4k9 gold badges121 silver badges121 bronze badges

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

3 Comments

Matthew Pollard Over a year ago

Thank-you @Anthony Grist & @Mchl . This may be an incredibly stupid question, but why do you use ' rather than ` here?

Mchl Over a year ago

Because backticks ` are for denoting database objects like tables and columns, while ' is for delimiting string values.

Lightness Races in Orbit Over a year ago

@Matthew: Please read the manual; all of this information is there (dev.mysql.com/doc/refman/5.0/en/string-literals.html & dev.mysql.com/doc/refman/5.0/en/identifiers.html).

Anthony Grist · Accepted Answer · 2012-02-27 16:37:06Z

1

This page indicates that your ENUM values should be strings, so need to be surrounded by single quotes.

type ENUM( 'member' , 'admin' ) NOT NULL

answered Feb 27, 2012 at 16:37

Anthony Grist

38.4k8 gold badges64 silver badges76 bronze badges

Comments

Borislav Jordanov · Accepted Answer · 2013-04-14 12:15:12Z

0

okey fixed it here is it :)

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`, `password`)
) ENGINE = MYISAM DEFAULT CHARSET = utf8;

answered Apr 14, 2013 at 12:15

Borislav Jordanov

12 bronze badges

Collectives™ on Stack Overflow

SQL syntax error

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related