MySQL Error 1022: Why Am I Getting a Duplicate Key Error When Creating a Table?

MySQL Error 1022: Pinpointing the Duplication

Encountering MySQL error 1022 when creating a table can be perplexing, especially if the SQL statement appears valid. Let's delve into the issue and identify the underlying cause.

In the provided SQL, it's mentioned that there's only one key defined for the table. However, the problem appears to lie in the foreign key definition. MySQL throws error 1022 when attempting to insert a duplicate key during table creation.

The issue stems from the fact that the foreign key name (error_id) is the same as the name of another foreign key elsewhere in the model. To understand this, consider the following scenario:

• Table "Catalog" contains a foreign key named "supplier" that references table "Supplier."
• Table "Product" also contains a foreign key named "supplier" that references the same table "Supplier."

Now, if both foreign keys have the same name ("supplier"), MySQL interprets this as a "collision" in foreign key names. To resolve the problem, each foreign key needs to have a unique name within the entire schema.

For instance, you could use different names like:

• catalog_supplier for the foreign key in table "Catalog"
• product_supplier for the foreign key in table "Product"

By differentiating foreign key names, MySQL can distinguish between them and correctly handle the referential integrity while creating the tables.

The above is the detailed content of MySQL Error 1022: Why Am I Getting a Duplicate Key Error When Creating a Table?. For more information, please follow other related articles on the PHP Chinese website!