MySQL will not turn a column into AUTO_INCREMENT
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a keyShort answer
MySQL accepts AUTO_INCREMENT only on one integer column per table, with no DEFAULT value, and that column must be indexed. Error 1075 means one of those rules is broken, most often the missing key. If the same ALTER also adds a primary key, duplicate values in the column stop it with error 1062.
Fix it fast
1Find out which cause you have
Print the table definition and read three things: which columns have AUTO_INCREMENT, which keys exist, and what type and default the target column has.
SHOW CREATE TABLE items\G2Match what you see, then jump to its fix
- 1
ERROR 1075 and the target column appears in no PRIMARY KEY, UNIQUE or KEY line of the definitionThe column is not part of any keyFix - 2
ERROR 1075, and the definition of the table shows AUTO_INCREMENT on a different columnAnother column in the table already has AUTO_INCREMENTFix - 3
The statement is rejected on the column itself, for a string or decimal column, or one that carries DEFAULTThe column is not an integer type, or it has a DEFAULT valueFix - 4
ERROR 1062 (23000): Duplicate entry for the key, raised by the ALTER that adds the primary keyExisting values in the column are not uniqueFix
- 1
What does the error look like?
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a keyERROR 1068 (42000): Multiple primary key definedERROR 1062 (23000): Duplicate entry
Cause 1 of 4
The column is not part of any key
1Check
The manual says an AUTO_INCREMENT column must be indexed. List the indexes of the table and look for the column.
SHOW INDEX FROM items;2Fix
Make the column the primary key in the same statement that makes it AUTO_INCREMENT. If the table already has a primary key on other columns, a second one gives error 1068, so add a unique key on the column instead.
-- no primary key yet
ALTER TABLE items
MODIFY id INT NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (id);
-- the table already has a primary key elsewhere
ALTER TABLE items
MODIFY id INT NOT NULL AUTO_INCREMENT,
ADD UNIQUE KEY uq_items_id (id);3Verify
The definition now shows AUTO_INCREMENT and the key on the same column.
SHOW CREATE TABLE items\GConfirmed by dev.mysql.com
Cause 2 of 4
Another column in the table already has AUTO_INCREMENT
1Check
A table can have only one AUTO_INCREMENT column. Ask the data dictionary which columns carry it.
SELECT COLUMN_NAME, COLUMN_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'items'
AND EXTRA LIKE '%auto_increment%';2Fix
Decide which column should own the counter. Restate the other column's definition without AUTO_INCREMENT, then set the one you want.
ALTER TABLE items MODIFY legacy_id INT NOT NULL;
ALTER TABLE items
MODIFY id INT NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (id);3Verify
Run the same information_schema query. Exactly one column is returned.
SELECT COLUMN_NAME FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'items' AND EXTRA LIKE '%auto_increment%';Confirmed by dev.mysql.com
Cause 3 of 4
The column is not an integer type, or it has a DEFAULT value
1Check
AUTO_INCREMENT applies only to integer types, and the column cannot have a DEFAULT value. Read the type and default of the column.
SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'items' AND COLUMN_NAME = 'id';2Fix
Use an integer type and drop the default. Pick the smallest integer type that is big enough for the rows you expect, and UNSIGNED if you can, because the counter stops at the limit of the type.
-- leave out the ADD PRIMARY KEY line if id is already the primary key
ALTER TABLE items
MODIFY id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (id);3Verify
The column now has an integer type and the default column is empty.
SHOW COLUMNS FROM items LIKE 'id';Confirmed by dev.mysql.com
Cause 4 of 4
Existing values in the column are not unique
1Check
A primary key is a unique index, so every value in the column must differ. Look for repeated values before you try again.
SELECT id, COUNT(*) AS copies
FROM items
GROUP BY id
HAVING COUNT(*) > 1;2Fix
Remove the surplus rows or give them their own ids, by hand and with a backup of the table. When every value is unique, run the ALTER from the first cause.
-- look at the rows that share an id, then correct them one by one
SELECT * FROM items
WHERE id IN (SELECT id FROM items GROUP BY id HAVING COUNT(*) > 1);
-- once the values are unique
ALTER TABLE items
MODIFY id INT NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (id);3Verify
The duplicate query from the check step returns no rows, and the ALTER completes.
SHOW CREATE TABLE items\GConfirmed by dev.mysql.com
How do I stop it coming back?
- Declare the counter in the CREATE TABLE of every new table: an integer column, NOT NULL, AUTO_INCREMENT, PRIMARY KEY.
- Before you convert an existing column, run a duplicate check on it and take a backup of the table.
- Choose the integer width from the number of rows you expect to ever insert, and use UNSIGNED where negative ids make no sense.
- Keep schema changes in migration files, so the key and the AUTO_INCREMENT change travel together.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
MySQL says Multiple primary key defined in CREATE or ALTER TABLE
ERROR 1068 (42000): Multiple primary key defined
A MySQL table can have only one PRIMARY KEY.
3 causes, each with a fixDatabases
Duplicate entry for key 'PRIMARY' when inserting a row
Error Code: 1062. Duplicate entry '1' for key 'PRIMARY'
Error 1062 says a row with that exact key value is already there.
3 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fix