MySQL error 1075: Only one auto column, and it must be a key
A table definition has an AUTO_INCREMENT column that isn't indexed, or more than one AUTO_INCREMENT column.
- Error number
1075- Name
ER_WRONG_AUTO_KEY- SQLSTATE
42000- Category
- Keys and constraints
What it means
MySQL allows one AUTO_INCREMENT column per table, and it must be the first column of some index, usually the primary key. Violating either rule makes CREATE TABLE or ALTER TABLE fail with 1075.
Common causes
- Declaring
id INT AUTO_INCREMENTwithout making it a key. - Dropping the primary key from an
AUTO_INCREMENTcolumn. - Putting
AUTO_INCREMENTon two columns.
How to fix it
-
Make the column a key.
CREATE TABLE notes ( id INT NOT NULL AUTO_INCREMENT, body TEXT, PRIMARY KEY (id) ); -
When changing the primary key, keep an index on the
AUTO_INCREMENTcolumn.ALTER TABLE notes DROP PRIMARY KEY, ADD PRIMARY KEY (id, created_at);
Related errors
More keys and constraints errors
1061Duplicate key name1452Foreign key fails: cannot add child row1451Foreign key fails: cannot delete parent row1216Cannot add or update a child row1217Cannot delete or update a parent row1215Cannot add foreign key constraint3780Foreign key columns are incompatible1822Missing index for foreign key constraint
The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.
Work on MySQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.