Page 1 of 1

error when try to make unique

Posted: Fri Nov 15, 2002 10:51 pm
by Sevengraff

Code: Select all

CREATE TABLE users(

id int not null auto_increment,
user varchar( 15 ) not null ,
pass varchar( 15 ) not null ,
bday date,
email varchar( 50 ) not null ,
bio mediumtext,
site varchar( 50 ) ,
color int not null ,
primary key ( id ) 
unique (user)
)
when i do that in PhpMyAdmin, it tells me there is an error near 'unique (user) )'
im new at MySQL, and i dont see what the problem is.

Posted: Sat Nov 16, 2002 12:08 am
by MeOnTheW3
An auto_increment field must be of one and defined as the key, mySQL is erroring due to the sturcture being stated with an auto but with no primary, even though, yes, you do have the primary defined later in the query.

Make sence? Simply, a declaration of primary key must accompany the auto_increment declaration, and there can only ever be one auto_increment declaration per table.

CREATE TABLE users(
id int not null auto_increment primary key ,
user varchar( 15 ) not null ,
pass varchar( 15 ) not null ,
bday date,
email varchar( 50 ) not null ,
bio mediumtext,
site varchar( 50 ) ,
color int not null ,
unique (user)
)

Posted: Sat Nov 16, 2002 1:47 pm
by Sevengraff
*takes notes*
oh, i didnt know that. thanks, now it works.