Why am I getting a \'Duplicate Key Constraint Violation\' error (Error 1022) in MySQL?

Duplicate Key Constraint Violation in MySQL: Error 1022

MySQL Workbench generated the following SQL code to create a table:

<code class="sql">CREATE  TABLE IF NOT EXISTS `mydb`.`errors_reports` (
   `error_id` INT NOT NULL ,
   `report_short` VARCHAR(15) NOT NULL ,
PRIMARY KEY (`error_id`, `report_short`) ,
INDEX `error_id_idx` (`error_id` ASC) ,
INDEX `report_short_idx` (`report_short` ASC) ,
CONSTRAINT `error_id`
   FOREIGN KEY (`error_id` )
   REFERENCES `mydb`.`errors` (`error_id` )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION,
CONSTRAINT `report_short`
   FOREIGN KEY (`report_short` )
   REFERENCES `mydb`.`reports` (`report_short` )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)
ENGINE = InnoDB</code>

Upon execution, this query fails with the following error:

<code class="sql">ERROR 1022 (23000): Can't write; duplicate key in table 'errors_reports'</code>

Analysis

Initially, the primary key definition seemed valid, as there was only one primary key defined. However, the issue lies with the foreign key constraint names (error_id and report_short).

Resolution

MySQL does not allow foreign key constraints to have the same name within the entire database. As the table errors_reports has two foreign key constraints referencing other tables, it is necessary to ensure that their constraint names are unique.

To resolve the error, rename one or both of the foreign key constraints. For example, instead of "error_id", it could be named "error_id_ref".

Example

<code class="sql">CONSTRAINT `error_id_ref`
   FOREIGN KEY (`error_id` )
   REFERENCES `mydb`.`errors` (`error_id` )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION,
CONSTRAINT `report_short_ref`
   FOREIGN KEY (`report_short` )
   REFERENCES `mydb`.`reports` (`report_short` )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)</code>

Summary

When encountering MySQL error 1022 related to a duplicate key, it is important to verify not only the primary key definition but also the names of any foreign key constraints. Ensuring that these constraints have unique names will prevent the error from occurring.

The above is the detailed content of Why am I getting a \'Duplicate Key Constraint Violation\' error (Error 1022) in MySQL?. For more information, please follow other related articles on the PHP Chinese website!