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

Question

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)  ) ENGINE = MYISAM; 

Id column is never used in queries, it is just for visual convenience (so it's easy to see how the table grows). Memberid is an actual key, is unique, and memberid is used in queries to identify any member (WHERE memberid='abcde').

My question is: how to keep auto_increment, but make memberid as a primary key? Is that possible? When I try to create this table with PRIMARY KEY (memberid), I get an error:

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

What is the best choice (Hopefully, there is a way to keep id column so performance is good and queries identify any user by memberid, not by id), if the performance is very important (although the disk space is not)?

Answer 1

You can have an auto-Incrementing column that is not the PRIMARY KEY, as long as there is an index (key) on it:

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 ,    PRIMARY KEY (memberid) ,   KEY (id)                          --- or:    UNIQUE KEY (id) ) ENGINE = MYISAM;  

Answer 2

First create table without auto_increment,

CREATE TABLE `members`(     `id` int(11) NOT NULL,     `memberid` VARCHAR( 30 ) NOT NULL ,     `Time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,     `firstname` VARCHAR( 50 ) NULL ,     `lastname` VARCHAR( 50 ) NULL     PRIMARY KEY (memberid)  ) ENGINE = MYISAM; 

after set id as index,

ALTER TABLE `members` ADD INDEX(`id`); 

after set id as auto_increment,

ALTER TABLE `members` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT; 

---

Or

CREATE TABLE IF NOT EXISTS `members` (     `id` int(11) NOT NULL,     `memberid` VARCHAR( 30 ) NOT NULL ,     `Time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,     `firstname` VARCHAR( 50 ) NULL ,     `lastname` VARCHAR( 50 ) NULL,       PRIMARY KEY (`memberid`),       KEY `id` (`id`) ) ENGINE=MYISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;