EC
1121
MySQLERRORCommonDDLHIGH confidence

Incorrect table definition — only one auto column and it must be a key

Production Risk

Low — DDL fails; no data loss.

What this means

ER_WRONG_AUTO_KEY (1121, SQLSTATE 42000) is raised when an AUTO_INCREMENT column is defined but is not the first column of an index or PRIMARY KEY. This is a variant of 1075 with a slightly different trigger condition.

Why it happens
  1. 1AUTO_INCREMENT column is part of a composite key but is not the leftmost column
  2. 2AUTO_INCREMENT column has no index at all
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE t (id INT AUTO_INCREMENT, cat INT, PRIMARY KEY (cat, id));

expected output

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

Fix

Make the AUTO_INCREMENT column the first column of its key

WHEN AUTO_INCREMENT is in a composite key.

Make the AUTO_INCREMENT column the first column of its key
CREATE TABLE t (
  id INT AUTO_INCREMENT,
  cat INT,
  PRIMARY KEY (id),
  INDEX idx_cat (cat)
);

Why this works

InnoDB requires AUTO_INCREMENT to be the leftmost column in at least one index so it can find the max value efficiently.

What not to do

✕ Use a composite primary key with AUTO_INCREMENT as a non-leading column

MySQL does not allow this; redesign with AUTO_INCREMENT as the primary key and add the category as a separate indexed column.

Sources
Official documentation ↗

MySQL 8.0 — 1121 ER_WRONG_AUTO_KEY

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

← All MySQL errors