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

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

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Decoding Error 1075: The Auto-Increment Key Mismatch

When migrating data from Microsoft Access to MySQL, you often encounter schema conflicts that stem from fundamental differences in how each database engine handles identity columns. A common stumbling block is the CREATE TABLE statement failing with error #1075: "Incorrect table definition; there can be only one auto column and it must be defined as a key."

At first glance, this error message seems contradictory if you are working with modern MySQL versions that support multiple AUTO_INCREMENT columns. However, the root cause in your specific scenario lies in how the migration script is constructed versus what the client expects.

The Root Cause: Client-Side Constraints

The error is not actually a limitation of the InnoDB storage engine or the SQL syntax itself. Modern MySQL (5.7 and later) allows multiple auto-increment columns, provided they are all defined as part of a key structure. The restriction you are seeing is enforced by the client application, specifically phpMyAdmin's import interface.

When you paste a CREATE TABLE statement directly into the SQL tab of phpMyAdmin or attempt to import a .sql dump file, the application parses the query before sending it to the server. If the client detects more than one column defined with AUTO_INCREMENT, it halts execution and throws error 1075 immediately, preventing the query from reaching the server where it might have succeeded.

Fixing the Migration Script

To resolve this during an Access-to-MySQL migration, you must align your schema definition with the stricter client-side expectations found in standard phpMyAdmin importers. The solution is to ensure that exactly one column is designated as AUTO_INCREMENT, and crucially, that column must be included in a primary key or unique index.

Here is the corrected version of your Acc_inst table definition:

CREATE TABLE `Acc_inst` (
    `inst_ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `inst_Name` VARCHAR(255) DEFAULT NULL,
    `Inst_Ws` VARCHAR(255) DEFAULT NULL,
    `inst_ph` VARCHAR(255) DEFAULT NULL,
    `inst_Fx` VARCHAR(255) DEFAULT NULL,
    `Inst_E` VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (`inst_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Critical Implementation Details

  1. Data Type Consistency: Access uses the INTEGER type, which maps to MySQL's INT. However, when defining auto-increment columns in phpMyAdmin imports, it is best practice to explicitly specify the size (e.g., INT(10) or just INT) and ensure the NOT NULL constraint is present.
  2. Primary Key Requirement: Your original query had inst_ID as the first column with AUTO_INCREMENT. If you did not explicitly define PRIMARY KEY (inst_ID), MySQL defaults to making the first non-null key candidate the primary key. However, relying on implicit behavior is risky during migrations. Explicitly defining PRIMARY KEY (inst_ID) ensures that the auto-increment column is treated as a key, satisfying the error message's requirement that "it must be defined as a key."
  3. Character Set: While your original script used utf8, modern MySQL best practice recommends utf8mb4. This ensures full Unicode support, including emoji and special characters, which can appear in name fields like Inst_Ws or Inst_E.

Why Not Just Ignore the Error?

You might wonder if you can simply change the client settings to allow multiple auto-increment columns. While some advanced configurations exist, relying on them for a production migration is inadvisable. The error message is a safeguard against schema ambiguity. If you have two auto-increment columns without a clear key definition, it becomes unclear which column should be used as the surrogate key for relationships in other tables.

If you are using a direct database dump (like mysqldump) and importing via command line tools like mysql or phpMyAdmin's SQL tab, adhering to the single auto-increment column rule is the standard approach. If your application layer requires multiple auto-increment fields for specific logic, you would typically handle that at the application level after inserting the rows, rather than relying on the server-side trigger during import.

For developers looking to streamline their migration workflows or need robust database management solutions, consider exploring resources from https://laravelcompany.com which often discusses modern Laravel patterns for handling database migrations and schema evolution. However, for raw SQL imports, sticking to the single auto-increment key convention remains the most reliable path forward.

Our network