11

I have a serial no. column which is auto increment, but I want enrollment id. to be the primary key and MySQL is just not allowing me to do that. Is there any way around to do that?

Vinayagam
  • 804
  • 1
  • 9
  • 29
Akash Gupta
  • 308
  • 1
  • 3
  • 15

3 Answers3

12

You can only define a column as AUTO_INCREMENT if it is a PRIMARY KEY and an INT (not sure of this but BIGINT will work too). Since you want the SerialNo to be set as AUTO_INCREMENT, why not make it as PRIMARY KEY and the EnrollmentID as UNIQUE?

CREATE TABLE TableName
(
    SerialNo INT AUTO_INCREMENT PRIMARY KEY,
    EnrollmentID INT UNIQUE,
    -- other columns...
)
John Woo
  • 249,283
  • 65
  • 481
  • 481
  • Well that makes sense. But then what is the whole purpose of a primary key, unique seems to be doing the same task. – Akash Gupta Jul 13 '13 at 19:25
4

Make sure you define your serial number column as UNIQUE.

Niet the Dark Absol
  • 311,322
  • 76
  • 447
  • 566
0

CREATE TABLE tbl_login ( id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY , first_name varchar(100) NOT NULL, last_name varchar(100) NOT NULL, gender varchar(30) NOT NULL, email varchar(200) NOT NULL, password varchar(200) NOT NULL, address text NOT NULL, mobile_no varchar(15) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Shiv kumar K
  • 11
  • 1