When working with MySQL, you may come across the error #1072 - Key column 'id ' doesn't exist in table. This error can be confusing, especially when you know the column does exist in the table. However, there are a few common causes for this error that are easy to overlook. In this blog, we’ll dive into the causes and the solution for this error.

Error Message:

#1072 - Key column 'id ' doesn't exist in table

Causes and Solutions

1. Extra Whitespace in Column Name

One of the most common causes of this error is having extra spaces or invisible characters in the column name. In the example above, there is an extra space after 'id '. MySQL sees this as a different column name from 'id', which leads to the error.

Solution: Remove any extra spaces or invisible characters from your SQL query or table definition. Ensure that the column name is clean and does not have any trailing spaces.

For example, if you’re trying to set 'id ' as a primary key:

ALTER TABLE your_table ADD PRIMARY KEY ('id ');

This query will throw an error because of the extra space. The correct query should be:

ALTER TABLE your_table ADD PRIMARY KEY ('id');

2. Missing Column Definition

Another possibility is that the column you’re trying to reference (id) doesn’t exist in the table yet. This can happen if you are trying to set the id column as a primary key or index before the column has been defined.

Solution: Double-check your table definition. If the id column isn’t defined, add it before attempting to use it as a key.

For example:

CREATE TABLE your_table (
    id INT AUTO_INCREMENT,
    name VARCHAR(255),
    PRIMARY KEY (id)
);

If the id column isn’t defined but you’re trying to set it as a primary key, you’ll encounter the error. Make sure that id is defined in your table structure.

3. Incorrect Use of Constraints

This error may also occur if you are adding the PRIMARY KEY constraint later on using ALTER TABLE, but the id column doesn’t already exist.

Solution: Ensure the id column exists in the table before adding constraints like PRIMARY KEY or INDEX.

Example:

ALTER TABLE your_table ADD PRIMARY KEY (id);

In this case, you must confirm that the id column is present before running the query.

4. Column Name Case Sensitivity

In some cases, MySQL treats column names as case-sensitive depending on the database settings. If you’re referencing id, but your table has it defined as ID or Id, this may cause the error.

Solution: Ensure that you’re using the correct case for column names. Check your database settings to see whether column names are case-sensitive.

For example:

ALTER TABLE your_table ADD PRIMARY KEY (ID); -- If the column is defined as 'ID'