Page tree

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 2 Current »

It can happen that the foreign key management of MySQL produces errors like:

  1. ERROR 1215 (HY000) at line 34: Cannot add foreign key constraint
  2. SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails

If in the first case the referenced table does exist, or in the second case the referenced data exists in the target table, then probably the foreign keys are damaged some how.

Solution for case 1:

Example SQL which had triggered the error in the past
CREATE TABLE `LEK_match_analysis_taskassoc` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `taskGuid` VARCHAR(38) NULL,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `internalFuzzy` TINYINT(1) NULL DEFAULT 0,
  `pretranslateMatchrate` INT(11) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_LEK_match_analysis_taskassoc_1_idx` (`taskGuid` ASC),
  CONSTRAINT `fk_LEK_match_analysis_taskassoc_1`
    FOREIGN KEY (`taskGuid`)
    REFERENCES `LEK_task` (`taskGuid`)
    ON DELETE CASCADE 
    ON UPDATE CASCADE);


  1. Ensure that no one can use the application (maintenance mode)

  2. mysqldump the target table (in the example LEK_task, to which a foreign key should be created)

  3. SET foreign_key_checks = 0;

  4. DROP the dumped table (LEK_task in the example)

  5. Reimport the dumped table (LEK_task in the example)

  6. SET foreign_key_checks = 1;

What probably could also help: If there is a key on the foreign table affecting the field to be used as foreign key: recreate that index. (Not tested)

Solution for case 2:

In that case the error happened on Updating data in the LEK_term_attributes table. Error was:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`translate5Net`.`LEK_term_attributes`, CONSTRAINT `fk_LEK_term_attributes_2` FOREIGN KEY (`termId`) REFERENCES `LEK_terms` (`id`) ON DELETE CASCADE ON UPDATE CASCADE), query was: INSERT INTO `LEK_term_attributes` (`labelId`, `collectionId`, `termId`, `parentId`, `internalCount`, `language`, `name`, `attrType`, `attrDataType`, `attrTarget`, `attrId`, `attrLang`, `value`, `updated`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

This was very strange since the inserted data / foreign key DID exists in the LEK_terms table.

The solution was to drop and recreate the key:

Solution: recreate the foreign key
ALTER TABLE `LEK_term_attributes` DROP FOREIGN KEY `fk_LEK_term_attributes_2`;
ALTER TABLE `LEK_term_attributes` ADD CONSTRAINT `fk_LEK_term_attributes_2` FOREIGN KEY (`termId`) REFERENCES `LEK_terms` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  • No labels