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
  • 944
  • 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
  • 258,903
  • 69
  • 498
  • 492
  • 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
  • 320,036
  • 81
  • 464
  • 592
1

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
  • 159
  • 8