How to Resolve the MySQL 'Can't write; duplicate key in table' Error (1022)?

Troubleshooting "Can't write; duplicate key in table" Error

Upon encountering a 1022 error when executing a CREATE TABLE command due to duplicate keys, it's essential to inspect the query and identify the source of duplication.

In the example provided, the error message indicates an issue related to the constraints named "iduser" and "idcategory." Most likely, there are already constraints with those names defined in the database.

Resolving Duplicate Constraints

To resolve this issue, you need to rename the duplicate constraints. Constraints must be unique across the entire database, not limited to the specific table being created or altered.

Finding Existing Constraints

To locate where the duplicate 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 return the table schema and table name where each constraint is used. Once you have identified the conflicting constraints, you can rename them using the ALTER TABLE command. For example:

ALTER TABLE `apptwo`.`usercircle` DROP CONSTRAINT `iduser`;
ALTER TABLE `apptwo`.`usercircle` ADD CONSTRAINT `user_constraint` FOREIGN KEY (`userId`) REFERENCES `apptwo`.`user` (`idUser`) ON DELETE NO ACTION ON UPDATE NO ACTION;

The above is the detailed content of How to Resolve the MySQL 'Can't write; duplicate key in table' Error (1022)?. For more information, please follow other related articles on the PHP Chinese website!