MySQL Error:
1075, Only One Auto Column Allowed
What does this error mean?
MySQL error 1075 (ER_WRONG_AUTO_KEY) fires when a CREATE TABLE or ALTER TABLE statement defines more than one AUTO_INCREMENT column. MySQL enforces a strict rule: exactly one AUTO_INCREMENT column per table, and that column must be part of an index (usually the PRIMARY KEY). In data pipelines, this error surfaces during schema migrations or table creation steps — an ADF copy activity with auto-create enabled, a dbt run that materializes a table, or a Flyway/Liquibase migration script. The pipeline fails at the DDL stage before any data is loaded. You will see the error in the MySQL error log, the connector's activity output, or the ORM's migration traceback as 'Incorrect table definition; there can be only one auto column and it must be defined as a key'.
Common causes
- 1ORM or code generator (Django, SQLAlchemy, Sequelize) produces a table definition with AUTO_INCREMENT on both the primary key and a secondary column — common when models are exported from a database that handled this differently.
- 2Copying DDL from MariaDB that uses sequences (CREATE SEQUENCE) for multiple auto-incrementing values. The DDL export may translate sequences into AUTO_INCREMENT attributes, which fails on MySQL.
- 3Manually adding AUTO_INCREMENT to a second column during an ALTER TABLE without first removing it from the original column.
- 4ADF copy activity with 'auto create table' enabled infers column types from the source and applies AUTO_INCREMENT to multiple integer columns that had identity/serial semantics in PostgreSQL or SQL Server.
- 5Flyway or Liquibase migration script generated from a schema diff tool that marks both an id column and a revision_number column as AUTO_INCREMENT.
How to fix it
- 1Step 1: Identify which columns currently carry AUTO_INCREMENT: `SHOW CREATE TABLE your_table;` — look for every column with AUTO_INCREMENT in the output.
- 2Step 2: Decide which single column should keep AUTO_INCREMENT. Normally this is the PRIMARY KEY (e.g., `id`).
- 3Step 3: Remove AUTO_INCREMENT from the extra column(s): `ALTER TABLE your_table MODIFY COLUMN secondary_col INT NOT NULL;` — repeat for each extra AUTO_INCREMENT column.
- 4Step 4: If the remaining AUTO_INCREMENT column is not indexed, add it to a key: `ALTER TABLE your_table ADD PRIMARY KEY (id);` or `ALTER TABLE your_table ADD KEY (id);`
- 5Step 5: If you need a second column to auto-generate sequential values, create a trigger: `CREATE TRIGGER trg_seq BEFORE INSERT ON your_table FOR EACH ROW SET NEW.secondary_col = (SELECT IFNULL(MAX(secondary_col), 0) + 1 FROM your_table);`
- 6Step 6: For ADF pipelines, disable auto-create and run a pre-copy script with the correct DDL: set tableOption to 'none' in the sink settings and add your CREATE TABLE statement as the pre-copy script.
- 7Step 7: Re-run the migration or pipeline and verify: `SELECT COLUMN_NAME, EXTRA FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'your_table' AND EXTRA LIKE '%auto_increment%';` — should return exactly one row.
Example log output
ERROR 1075 (42000) at line 14: Incorrect table definition; there can be only one auto column and it must be defined as a key
Statement: CREATE TABLE staging_orders (id INT AUTO_INCREMENT PRIMARY KEY, seq_num INT AUTO_INCREMENT, order_date DATE NOT NULL) ENGINE=InnoDB;