MySQL Error 1022: A Collision of Foreign Key Names

While attempting to create a table using MySQL, you encounter Error 1022: "Can't write; duplicate key in table." The peculiar aspect is that the table only defines a single key, but the error persists.

Upon investigation, it appears that a specific snippet within the table definition triggers the error:

CONSTRAINT `error_id`
FOREIGN KEY (`error_id` )
REFERENCES `mydb`.`errors` (`error_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,

While similar foreign key definitions exist in other tables without issue, the problem lies in the name of the foreign key: error_id.

The Root Cause:

The issue arises because a foreign key name cannot be the same as another foreign key name within the entire database model. This means that if two tables reference the same table, the foreign keys in each table must have unique names.

Solution:

To resolve the error, simply give the foreign key in question a different name. For instance, you could name it fk_error_id. This will distinguish it from any other foreign key in the model and allow MySQL to create the table successfully.