Resolving MySQL Error 1075: Best Practices for Auto Increment and Primary Key Configuration

Problem Background and Error Analysis

In MySQL database design, the configuration relationship between auto increment columns (AUTO_INCREMENT) and primary keys (PRIMARY KEY) often causes confusion. A typical error scenario occurs when developers attempt to create a table containing both an auto increment ID and a business primary key, but encounter Error 1075 during CREATE TABLE execution, indicating “Incorrect table definition; there can be only one auto column and it must be defined as a key”.

The root cause of this error lies in MySQL’s constraint rules: any column defined as AUTO_INCREMENT must be indexed (defined as a key). In the initial problem case, the table structure was defined as:

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;

Although the id column was correctly set as an auto increment primary key, business requirements demanded using memberid as the primary query identifier. When attempting to change the PRIMARY KEY to memberid, the id column lost its key definition, violating the rule that auto increment columns must be indexed, thus triggering Error 1075.

Core Solution

According to MySQL official documentation and best practices, the key to solving this problem is ensuring the auto increment column has appropriate indexing. Here is the verified effective solution:

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

In this optimized table structure:

• memberid is set as the primary key, meeting business query requirements
• The id column maintains auto increment functionality while being indexed via KEY (id)
• All column definitions comply with MySQL constraint requirements

This configuration ensures efficient query performance using memberid as the business primary key while preserving the visual convenience and potential foreign key association value of the auto increment id column.

Technical Principle Deep Dive

MySQL’s requirement for auto increment columns to be indexed relates to the underlying storage engine implementation mechanisms. In the MyISAM storage engine:

• Auto increment counters are tightly coupled with index structures
• Indexes provide fast access and update paths for auto increment values
• Lack of indexing causes severe performance degradation in auto increment operations

The reference article case further confirms the universality of this constraint. During the import of the wp_commentmeta table, a similar table structure definition:

CREATE TABLE IF NOT EXISTS `wp_commentmeta` (
`meta_id` bigint(20) unsigned NOT NULL auto_increment,
`comment_id` bigint(20) unsigned NOT NULL default '0',
`meta_key` varchar(255) default NULL,
`meta_value` longtext
) ENGINE=MyISAM AUTO_INCREMENT=2610 DEFAULT CHARSET=utf8;

Also triggered Error 1075 due to the meta_id auto increment column lacking key definition. This demonstrates the consistent application of this constraint across different table structures.

Alternative Approaches and Comparison

Beyond the direct table creation solution, implementation can also be achieved through ALTER TABLE statements in steps:

-- Step 1: 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;

-- Step 2: Add index to id column
ALTER TABLE `members` ADD INDEX(`id`);

-- Step 3: Set id column as auto increment
ALTER TABLE `members` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;

This step-by-step approach offers advantages including:

• Clearer demonstration of constraint addition process
• Easier modification of existing table structures
• Suitability for automation scripts and migration scenarios

However, from performance and simplicity perspectives, the single CREATE TABLE statement remains the preferred solution.

Performance Optimization Recommendations

In high-performance application scenarios, additional factors should be considered:

• Index Selection: Using UNIQUE KEY instead of ordinary KEY for the id column prevents potential duplicate value issues
• Storage Engine Characteristics: MyISAM’s table-level locking mechanism may become a concurrency bottleneck; consider InnoDB for high-concurrency environments
• Data Type Optimization Adjust VARCHAR(30) length for memberid based on actual business needs to avoid space waste
• Query Pattern Matching: Ensure all memberid-based queries effectively utilize the primary key index

Through reasonable table structure design and indexing strategies, optimal database performance can be achieved while maintaining clear business logic.