Developer

Page tree

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)

Solutions for case 2:

Solution A - inconsistent data types / collations / encodings

See https://stackoverflow.com/questions/16969060/mysql-error-1215-cannot-add-foreign-key-constraint

One example to get the error: 

  1. Livesystem DB System or DB itself is using encoding and/or collation A as default
  2. Testsystem DB System or DB itself is using encoding and/or collation B as default
  3. User makes a dump from live and applies it to Testsystem
  4. User then tries to update the Testsystem
    1. The dump from live creates table LEK_task with encoding/collation A
    2. The Update tries to create table XYZ with collation B
  5. => Result: the foreign key could not be created

Solution B - innoDB foreign keys corrupt

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