Magento Question posted in
The official documentation can be found here.

Error: 1005 Can't add foreign key (errno:121 Duplate key on write or update) – Magento

Whenever i try to reindex a table in magento it tries to create a foreign key. That’s not the problem. The error i’m getting however, is.
Reindexing magento 1.9.x
Looking into Mysql it gives the following error:

Error in foreign key constraint creation for table `admin_staging`.`#sql-5f81_690`.
A foreign key constraint of name `admin_staging`.`FK_CAT_PRD_FLAT_1_ENTT_ID_CAT_PRD_ENTT_ENTT_ID`
already exists. (Note that internally InnoDB adds 'databasename'
in front of the user-defined constraint name.)
Note that InnoDB's FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.

When i look into to know foreign keys in the system it can find ALOT. But not the one that should allready exist. When i execute the following SQL it also returns none. 

SELECT
    *
FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
    CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME = 'FK_CAT_PRD_FLAT_1_ENTT_ID_CAT_PRD_ENTT_ENTT_ID'

ps. also manually making the foreign key doesn’t work: 

ALTER TABLE `catalog_product_flat_1` ADD CONSTRAINT `FK_CAT_PRD_FLAT_1_ENTT_ID_CAT_PRD_ENTT_ENTT_ID_TEST` FOREIGN KEY(`entity_id`) REFERENCES `catalog_product_entity`(`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE

I don’t know what to do/check next? ps. i hope this isn’t a duplicate question since i read most of them.

UPDATE
I tried to restart the mysql server. After that i could add the foreign key.
So i tried to run the indexer.. No success 🙁 Still the same error. It succesfully removes the foreign key but can’t seem to add it again.
Also restarted and didn’t add foreign key manually. Got the error also then.
Really thinking it’s a mysql bug.

UPDATE2
As asked the SHOW CREATE TABLE

CREATE TABLE `catalog_product_flat_1` (
 `entity_id` int(10) unsigned NOT NULL COMMENT 'Entity Id',
 `attribute_set_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Attribute Set Id',
 `type_id` varchar(32) NOT NULL DEFAULT 'simple' COMMENT 'Type Id',
 `cost` decimal(12,4) DEFAULT NULL COMMENT 'Cost',
 `created_at` timestamp NULL DEFAULT NULL COMMENT 'Created At',
 `gift_message_available` smallint(6) DEFAULT NULL COMMENT 'Gift Message Available',
 `has_options` smallint(6) NOT NULL DEFAULT 0 COMMENT 'Has Options',
 `image_label` varchar(255) DEFAULT NULL COMMENT 'Image Label',
 `is_recurring` smallint(6) DEFAULT NULL COMMENT 'Is Recurring',
 `links_exist` int(11) DEFAULT NULL COMMENT 'Links Exist',
 `links_purchased_separately` int(11) DEFAULT NULL COMMENT 'Links Purchased Separately',
 `links_title` varchar(255) DEFAULT NULL COMMENT 'Links Title',
 `msrp` decimal(12,4) DEFAULT NULL COMMENT 'Msrp',
 `msrp_display_actual_price_type` varchar(255) DEFAULT NULL COMMENT 'Msrp Display Actual Price Type',
 `msrp_enabled` smallint(6) DEFAULT NULL COMMENT 'Msrp Enabled',
 `name` varchar(255) DEFAULT NULL COMMENT 'Name',
 `news_from_date` datetime DEFAULT NULL COMMENT 'News From Date',
 `news_to_date` datetime DEFAULT NULL COMMENT 'News To Date',
 `price` decimal(12,4) DEFAULT NULL COMMENT 'Price',
 `price_type` int(11) DEFAULT NULL COMMENT 'Price Type',
 `price_view` int(11) DEFAULT NULL COMMENT 'Price View',
 `recurring_profile` text DEFAULT NULL COMMENT 'Recurring Profile',
 `required_options` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Required Options',
 `shipment_type` int(11) DEFAULT NULL COMMENT 'Shipment Type',
 `short_description` text DEFAULT NULL COMMENT 'Short Description',
 `sku` varchar(64) DEFAULT NULL COMMENT 'Sku',
 `sku_type` int(11) DEFAULT NULL COMMENT 'Sku Type',
 `small_image` varchar(255) DEFAULT NULL COMMENT 'Small Image',
 `small_image_label` varchar(255) DEFAULT NULL COMMENT 'Small Image Label',
 `special_from_date` datetime DEFAULT NULL COMMENT 'Special From Date',
 `special_price` decimal(12,4) DEFAULT NULL COMMENT 'Special Price',
 `special_to_date` datetime DEFAULT NULL COMMENT 'Special To Date',
 `tax_class_id` int(10) unsigned DEFAULT NULL COMMENT 'Tax Class Id',
 `thumbnail` varchar(255) DEFAULT NULL COMMENT 'Thumbnail',
 `thumbnail_label` varchar(255) DEFAULT NULL COMMENT 'Thumbnail Label',
 `updated_at` timestamp NULL DEFAULT NULL COMMENT 'Updated At',
 `url_key` varchar(255) DEFAULT NULL COMMENT 'Url Key',
 `url_path` varchar(255) DEFAULT NULL COMMENT 'Url Path',
 `visibility` smallint(5) unsigned DEFAULT NULL COMMENT 'Visibility',
 `weight` decimal(12,4) DEFAULT NULL COMMENT 'Weight',
 `weight_type` int(11) DEFAULT NULL COMMENT 'Weight Type',
 `manufacturer_sku` varchar(255) DEFAULT NULL COMMENT 'Manufacturer Sku',
 `width` varchar(255) DEFAULT NULL COMMENT 'Width',
 `depth` varchar(255) DEFAULT NULL COMMENT 'Depth',
 `height` varchar(255) DEFAULT NULL COMMENT 'Height',
 `stock` int(11) DEFAULT NULL COMMENT 'Stock',
 `stock_value` varchar(255) DEFAULT NULL COMMENT 'Stock Value',
 `status` smallint(5) unsigned DEFAULT NULL COMMENT 'Status',
 PRIMARY KEY (`entity_id`),
 KEY `IDX_CATALOG_PRODUCT_FLAT_1_TYPE_ID` (`type_id`),
 KEY `IDX_CATALOG_PRODUCT_FLAT_1_ATTRIBUTE_SET_ID` (`attribute_set_id`),
 KEY `IDX_CATALOG_PRODUCT_FLAT_1_NAME` (`name`),
 KEY `IDX_CATALOG_PRODUCT_FLAT_1_PRICE` (`price`),
 KEY `IDX_CATALOG_PRODUCT_FLAT_1_CREATED_AT` (`created_at`),
 KEY `IDX_CATALOG_PRODUCT_FLAT_1_STOCK` (`stock`),
 KEY `IDX_CATALOG_PRODUCT_FLAT_1_STOCK_VALUE` (`stock_value`),
 KEY `IDX_CATALOG_PRODUCT_FLAT_1_STATUS` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Catalog Product Flat (Store 1)'

—

CREATE TABLE `catalog_product_entity` (
 `entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
 `entity_type_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Entity Type ID',
 `attribute_set_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Attribute Set ID',
 `type_id` varchar(32) NOT NULL DEFAULT 'simple' COMMENT 'Type ID',
 `sku` varchar(64) DEFAULT NULL COMMENT 'SKU',
 `has_options` smallint(6) NOT NULL DEFAULT 0 COMMENT 'Has Options',
 `required_options` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Required Options',
 `created_at` timestamp NULL DEFAULT NULL COMMENT 'Creation Time',
 `updated_at` timestamp NULL DEFAULT NULL COMMENT 'Update Time',
 PRIMARY KEY (`entity_id`),
 KEY `IDX_CATALOG_PRODUCT_ENTITY_ENTITY_TYPE_ID` (`entity_type_id`),
 KEY `IDX_CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID` (`attribute_set_id`),
 KEY `IDX_CATALOG_PRODUCT_ENTITY_SKU` (`sku`),
 CONSTRAINT `FK_CAT_PRD_ENTT_ATTR_SET_ID_EAV_ATTR_SET_ATTR_SET_ID` FOREIGN KEY (`attribute_set_id`) REFERENCES `eav_attribute_set` (`attribute_set_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `FK_CAT_PRD_ENTT_ENTT_TYPE_ID_EAV_ENTT_TYPE_ENTT_TYPE_ID` FOREIGN KEY (`entity_type_id`) REFERENCES `eav_entity_type` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=111152 DEFAULT CHARSET=utf8 COMMENT='Catalog Product Table'
Tags:

3

Answers


  1. 0

    I don’t know if CONSTRAINT_TYPE is part of the primary key of INFORMATION_SCHEMA.TABLE_CONSTRAINTS or if that table has an other unique key constraint on CONSTRAINT_NAME.

    But you could try the query on INFORMATION_SCHEMA.TABLE_CONSTRAINTS without the CONSTRAINT_TYPE.

    Login or Signup to reply.
  2. 0

    It appears that the original message is about that it can’t create FK, because for some reason the field entity_id in the relevant table is not unique.
    You should try 

    SELECT entity_id, COUNT(entity_id) AS CNT FROM catalog_product_entity
GROUP BY entity_id
HAVING CNT > 1;

    Try it also on the catalog_product_flat_1 table and see if deleting duplicate records in any of the tables help.

    Login or Signup to reply.
  3. 0

    (To long for a Comment; mostly debugging info so far…)

    I simplified the question to

    CREATE TABLE so729_flat (
  entity_id INT UNSIGNED NOT NULL,
  PRIMARY KEY(entity_id)
);

CREATE TABLE so729_cpe (
  entity_id INT UNSIGNED NOT NULL,
  PRIMARY KEY(entity_id)
);

ALTER TABLE `so729_flat`
    ADD CONSTRAINT  FOREIGN KEY(`entity_id`)
        REFERENCES `so729_cpe`(`entity_id`)
        ON DELETE CASCADE ON UPDATE CASCADE;

    and it works fine. After running the ALTER twice, I get:

    SHOW CREATE TABLE so729_flat G

CREATE TABLE `so729_flat` (
  `entity_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`entity_id`),
  CONSTRAINT `so729_flat_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `so729_cpe` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `so729_flat_ibfk_2` FOREIGN KEY (`entity_id`) REFERENCES `so729_cpe` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

    (which includes two FKs — not useful).

    Can you try simplifying your use case to still demonstrate the problem? Or enhance my example until it fails? Perhaps in the process you can spot the problem. Or can at least identify what triggers the problem.

    Oh, and please provide text, not images.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search