Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL will not turn a column into AUTO_INCREMENT

ErrorERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Short 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.

  • Applies to MySQL 8.4, InnoDB
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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\G
  2. 2Match what you see, then jump to its fix

    1. 1ERROR 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. 2ERROR 1075, and the definition of the table shows AUTO_INCREMENT on a different columnAnother column in the table already has AUTO_INCREMENTFix
    3. 3The 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. 4ERROR 1062 (23000): Duplicate entry for the key, raised by the ALTER that adds the primary keyExisting values in the column are not uniqueFix

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 key
  • ERROR 1068 (42000): Multiple primary key defined
  • ERROR 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\G

Confirmed by dev.mysql.com

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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\G

Confirmed by dev.mysql.com

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.