I need to auto_increment a field in MySQL that is not primary key

Question

Right now, I have a table whose primary key is an auto_increment field. However, I need to set the primary key as username, date (to ensure that there cannot be a duplicate username with a date).

I need the auto_increment field, however, in order to make changes to row information (adding and deleting).

What is normally done with this situation?

Thanks!

Answer 1

Just set a unique index on composite of (username, date).

ALTER TABLE `table` ADD UNIQUE INDEX `name` (`username`, `date`); 

Alternatively, you can try to

ALTER TABLE `table` DROP PRIMARY KEY, ADD PRIMARY KEY(`username`,`date`); 

and I think in the latter case you need those columns to be declared NOT NULL.

Answer 2

I know this is old question, here is how i solved the problem -

ALTER TABLE `student_info` ADD `sn` INT(3) UNIQUE NOT NULL AUTO_INCREMENT FIRST