How to Fix MySQL Error 1075: Incorrect Table Definition — Auto Column Must Be Indexed

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key is raised when a CREATE TABLE or ALTER TABLE statement includes an AUTO_INCREMENT column that is not part of a key, or defines more than one AUTO_INCREMENT column. The error symbol is ER_WRONG_AUTO_KEY.

Impact

This error occurs at DDL execution time — the CREATE TABLE or ALTER TABLE statement fails and no table is created or modified. No data is affected. You will encounter this when bootstrapping a schema, running migrations, or applying ORM-generated DDL that has a misconfigured AUTO_INCREMENT attribute.

ORMs that auto-generate DDL (Hibernate, SQLAlchemy, ActiveRecord, Django ORM, Sequelize) will surface this as a migration failure or startup error. The exact message is typically wrapped inside a OperationalError, ProgrammingError, or similar database exception depending on the driver.

Common Causes

1. AUTO_INCREMENT column not defined as a key. MySQL requires that any column using AUTO_INCREMENT be part of an index — at minimum a regular key (KEY), though primary key is the most common usage. Omitting the key definition entirely triggers error 1075.

2. More than one AUTO_INCREMENT column in the same table. MySQL allows at most one AUTO_INCREMENT column per table. Defining two or more will raise this error regardless of whether both are indexed.

3. Manual schema DDL with a missing PRIMARY KEY clause. When writing raw CREATE TABLE statements by hand and relying on AUTO_INCREMENT as the row identifier, developers sometimes add the AUTO_INCREMENT attribute but forget to declare PRIMARY KEY or add a separate KEY definition for the column.

4. Incorrect ALTER TABLE statement. Adding AUTO_INCREMENT to an existing column that has no index, or adding a second AUTO_INCREMENT column through ALTER TABLE ADD COLUMN, will trigger the same error.

Troubleshooting and Resolution Steps

1. Identify the problematic CREATE TABLE or ALTER TABLE statement. Review the DDL being executed. The error message does not name the column, so you must inspect the statement directly.

2. Ensure the AUTO_INCREMENT column is a key. The most common pattern is to make it the PRIMARY KEY:

   CREATE TABLE orders (
     id INT UNSIGNED NOT NULL AUTO_INCREMENT,
     customer_id INT NOT NULL,
     created_at DATETIME NOT NULL,
     PRIMARY KEY (id)
   );
   

   If you do not want a primary key, you can use a regular index instead, though this is uncommon:

   CREATE TABLE orders (
     id INT UNSIGNED NOT NULL AUTO_INCREMENT,
     customer_id INT NOT NULL,
     KEY (id)
   );
   

3. Check for more than one AUTO_INCREMENT column. Review your CREATE TABLE statement for multiple occurrences of AUTO_INCREMENT. Only one column per table may carry this attribute:

   -- WRONG: two AUTO_INCREMENT columns
   CREATE TABLE bad_example (
     id INT AUTO_INCREMENT PRIMARY KEY,
     seq INT AUTO_INCREMENT  -- this triggers error 1075
   );
   
   -- CORRECT: one AUTO_INCREMENT column
   CREATE TABLE good_example (
     id INT AUTO_INCREMENT PRIMARY KEY,
     seq INT NOT NULL DEFAULT 0
   );
   

4. Fix an ALTER TABLE that adds AUTO_INCREMENT to an unindexed column. First add the index, then apply AUTO_INCREMENT:

   -- Add index first, then AUTO_INCREMENT
   ALTER TABLE orders ADD INDEX (legacy_id);
   ALTER TABLE orders MODIFY COLUMN legacy_id INT UNSIGNED NOT NULL AUTO_INCREMENT;
   
   -- Or combine both in one statement
   ALTER TABLE orders MODIFY COLUMN legacy_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
                      ADD INDEX (legacy_id);
   

5. Inspect an existing table to see current AUTO_INCREMENT and key configuration:

   SHOW CREATE TABLE orders\G
   -- or
   SELECT COLUMN_NAME, COLUMN_KEY, EXTRA
   FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA = 'your_database'
     AND TABLE_NAME = 'orders'
     AND EXTRA LIKE '%auto_increment%';
   

Additional Information

• The SQLSTATE code 42000 indicates a syntax or access rule violation — this is a DDL-level schema definition error, not a data or permissions issue.
• Error 1075 is consistent across MySQL 5.x, 8.0, and MariaDB. The constraint has been present since early MySQL versions and is part of the storage engine interface, not specific to InnoDB.
• MariaDB also supports AUTO_INCREMENT on non-primary-key columns as long as they are indexed, same as MySQL.
• With InnoDB (the default engine), the AUTO_INCREMENT column is almost always the primary key. MyISAM allows AUTO_INCREMENT on a non-first column of a composite key — but even there, it must be part of the key.
• Related errors: 1050 (ER_TABLE_EXISTS_ERROR) if a migration attempts to re-create an existing table; 1060 (ER_DUP_FIELDNAME) if an ALTER TABLE tries to add a duplicate column.

Frequently Asked Questions

Can I have an AUTO_INCREMENT column that is not the primary key? Yes. MySQL requires the column to be part of some index, but it does not have to be the primary key. Using KEY (col) is sufficient. In practice, nearly all schemas use AUTO_INCREMENT as the primary key (PRIMARY KEY (id)), but a secondary key works too.

Why does MySQL require AUTO_INCREMENT columns to be indexed? MySQL needs to efficiently look up the current maximum value of the column to compute the next sequence value. Without an index, finding the maximum would require a full table scan on every insert, making inserts O(n) rather than O(log n). The index requirement exists for performance correctness.

My ORM generated the DDL — how do I fix this? Check your model/entity definition. Most ORMs infer the primary key from the AUTO_INCREMENT field automatically, but if you explicitly annotated a column as auto-incrementing without also marking it as a key or primary key, you need to add that annotation. For example, in SQLAlchemy: Column(Integer, primary_key=True, autoincrement=True).

Can I use AUTO_INCREMENT with a composite primary key? Yes, with MyISAM. With InnoDB, the AUTO_INCREMENT column must be the first (leftmost) column of the key. Placing it as a non-leading column in a composite primary key will produce error 1075 with InnoDB.