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

Can't Write; Duplicate Key While Creating Table

You encounter Error 1022 when attempting to create a new table due to duplicate primary key entries. Even after reviewing the query, you may have difficulty identifying the source of the duplication. Let's investigate further.

The query you provided contains a constraint named "iduser" as the foreign key for the "userId" column. Additionally, it specifies a constraint named "idcategory" for the "circleId" column. These constraint names appear to be the root of the issue.

Constraints and Duplication

Constraints in MySQL must be unique across the entire database, not just for the specific table being created or modified. Therefore, it is possible that you already have other tables or indexes in your database that utilize the "iduser" or "idcategory" constraint names. This conflict leads to the duplicate key error.

Identifying Duplicate Constraints

To determine where these constraints are currently in use, execute the following query:

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

This query will display the tables and schemas where the "iduser" and "idcategory" constraints are referenced.

Solution

To resolve the error, you need to rename the duplicate constraints. Consider using unique names that are specific to the current table, such as "fk_user" for the foreign key constraint on the "userId" column.Once the constraints are renamed and the query is updated, you should be able to create the table successfully without the duplicate key error.

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