error when try to make unique

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Sevengraff
Forum Contributor
Posts: 232
Joined: Thu Apr 25, 2002 9:34 pm
Location: California USA
Contact:

error when try to make unique

Post 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.
MeOnTheW3
Forum Commoner
Posts: 48
Joined: Wed Nov 13, 2002 3:28 pm
Location: Calgary, AB

Post 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)
)
User avatar
Sevengraff
Forum Contributor
Posts: 232
Joined: Thu Apr 25, 2002 9:34 pm
Location: California USA
Contact:

Post by Sevengraff »

*takes notes*
oh, i didnt know that. thanks, now it works.
Post Reply