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

mysql primary-key auto-increment
littleK picture littleK · Jul 20, 2009 · Viewed 41.3k times · Source

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

Artem Russakovskii picture Artem Russakovskii · Jul 20, 2009

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.

Related questions

make an ID in a mysql table auto_increment (after the fact)

I acquired a database from another developer. He didn't use auto_incrementers on any tables. They all have primary key ID's, but he did all the incrementing manually, in code. Can I turn those into Auto_incrementers now? Wow, very …

Read More
Auto Increment after delete in MySQL

I have a MySQL table with a primary key field that has AUTO_INCREMENT on. After reading other posts on here I've noticed people with the same problem and with varied answers. Some recommend not using this feature, others state …

Read More
MySQL: #1075 - Incorrect table definition; autoincrement vs another key?

Here is a table in MySQL 5.3.X+ db: CREATE TABLE members` ( `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, `memberid` VARCHAR( 30 ) NOT NULL , `Time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , `firstname` VARCHAR( 50 ) NULL , `lastname` VARCHAR( 50 ) NULL , UNIQUE (memberid), PRIMARY KEY (id) ) …

Read More