#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

Question

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?

Answer 1

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...
)

Answer 2

Make sure you define your serial number column as UNIQUE.

Answer 3

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;