Error
Error Code:
1072
MariaDB Error 1072: Missing Key Column
Description
This error occurs when a Data Definition Language (DDL) statement, such as `CREATE INDEX` or `ALTER TABLE`, attempts to define a key (primary, unique, or foreign) on a column that does not exist in the specified table. It indicates a mismatch between the column name provided and the actual table schema.
Error Message
Key column '%s' doesn't exist in table
Known Causes
4 known causesTypo in Column Name
A misspelled or incorrect column name was provided when attempting to define a key or index on a table.
Column Not Yet Created
The DDL statement attempts to create a key on a column that has not been previously added to the table schema.
Column Renamed or Dropped
The specified column was previously renamed or dropped from the table, and the key definition refers to its old name.
Incorrect Table Context
The DDL statement is being applied to the wrong table, which does not contain the specified key column.
Solutions
3 solutions available1. Verify and Add the Missing Column easy
The most direct solution is to add the column that the index or foreign key is trying to reference.
1
Identify the table and the missing column name from the error message. The error message will typically look like `ERROR 1072 (42S22): Key column 'column_name' doesn't exist in table 'table_name'`.
2
Connect to your MariaDB server using a client like `mysql` or `mariadb`.
mariadb -u your_user -p3
Select the database containing the table.
USE your_database_name;4
Add the missing column to the table. Replace `table_name`, `column_name`, and `column_definition` with your specific details. `column_definition` should include the data type and any constraints (e.g., `INT NOT NULL`, `VARCHAR(255)`).
ALTER TABLE table_name ADD COLUMN column_name column_definition;5
If the missing column was intended for an index or foreign key, you may need to re-create that index or foreign key after adding the column.
ALTER TABLE table_name ADD INDEX index_name (column_name); -- or CREATE INDEX...6
Commit any pending transactions if you are in a transactional context.
COMMIT;2. Correcting Incorrect Index or Foreign Key Definition medium
If the column name is misspelled or incorrectly referenced in an index or foreign key constraint, correct the definition.
1
Examine the `CREATE TABLE` statement or `ALTER TABLE` statements that define indexes and foreign keys for the table in question. Pay close attention to the column names used in these definitions.
2
Connect to your MariaDB server.
mariadb -u your_user -p3
Select the relevant database.
USE your_database_name;4
To find existing indexes, use `SHOW INDEXES FROM table_name;`.
SHOW INDEXES FROM table_name;5
To find foreign key constraints, query the `INFORMATION_SCHEMA.KEY_COLUMN_USAGE` table or `SHOW CREATE TABLE table_name;`.
SELECT CONSTRAINT_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'table_name' AND REFERENCED_TABLE_NAME IS NOT NULL;6
If you find a typo or incorrect column reference in an index, drop and re-create the index with the correct column name. Replace `table_name`, `index_name`, and `correct_column_name`.
DROP INDEX index_name ON table_name;
CREATE INDEX index_name ON table_name (correct_column_name);7
If you find a typo or incorrect column reference in a foreign key, drop and re-create the foreign key constraint. You will need to know the constraint name. Replace `table_name`, `constraint_name`, `column_name`, `referenced_table_name`, and `referenced_column_name`.
ALTER TABLE table_name DROP FOREIGN KEY constraint_name;
ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES referenced_table_name(referenced_column_name);8
Commit any pending transactions.
COMMIT;3. Reviewing and Correcting Data Migration Scripts medium
If this error occurs during a data migration, the migration script likely has an error referencing a non-existent column.
1
Identify the specific SQL statement within your migration script that is failing. The error message should give you a clue.
2
Carefully review the problematic SQL statement. Look for `ALTER TABLE` statements, `CREATE INDEX` statements, or `INSERT`/`UPDATE` statements that reference columns.
3
Compare the column names used in the script against the actual schema of the target table in your MariaDB database. You can use `DESCRIBE table_name;` or `SHOW COLUMNS FROM table_name;` to see the table structure.
DESCRIBE table_name;4
Correct any typos or incorrect column names in your migration script.
5
Re-run the corrected migration script.