AUTO_INCREMENT column defined without PRIMARY KEY or UNIQUE KEY constraint Multiple AUTO_INCREMENT columns defined in the same table AUTO_INCREMENT column is not the leftmost column in a compound primary key (InnoDB only)

Is the "1075" error serious?

Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.

How to fix ERROR 1075: Incorrect table definition; only one auto column in MySQL

MySQLBEGINNERHIGHUpdated Dec 22, 2025· 267 views

This MySQL error occurs when you define an AUTO_INCREMENT column without a key constraint, or attempt to create multiple AUTO_INCREMENT columns. Fix it by adding PRIMARY KEY to your AUTO_INCREMENT column or ensuring only one auto column exists per table.

Quick fix

Fixes 1075

-- FAILS with ERROR 1075:
CREATE TABLE users (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(100),
  email VARCHAR(100)
);

-- WORKS: Add PRIMARY KEY
CREATE TABLE users (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

Add PRIMARY KEY to the AUTO_INCREMENT column
For compound primary keys, put AUTO_INCREMENT column first (InnoDB)
Check for and remove duplicate AUTO_INCREMENT columns
Use UNIQUE KEY instead of PRIMARY KEY if needed

mysql — error

$-- FAILS with ERROR 1075:
CREATE TABLE users (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(100),
  email VARCHAR(100)
);
-- WORKS: Add PRIMARY KEY
CREATE TABLE users (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);
ERROR 1075: Incorrect table definition; only one auto column

What this error means

MySQL Error 1075 (SQLSTATE 42000) enforces a critical table design rule: every table can have at most one AUTO_INCREMENT column, and that column must be defined as a key (PRIMARY KEY, UNIQUE KEY, or indexed column). When you violate this rule by defining AUTO_INCREMENT on a non-indexed column or creating multiple AUTO_INCREMENT columns, MySQL rejects the table definition during CREATE TABLE or ALTER TABLE operations. This is a fundamental constraint that ensures MySQL can reliably generate and track unique sequence numbers. The error message is strict because allowing multiple AUTO_INCREMENT columns or non-indexed auto columns would break MySQL's ability to enforce uniqueness.

Typical symptoms

CREATE TABLE statement fails with "ERROR 1075" immediately
ALTER TABLE ADD COLUMN with AUTO_INCREMENT fails with this error
Error appears during database migration or schema setup
Code that worked in MyISAM fails when switching to InnoDB
Compound primary key with AUTO_INCREMENT is rejected by InnoDB

Common causes

AUTO_INCREMENT column defined without PRIMARY KEY or UNIQUE KEY constraint
Multiple AUTO_INCREMENT columns defined in the same table
AUTO_INCREMENT column is not the leftmost column in a compound primary key (InnoDB only)
ALTER TABLE attempting to add AUTO_INCREMENT to an existing non-key column
Using InnoDB engine with compound keys where AUTO_INCREMENT is not first

How to fix "ERROR 1075: Incorrect table definition; only one auto column"

1Add PRIMARY KEY to the AUTO_INCREMENT column

The simplest solution is to define PRIMARY KEY inline with the AUTO_INCREMENT column:

sql

-- FAILS with ERROR 1075:
CREATE TABLE users (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(100),
  email VARCHAR(100)
);

-- WORKS: Add PRIMARY KEY
CREATE TABLE users (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

The PRIMARY KEY constraint:
- Ensures the AUTO_INCREMENT column is indexed
- Guarantees uniqueness of generated sequence numbers
- Satisfies MySQL's requirement that auto columns must be keys

2For compound primary keys, put AUTO_INCREMENT column first (InnoDB)

If you need a compound primary key with InnoDB, the AUTO_INCREMENT column must be the leftmost (first) column:

sql

-- FAILS with InnoDB (ERROR 1075):
CREATE TABLE order_items (
  order_id INT NOT NULL,
  id INT NOT NULL AUTO_INCREMENT,
  product_id INT NOT NULL,
  quantity INT,
  PRIMARY KEY(order_id, id)  -- AUTO_INCREMENT not first
);

-- WORKS: AUTO_INCREMENT column first
CREATE TABLE order_items (
  id INT NOT NULL AUTO_INCREMENT,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT,
  PRIMARY KEY(id, order_id)  -- AUTO_INCREMENT is first
);

Why this matters for InnoDB:
- InnoDB optimizes AUTO_INCREMENT lookups by using the leftmost column
- If AUTO_INCREMENT is not first, InnoDB cannot efficiently generate the next sequence value
- MyISAM is more flexible here, but InnoDB enforces this rule strictly

3Check for and remove duplicate AUTO_INCREMENT columns

If you're modifying an existing table, verify only one column has AUTO_INCREMENT:

sql

-- View all columns and their attributes
DESC your_table_name;

-- Check if multiple columns have AUTO_INCREMENT
SELECT COLUMN_NAME, EXTRA FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your_table_name'
  AND EXTRA LIKE '%auto_increment%';

If multiple columns show AUTO_INCREMENT, remove it from all but one:

sql

-- Remove AUTO_INCREMENT from the secondary column
ALTER TABLE your_table_name
MODIFY secondary_col INT NOT NULL;

-- Keep it on the primary column
ALTER TABLE your_table_name
MODIFY primary_col INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

4Use UNIQUE KEY instead of PRIMARY KEY if needed

If you already have a different PRIMARY KEY, you can use UNIQUE KEY for the AUTO_INCREMENT column:

sql

-- Table with natural primary key and separate auto column
CREATE TABLE products (
  sku VARCHAR(50) PRIMARY KEY,
  id INT NOT NULL AUTO_INCREMENT UNIQUE KEY,
  name VARCHAR(100),
  price DECIMAL(10,2)
);

The AUTO_INCREMENT column still needs to be indexed (PRIMARY KEY, UNIQUE KEY, or regular INDEX), but it doesn't have to be the primary key. However, PRIMARY KEY is most common because:
- It's slightly more efficient
- It signals the auto-generated identifier clearly
- It's the typical pattern in most applications

5If using MyISAM, you have more flexibility with compound keys

MyISAM allows AUTO_INCREMENT on secondary columns in compound keys, but this is rarely recommended:

sql

-- Works in MyISAM but NOT in InnoDB:
CREATE TABLE logs (
  category ENUM('error', 'warning', 'info') NOT NULL,
  id INT NOT NULL AUTO_INCREMENT,
  message TEXT,
  PRIMARY KEY(category, id)  -- AUTO_INCREMENT is NOT first
) ENGINE=MyISAM;

Important considerations:
- This generates a separate sequence per category value
- Most new projects use InnoDB (MySQL 5.7+), which doesn't support this
- Modern best practice is to make AUTO_INCREMENT the leftmost/only indexed column
- If you need per-group sequences, use application-level logic instead

Advanced notes

Storage Engine Differences: InnoDB and MyISAM have different AUTO_INCREMENT rules. InnoDB requires the AUTO_INCREMENT column to be the first column in an index (or the only column), while MyISAM allows it to be a secondary column in a compound index. When migrating from MyISAM to InnoDB, you may encounter this error if your schema uses MyISAM-style compound keys.

Index Selection: When multiple indexes include the AUTO_INCREMENT column, MySQL chooses the index that starts with the AUTO_INCREMENT column for generating sequence values. For example, if you have both PRIMARY KEY(id) and INDEX(id, status), MySQL uses the PRIMARY KEY since it's listed first. This matters for efficiency in high-volume insert scenarios.

Integer Overflow: Remember that AUTO_INCREMENT uses integer data types. INT supports up to 2 billion unique values, INT UNSIGNED supports ~4 billion, and BIGINT UNSIGNED supports ~18 quintillion. Choose the appropriate type to avoid sequence exhaustion in long-running systems. If you hit the integer limit, inserts will fail until you expand the column type.

Replication Considerations: In MySQL replication, AUTO_INCREMENT behavior can be tricky with multi-source replication or circular topologies. The auto_increment_increment and auto_increment_offset variables control this, but the column definition must still satisfy the basic rules outlined above.

Frequently asked questions

What does "1075" mean?: MySQL Error 1075 (SQLSTATE 42000) enforces a critical table design rule: every table can have at most one AUTO_INCREMENT column, and that column must be defined as a key (PRIMARY KEY, UNIQUE KEY, or indexed column). When you violate this rule by defining AUTO_INCREMENT on a non-indexed column or creating multiple AUTO_INCREMENT columns, MySQL rejects the table definition during CREATE TABLE or ALTER TABLE operations. This is a fundamental constraint that ensures MySQL can reliably generate and track unique sequence numbers.
How do I fix "1075"?: This MySQL error occurs when you define an AUTO_INCREMENT column without a key constraint, or attempt to create multiple AUTO_INCREMENT columns. Fix it by adding PRIMARY KEY to your AUTO_INCREMENT column or ensuring only one auto column exists per table.
What causes "1075"?: AUTO_INCREMENT column defined without PRIMARY KEY or UNIQUE KEY constraint Multiple AUTO_INCREMENT columns defined in the same table AUTO_INCREMENT column is not the leftmost column in a compound primary key (InnoDB only)
Is the "1075" error serious?: Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.

Official resources & further reading

📚MySQL Official Documentation
📖
MySQL 8.4 Reference Manual: Using AUTO_INCREMENT(Official Docs)
📖
MySQL 8.4 Reference Manual: AUTO_INCREMENT Handling in InnoDB(Official Docs)
📖
MariaDB Knowledge Base: Error 1075(Official Docs)
MySQL Bugs: #60104 - Compound Primary Key with AUTO_INCREMENT
GetGalaxy: MySQL Error 1075 - How to Fix and Prevent