MySQL Error 1022: How Do I Resolve Duplicate Key Constraints When Creating a Table?

MySQL Error 1022: Duplicate Key in Table

Scenario:

Upon attempting to create a table, you encounter a perplexing error 1022 indicating a duplicate key constraint violation. Despite reviewing the query, the source of the duplication remains elusive.

Explanation:

While the error message mentions a duplicate key in the usercircle table, the cause may lie in another part of the database schema. Constraints, such as foreign key and unique key constraints, must be unique across the entire database, not just within a single table.

Solution:

1. Identify Duplicate Constraints:

   • Execute the following query to find where the constraints are used in the database:

   SELECT `TABLE_SCHEMA`, `TABLE_NAME`
   FROM `information_schema`.`KEY_COLUMN_USAGE`
   WHERE `CONSTRAINT_NAME` IN ('iduser', 'idcategory');

2. Rename Duplicate Constraints:

   • If you locate duplicate constraints, simply rename them to resolve the conflict. For example, if there's another constraint named iduser in a different table, rename it to iduser_other.

3. Recreate Table:

   • Once the duplicate constraints are renamed, recreate the usercircle table with the updated query.

By following these steps, you can resolve error 1022 and successfully create the desired table without duplicate key constraints.

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