Error
Error Code:
1240
MariaDB Error 1240: Mismatched Key and Table Reference
Description
This error indicates a fundamental mismatch between a key reference (e.g., in a foreign key, index, or partition definition) and the table it is intended to reference. It commonly occurs during `CREATE TABLE` or `ALTER TABLE` operations when defining new constraints or modifying table structures.
Error Message
Key reference and table reference don't match
Known Causes
4 known causesIncorrect Foreign Key Definition
A foreign key constraint references a column that either does not exist in the parent table or has a mismatch in data type or length.
Invalid Partition Key Column
When defining table partitioning, the specified partition key column does not exist within the table or is incorrectly referenced.
Non-Existent Column in Index
An index definition attempts to create an index on a column that does not exist in the table being modified or created.
Generated Column Reference Error
A generated column or `CHECK` constraint references a column that is misspelled or does not exist in the table definition.
Solutions
3 solutions available1. Verify Foreign Key Constraint Definitions easy
Ensure that foreign key constraints correctly reference columns in the parent table.
1
Identify the table and column involved in the foreign key constraint that is causing the error. This information is usually present in the specific error message or can be found by examining your `CREATE TABLE` statements or `SHOW CREATE TABLE` output.
2
For the child table, examine the `FOREIGN KEY` definition. Pay close attention to the column(s) listed in parentheses after the `REFERENCES` keyword. These should be the column(s) in the parent table that the foreign key points to.
SHOW CREATE TABLE your_child_table;3
For the parent table, confirm that the column(s) specified in the `REFERENCES` clause of the child table's foreign key definition actually exist and are appropriately indexed (preferably as a primary key or a unique key) in the parent table.
SHOW CREATE TABLE your_parent_table;4
If there's a mismatch (e.g., incorrect column name, missing column, incorrect data type), you will need to drop and recreate the foreign key constraint with the correct definitions. If the table structure needs to be altered, use `ALTER TABLE` statements.
ALTER TABLE your_child_table DROP FOREIGN KEY your_fk_constraint_name;
ALTER TABLE your_child_table ADD CONSTRAINT your_fk_constraint_name FOREIGN KEY (child_column_name) REFERENCES your_parent_table(parent_column_name) ON DELETE ... ON UPDATE ...;2. Check Data Type Compatibility medium
Confirm that the data types of the referencing and referenced columns are compatible.
1
Retrieve the `CREATE TABLE` statements for both the child and parent tables involved in the foreign key relationship.
SHOW CREATE TABLE your_child_table;
SHOW CREATE TABLE your_parent_table;2
Carefully compare the data types of the column(s) defined in the foreign key constraint in the child table and the corresponding column(s) in the parent table. For example, if the child column is `INT` and the parent column is `BIGINT`, this can sometimes cause issues, though MariaDB is generally flexible. However, stark differences like `VARCHAR` to `INT` will certainly cause problems.
3
If data types are incompatible, you'll need to alter the table(s) to make them compatible. This might involve changing the data type of one of the columns. Be very cautious when altering column data types, especially on large tables, as it can be a time-consuming and resource-intensive operation, and may lead to data loss if not handled carefully. Consider creating a new column with the correct type, migrating data, and then dropping the old column.
ALTER TABLE your_table_name MODIFY COLUMN your_column_name NEW_DATA_TYPE;4
After ensuring data type compatibility, drop and recreate the foreign key constraint if it was previously dropped or if the alteration requires it.
ALTER TABLE your_child_table DROP FOREIGN KEY your_fk_constraint_name;
ALTER TABLE your_child_table ADD CONSTRAINT your_fk_constraint_name FOREIGN KEY (child_column_name) REFERENCES your_parent_table(parent_column_name) ON DELETE ... ON UPDATE ...;3. Recreate Corrupted Index or Constraint medium
Sometimes, index or constraint metadata can become corrupted, leading to this error. Recreating them can resolve the issue.
1
Identify the foreign key constraint name that is causing the error. You can usually find this by examining `SHOW CREATE TABLE` output for the child table.
SHOW CREATE TABLE your_child_table;2
Drop the problematic foreign key constraint.
ALTER TABLE your_child_table DROP FOREIGN KEY your_fk_constraint_name;3
Recreate the foreign key constraint using the correct syntax and ensuring the referenced columns in the parent table are valid (preferably a PRIMARY KEY or UNIQUE index).
ALTER TABLE your_child_table ADD CONSTRAINT your_fk_constraint_name FOREIGN KEY (child_column_name) REFERENCES your_parent_table(parent_column_name) ON DELETE ... ON UPDATE ...;4
If the error persists, it might indicate a deeper corruption. Consider checking the integrity of the table using `CHECK TABLE` and potentially repairing it if necessary, though this is a more drastic step.
CHECK TABLE your_child_table;
REPAIR TABLE your_child_table;