📋

Description

MySQL Error 1072, 'Key column '%s' doesn't exist in table', indicates that a column specified in a key definition (such as PRIMARY KEY, UNIQUE KEY, or FOREIGN KEY) does not exist within the table being modified or created. This error typically occurs during `CREATE TABLE` or `ALTER TABLE` statements when MySQL cannot find the referenced column.

💬

Error Message

Key column '%s' doesn't exist in table

🔍

Known Causes

3 known causes

⚠️

Typo in Column Name

The column name provided in the key definition statement is misspelled or does not exactly match an existing column in the table.

⚠️

Missing Column Definition

The key is being defined on a column that has not yet been declared or created within the table's schema.

⚠️

Case Sensitivity Mismatch

In a case-sensitive MySQL environment, the casing of the column name in the key definition does not match the actual column's casing.

🛠️

Solutions

4 solutions available

1. Check Column Name in Index easy

Verify column exists with exact spelling

1

List all columns

DESCRIBE your_table;

2

Fix column name in index definition

-- Wrong (column is 'email' not 'e_mail'):
CREATE INDEX idx_email ON users (e_mail);

-- Right:
CREATE INDEX idx_email ON users (email);

2. Add Column Before Creating Index easy

Column must exist before indexing

1

Add the column first

ALTER TABLE users ADD COLUMN email VARCHAR(255);

2

Then create the index

CREATE INDEX idx_email ON users (email);

3

Or do both in one statement

ALTER TABLE users 
  ADD COLUMN email VARCHAR(255),
  ADD INDEX idx_email (email);

3. Fix Case Sensitivity Issue easy

Column names may be case-sensitive

1

Check exact column name

SELECT COLUMN_NAME FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'your_table';

2

Use exact case in index

-- If column is 'Email' not 'email':
CREATE INDEX idx_email ON users (Email);

4. Fix Composite Index Column Order easy

All columns in composite index must exist

1

Check all columns exist

-- If creating: INDEX idx_name (first_name, middle_name, last_name)
-- Verify all three columns exist:
DESCRIBE users;

2

Add missing columns or remove from index

-- Option 1: Add missing column
ALTER TABLE users ADD COLUMN middle_name VARCHAR(50);

-- Option 2: Create index without that column
CREATE INDEX idx_name ON users (first_name, last_name);

🔗

Related Errors

5 related errors

MySQL Error 1072: Key Column Not Found