MySQL - INCORRECT TABLE DEFINITION,THERE CAN ONLY BE ONE AUTO COLUMN .......

Hi all,

Table Name : Users
user_id int not null auto_increment
name varchar(40) not null
admission_no varchar(8) not null
password varchar(20) not null
email varchar(60)not null

I want my email to be the primary key so i typed this on mysql;

Create table users(user_id int not null auto_increment,name varchar(40) not null,admission_no varchar(8) not null,password varchar(20) not null,email varchar(60) not null,Primary Key(email));

Apparently , it showed this instead : Incorrect table definition, there can only be one auto column and it must be defined as a key.

How do i solve this. Really need help on this.

Try removing auto increment option for user id

But i need the auto increment though..

Try this:

Create table users(user_id int not null auto_increment,name varchar(40) not null,admission_no varchar(8) not null,
password varchar(20) not null,email varchar(60) not null,Primary Key(user_id,email));

Does it work?

.

the above code works but you will have 2 primary key not sure if it's going to be a problem or not with your application but you have what you want: email as PK

.