🚨 MySQL Error: 'ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key' — Cause, Fix, and Troubleshooting Guide

Overview

ERROR 1075 (42000) fires during CREATE TABLE/ALTER TABLE when an AUTO_INCREMENT column is not backed by a key, or when you try to define more than one auto-increment column:

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

MySQL enforces two rules: a table may have at most one AUTO_INCREMENT column, and that column must be indexed (part of the primary key or another key). The auto-increment counter relies on that index to find the current maximum efficiently.

Symptoms

• A CREATE TABLE fails whenever you add AUTO_INCREMENT.
• Dropping a primary key from a table that has an auto-increment column fails or triggers 1075.
• Reordering columns in a composite primary key breaks a previously valid schema.

Common Root Causes

1. AUTO_INCREMENT column with no key

CREATE TABLE t (
  id INT AUTO_INCREMENT,
  name VARCHAR(50)
);

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

id is auto-increment but not declared as a key.

2. Two auto-increment columns

Only one auto column is allowed per table; a second one is rejected.

3. Dropping the key that backs the auto column

ALTER TABLE t DROP PRIMARY KEY; on a table whose PK is the auto-increment column removes the index the counter depends on.

4. Auto column not first in a composite index (engine-dependent)

In InnoDB the auto-increment column must be the leading column of some index. Putting it second in a composite key can trigger 1075.

How to diagnose

Inspect the current definition and its keys:

SHOW CREATE TABLE t\G
SHOW INDEX FROM t;

Confirm exactly one auto-increment column exists:

SELECT COLUMN_NAME, EXTRA
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 't'
  AND EXTRA LIKE '%auto_increment%';

Fixes

Give the auto-increment column a key — usually the primary key:

CREATE TABLE t (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

If it must live in a composite primary key, make it the leading column:

CREATE TABLE line_items (
  id INT AUTO_INCREMENT,
  order_id INT NOT NULL,
  PRIMARY KEY (id, order_id)
);

Or back it with a standalone key while another column owns the PK:

CREATE TABLE t (
  code CHAR(8) PRIMARY KEY,
  seq INT AUTO_INCREMENT,
  KEY (seq)
);

To drop a primary key without hitting 1075, remove the attribute first:

ALTER TABLE t MODIFY id INT NOT NULL;   -- strip AUTO_INCREMENT
ALTER TABLE t DROP PRIMARY KEY;

What to watch out for

• Only one column per table can be AUTO_INCREMENT — for a second surrogate, use a UUID or a sequence table.
• When the auto column shares a composite PK, its position matters in InnoDB; keep it first unless you have tested otherwise.
• Removing a primary key backing an auto column requires stripping AUTO_INCREMENT first, or the drop fails.
• Migration tools that reorder columns can silently reproduce this — review generated DDL before applying.

Related

• ERROR 1064: SQL syntax error — the broader DDL parse error you will hit while editing table definitions.
• ERROR 1071: Specified key was too long — another index-definition constraint that blocks CREATE TABLE.
• ERROR 1050: Table already exists — a common companion when re-running create scripts.