Introduction

In MySQL, the message “there can be only one auto column” usually refers to the rule around AUTO_INCREMENT: a table can have only one such column, and that column must be indexed. The fix is normally to redesign the key strategy rather than trying to force several independent auto-generated counters into the same table.

What the Error Actually Means

A typical failing table definition looks like this:

1CREATE TABLE orders (
2    id INT AUTO_INCREMENT,
3    ticket_no INT AUTO_INCREMENT,
4    customer_name VARCHAR(100),
5    PRIMARY KEY (id)
6);

MySQL rejects this because only one AUTO_INCREMENT column is allowed per table.

Another common failure is this:

1CREATE TABLE orders (
2    id INT AUTO_INCREMENT,
3    customer_name VARCHAR(100)
4);

That fails for a different but related reason: the auto-increment column must be part of a key.

So the rule has two parts:

• only one AUTO_INCREMENT column per table
• that column must be indexed

The Normal Correct Pattern

Most tables only need one generated surrogate key:

1CREATE TABLE orders (
2    id INT NOT NULL AUTO_INCREMENT,
3    customer_name VARCHAR(100) NOT NULL,
4    created_at DATETIME NOT NULL,
5    PRIMARY KEY (id)
6);

This is the common design because:

• it gives each row a unique identifier
• inserts remain simple
• joins can use a stable numeric key

If you need more than one unique field, the others should usually be regular columns with UNIQUE constraints, not extra auto-increment counters.

If You Need Another Unique Number

Suppose you want both:

• an internal row ID
• a public-facing ticket number

You still do not get two auto columns in one table. Better options include:

• keep one AUTO_INCREMENT column and derive the public identifier separately
• use a UNIQUE column populated by application logic
• use another table dedicated to sequence generation
• use UUID-style identifiers when sequential numbering is not required

Example with one auto key and one unique business key:

1CREATE TABLE orders (
2    id INT NOT NULL AUTO_INCREMENT,
3    ticket_no VARCHAR(20) NOT NULL,
4    customer_name VARCHAR(100) NOT NULL,
5    PRIMARY KEY (id),
6    UNIQUE KEY uq_orders_ticket_no (ticket_no)
7);

Here, MySQL generates only id, and the application or service layer takes responsibility for ticket_no.

Why MySQL Requires the Auto Column to Be a Key

An AUTO_INCREMENT column is not just a value generator. MySQL uses indexing to manage and retrieve the next sequence value efficiently. That is why the column must be part of an index, typically the primary key.

For InnoDB in particular, the safest and most common design is to make the auto-increment column the primary key:

1CREATE TABLE users (
2    user_id BIGINT NOT NULL AUTO_INCREMENT,
3    email VARCHAR(255) NOT NULL,
4    PRIMARY KEY (user_id),
5    UNIQUE KEY uq_users_email (email)
6);

This matches how MySQL expects the feature to be used in ordinary table design.

Composite Keys and Special Cases

There are advanced cases involving composite indexes, but those are not the right starting point when you hit this error. Most developers encountering the message are not dealing with a special engine feature. They simply need to:

• reduce the table to one auto-generated column
• make that column part of a key

Trying to outsmart the rule is usually a sign that the table design needs simplification.

A Better Database Design Question

When this error appears, the real design question is usually:

“What is the actual primary identity of a row, and which other columns need uniqueness rather than auto-generation?”

That leads to much better schema decisions than treating AUTO_INCREMENT as a general-purpose solution for every numbered field.

Common Pitfalls

• Declaring two different AUTO_INCREMENT columns in the same table.
• Forgetting to index the auto-increment column.
• Using AUTO_INCREMENT for business identifiers that really need their own domain-specific generation logic.
• Confusing “must be unique” with “must be auto-increment.”
• Adding numeric sequence fields without first deciding which one is the actual row key.

Summary

• In MySQL, a table can have only one AUTO_INCREMENT column.
• That column must be indexed, and it is usually the primary key.
• If you need other unique identifiers, use UNIQUE constraints or generate them separately.
• Do not try to solve every numbering problem with extra auto-increment columns.
• The error is usually a schema-design prompt, not just a syntax issue.