This MySQL error occurs when you define an AUTO_INCREMENT column without a key constraint, or attempt to create multiple AUTO_INCREMENT columns. Fix it by adding PRIMARY KEY to your AUTO_INCREMENT column or ensuring only one auto column exists per table.
MySQL Error 1075 (SQLSTATE 42000) enforces a critical table design rule: every table can have at most one AUTO_INCREMENT column, and that column must be defined as a key (PRIMARY KEY, UNIQUE KEY, or indexed column). When you violate this rule by defining AUTO_INCREMENT on a non-indexed column or creating multiple AUTO_INCREMENT columns, MySQL rejects the table definition during CREATE TABLE or ALTER TABLE operations. This is a fundamental constraint that ensures MySQL can reliably generate and track unique sequence numbers. The error message is strict because allowing multiple AUTO_INCREMENT columns or non-indexed auto columns would break MySQL's ability to enforce uniqueness.
The simplest solution is to define PRIMARY KEY inline with the AUTO_INCREMENT column:
-- FAILS with ERROR 1075:
CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(100),
email VARCHAR(100)
);
-- WORKS: Add PRIMARY KEY
CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);The PRIMARY KEY constraint:
- Ensures the AUTO_INCREMENT column is indexed
- Guarantees uniqueness of generated sequence numbers
- Satisfies MySQL's requirement that auto columns must be keys
If you need a compound primary key with InnoDB, the AUTO_INCREMENT column must be the leftmost (first) column:
-- FAILS with InnoDB (ERROR 1075):
CREATE TABLE order_items (
order_id INT NOT NULL,
id INT NOT NULL AUTO_INCREMENT,
product_id INT NOT NULL,
quantity INT,
PRIMARY KEY(order_id, id) -- AUTO_INCREMENT not first
);
-- WORKS: AUTO_INCREMENT column first
CREATE TABLE order_items (
id INT NOT NULL AUTO_INCREMENT,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT,
PRIMARY KEY(id, order_id) -- AUTO_INCREMENT is first
);Why this matters for InnoDB:
- InnoDB optimizes AUTO_INCREMENT lookups by using the leftmost column
- If AUTO_INCREMENT is not first, InnoDB cannot efficiently generate the next sequence value
- MyISAM is more flexible here, but InnoDB enforces this rule strictly
If you're modifying an existing table, verify only one column has AUTO_INCREMENT:
-- View all columns and their attributes
DESC your_table_name;
-- Check if multiple columns have AUTO_INCREMENT
SELECT COLUMN_NAME, EXTRA FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your_table_name'
AND EXTRA LIKE '%auto_increment%';If multiple columns show AUTO_INCREMENT, remove it from all but one:
-- Remove AUTO_INCREMENT from the secondary column
ALTER TABLE your_table_name
MODIFY secondary_col INT NOT NULL;
-- Keep it on the primary column
ALTER TABLE your_table_name
MODIFY primary_col INT NOT NULL AUTO_INCREMENT PRIMARY KEY;If you already have a different PRIMARY KEY, you can use UNIQUE KEY for the AUTO_INCREMENT column:
-- Table with natural primary key and separate auto column
CREATE TABLE products (
sku VARCHAR(50) PRIMARY KEY,
id INT NOT NULL AUTO_INCREMENT UNIQUE KEY,
name VARCHAR(100),
price DECIMAL(10,2)
);The AUTO_INCREMENT column still needs to be indexed (PRIMARY KEY, UNIQUE KEY, or regular INDEX), but it doesn't have to be the primary key. However, PRIMARY KEY is most common because:
- It's slightly more efficient
- It signals the auto-generated identifier clearly
- It's the typical pattern in most applications
MyISAM allows AUTO_INCREMENT on secondary columns in compound keys, but this is rarely recommended:
-- Works in MyISAM but NOT in InnoDB:
CREATE TABLE logs (
category ENUM('error', 'warning', 'info') NOT NULL,
id INT NOT NULL AUTO_INCREMENT,
message TEXT,
PRIMARY KEY(category, id) -- AUTO_INCREMENT is NOT first
) ENGINE=MyISAM;Important considerations:
- This generates a separate sequence per category value
- Most new projects use InnoDB (MySQL 5.7+), which doesn't support this
- Modern best practice is to make AUTO_INCREMENT the leftmost/only indexed column
- If you need per-group sequences, use application-level logic instead
Storage Engine Differences: InnoDB and MyISAM have different AUTO_INCREMENT rules. InnoDB requires the AUTO_INCREMENT column to be the first column in an index (or the only column), while MyISAM allows it to be a secondary column in a compound index. When migrating from MyISAM to InnoDB, you may encounter this error if your schema uses MyISAM-style compound keys.
Index Selection: When multiple indexes include the AUTO_INCREMENT column, MySQL chooses the index that starts with the AUTO_INCREMENT column for generating sequence values. For example, if you have both PRIMARY KEY(id) and INDEX(id, status), MySQL uses the PRIMARY KEY since it's listed first. This matters for efficiency in high-volume insert scenarios.
Integer Overflow: Remember that AUTO_INCREMENT uses integer data types. INT supports up to 2 billion unique values, INT UNSIGNED supports ~4 billion, and BIGINT UNSIGNED supports ~18 quintillion. Choose the appropriate type to avoid sequence exhaustion in long-running systems. If you hit the integer limit, inserts will fail until you expand the column type.
Replication Considerations: In MySQL replication, AUTO_INCREMENT behavior can be tricky with multi-source replication or circular topologies. The auto_increment_increment and auto_increment_offset variables control this, but the column definition must still satisfy the basic rules outlined above.
EE_WRITE (3): Error writing file
How to fix "EE_WRITE (3): Error writing file" in MySQL
CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters
How to fix "CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters" in MySQL
CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed
How to fix "CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed" in MySQL
ERROR 1146: Table 'database.table' doesn't exist
How to fix "ERROR 1146: Table doesn't exist" in MySQL
ERROR 1040: Too many connections
How to fix "ERROR 1040: Too many connections" in MySQL