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

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

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

Solution 3:

You can make the id the primary key, and set member_id to NOT NULL UNIQUE. (Which you've done.) Columns that are NOT NULL UNIQUE can be the target of foreign key references, just like a primary key can. (I'm pretty sure that's true of all SQL platforms.)

At the conceptual level, there's no difference between PRIMARY KEY and NOT NULL UNIQUE. At the physical level, this is a MySQL issue; other SQL platforms will let you use a sequence without making it the primary key.

But if performance is really important, you should think twice about widening your table by four bytes per row for that tiny visual convenience. In addition, if you switch to INNODB in order to enforce foreign key constraints, MySQL will use your primary key in a clustered index. Since you're not using your primary key, I imagine that could hurt performance.

Solution 4:

I think i understand what the reason of your error. First you click auto AUTO INCREMENT field then select it as a primary key.

The Right way is First You have to select it as a primary key then you have to click auto AUTO INCREMENT field.

Very easy. Thanks