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

Question

Hey everyone,

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

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

Answer 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);