I need to auto_increment a field in MySQL that is not primary key
Solution 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.
Solution 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
Solution 3
Use something like:
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
user VARCHAR(32) NOT NULL,
thedate DATE NOT NULL,
UNIQUE(user,thedate)
);
If you already have the table, and just want to add a unique constraint on user+thedate, run
ALTER TABLE users ADD UNIQUE KEY user_date_idx (user, thedate);
Solution 4
Change your current primary key to be a unique key instead:
ALTER TABLE table DROP PRIMARY KEY, ADD UNIQUE KEY(username,date);
The auto_increment will function normally after that without any problems. You should also place a unique key on the auto_increment field as well, to use for your row handling:
ALTER TABLE table ADD UNIQUE KEY(id);
littleK
Updated on July 04, 2020Comments
-
littleK almost 4 years
Right now, I have a table whose primary key is an
auto_increment
field. However, I need to set the primary key asusername
,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!