EC
1075
MySQLERRORCommonDDLHIGH confidence

Only one AUTO_INCREMENT column allowed and it must be a key

Production Risk

Low — DDL fails; no data loss.

What this means

ER_MULTIPLE_PRI_KEY (1075, SQLSTATE 42000) is raised when a table definition contains an AUTO_INCREMENT column that is not declared as a key, or when more than one AUTO_INCREMENT column is defined.

Why it happens
  1. 1AUTO_INCREMENT column is not part of any index
  2. 2More than one AUTO_INCREMENT column defined in the same CREATE TABLE
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE t (id INT AUTO_INCREMENT, other INT AUTO_INCREMENT);

expected output

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

Fix

Define exactly one AUTO_INCREMENT column and make it a key

WHEN Always.

Define exactly one AUTO_INCREMENT column and make it a key
CREATE TABLE t (
  id INT AUTO_INCREMENT PRIMARY KEY,
  other INT
);

Why this works

MySQL requires the AUTO_INCREMENT column to be indexed so it can efficiently find the maximum value.

What not to do

✕ Use AUTO_INCREMENT on a non-indexed column

Without an index MySQL cannot efficiently determine the next AUTO_INCREMENT value; hence the restriction.

Sources
Official documentation ↗

MySQL 8.0 — 1075 ER_MULTIPLE_PRI_KEY

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors