Foreign key constraints failure when cross updating between two databases

Question

I have two databases (com and usa) with a table with the following schema:

Create Table: CREATE TABLE `eav_attribute` (
 `attribute_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Attribute Id',
 `entity_type_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Type Id',
 `attribute_code` varchar(255) NOT NULL COMMENT 'Attribute Code',
 PRIMARY KEY (`attribute_id`),
 UNIQUE KEY `UNQ_EAV_ATTRIBUTE_ENTITY_TYPE_ID_ATTRIBUTE_CODE` (`entity_type_id`,`attribute_code`),
 KEY `IDX_EAV_ATTRIBUTE_ENTITY_TYPE_ID` (`entity_type_id`),
 CONSTRAINT `FK_EAV_ATTRIBUTE_ENTITY_TYPE_ID_EAV_ENTITY_TYPE_ENTITY_TYPE_ID` FOREIGN KEY (`entity_type_id`) REFERENCES `eav_entity_type` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=212 DEFAULT CHARSET=utf8 COMMENT='Eav Attribute'
1 row in set (0.00 sec)

I'd like to make the attribute_ids of USA the same as COM (where code + type match). This query shows my expected result by joining the two tables:

SELECT usa_attribute.attribute_id,
 usa_attribute.entity_type_id,
 usa_attribute.attribute_code,
 com_attribute.attribute_id,
 com_attribute.entity_type_id,
 com_attribute.attribute_code
FROM eav_attribute usa_attribute
LEFT JOIN com.eav_attribute com_attribute
 ON ( usa_attribute.entity_type_id = com_attribute.entity_type_id
 AND usa_attribute.attribute_code = com_attribute.attribute_code );
+--------------+----------------+--------------------------------+--------------+----------------+--------------------------------+
| attribute_id | entity_type_id | attribute_code | attribute_id | entity_type_id | attribute_code |
+--------------+----------------+--------------------------------+--------------+----------------+--------------------------------+
| 615 | 1 | confirmation | 16 | 1 | confirmation |
| 616 | 1 | created_at | 481 | 1 | created_at |
| 602 | 1 | created_in | 3 | 1 | created_in |
| 612 | 1 | default_billing | 13 | 1 | default_billing |
| 613 | 1 | default_shipping | 14 | 1 | default_shipping |
| 782 | 1 | disable_auto_group_change | 571 | 1 | disable_auto_group_change |
| 610 | 1 | dob | 11 | 1 | dob |
| 647 | 3 | children | 46 | 3 | children |
| 656 | 3 | children_count | 55 | 3 | children_count |
| 706 | 3 | custom_apply_to_products | 494 | 3 | custom_apply_to_products |
| 649 | 3 | custom_design | 48 | 3 | custom_design |
| 651 | 3 | custom_design_from | 50 | 3 | custom_design_from |
| 652 | 3 | custom_design_to | 51 | 3 | custom_design_to |
| 654 | 3 | custom_layout_update | 53 | 3 | custom_layout_update |
| 705 | 3 | custom_use_parent_settings | 493 | 3 | custom_use_parent_settings |
| 658 | 3 | default_sort_by | 486 | 3 | default_sort_by |
| 635 | 3 | description | 34 | 3 | description |
| 640 | 3 | display_mode | 39 | 3 | display_mode |
| 707 | 3 | filter_price_range | 495 | 3 | filter_price_range |
| 636 | 3 | image | 35 | 3 | image |
| 704 | 3 | include_in_menu | 490 | 3 | include_in_menu |
| 633 | 3 | is_active | 32 | 3 | is_active |
| 642 | 3 | is_anchor | 41 | 3 | is_anchor |
| 810 | 3 | is_spacer | 599 | 3 | is_spacer |
| 641 | 3 | landing_page | 40 | 3 | landing_page |
| 655 | 3 | level | 54 | 3 | level |
| 639 | 3 | meta_description | 38 | 3 | meta_description |
| 638 | 3 | meta_keywords | 37 | 3 | meta_keywords |
| 637 | 3 | meta_title | 36 | 3 | meta_title |
| 632 | 3 | name | 31 | 3 | name |
| 653 | 3 | page_layout | 52 | 3 | page_layout |
| 643 | 3 | path | 42 | 3 | path |
| 646 | 3 | path_in_store | 45 | 3 | path_in_store |
| 644 | 3 | position | 43 | 3 | position |
| 719 | 3 | thumbnail | 568 | 3 | thumbnail |
| 634 | 3 | url_key | 33 | 3 | url_key |
| 648 | 3 | url_path | 47 | 3 | url_path |
| 679 | 4 | color | 76 | 4 | color |
| 772 | 4 | color_description | 469 | 4 | color_description |
| 667 | 4 | cost | 64 | 4 | cost |
| 788 | 4 | country_of_manufacture | 577 | 4 | country_of_manufacture |
| 702 | 4 | created_at | 488 | 4 | created_at |
| 691 | 4 | custom_design | 86 | 4 | custom_design |
| 692 | 4 | custom_design_from | 87 | 4 | custom_design_from |
| 693 | 4 | custom_design_to | 88 | 4 | custom_design_to |
| 694 | 4 | custom_layout_update | 89 | 4 | custom_layout_update |
| 660 | 4 | description | 57 | 4 | description |
| 740 | 4 | e3ac01 | 539 | 4 | e3ac01 |
| 741 | 4 | e3ac02 | 540 | 4 | e3ac02 |
| 742 | 4 | e3ac03 | 541 | 4 | e3ac03 |
| 743 | 4 | e3ac04 | 542 | 4 | e3ac04 |
| 744 | 4 | e3ac05 | 543 | 4 | e3ac05 |
| 745 | 4 | e3ac06 | 544 | 4 | e3ac06 |
| 746 | 4 | e3ac07 | 545 | 4 | e3ac07 |
| 747 | 4 | e3ac08 | 546 | 4 | e3ac08 |
| 748 | 4 | e3ac09 | 547 | 4 | e3ac09 |
| 730 | 4 | e3cdes | 456 | 4 | e3cdes |
| 735 | 4 | e3col | 461 | 4 | e3col |
| 725 | 4 | e3cono | 451 | 4 | e3cono |
| 749 | 4 | e3cost | 548 | 4 | e3cost |
| 731 | 4 | e3csds | 457 | 4 | e3csds |
| 758 | 4 | e3ris1 | 553 | 4 | e3ris1 |
+--------------+----------------+--------------------------------+--------------+----------------+--------------------------------+

Before I run the query I can confirm that USA has no attribute_id = 51:

# There is no attribute_id before I begin the copy
mysql> select * from eav_attribute where attribute_id = 51;
Empty set (0.00 sec)

However the update causes a FK constraint error. Any idea why? How do I do this update successfully?

UPDATE eav_attribute usa_attribute, 
 com.eav_attribute com_attribute
SET usa_attribute.attribute_id = com_attribute.attribute_id
WHERE usa_attribute.entity_type_id = com_attribute.entity_type_id
 AND usa_attribute.attribute_code = com_attribute.attribute_code

Upholding foreign key constraints for table 'eav_attribute', entry '51', key 1 would lead to a duplicate entry

Answer 1

Ok, so I should have guessed - when getting a FK error I should check other tables referencing this table to see if ON UPDATE CASCADE might cause issues. To do this I did the following query:

SELECT table_name, 
 column_name, 
 constraint_name, 
 referenced_table_name, 
 referenced_column_name 
FROM information_schema.key_column_usage 
WHERE referenced_table_name = 'eav_attribute' AND table_schema = 'usa';
+--------------------------------------+--------------+------------------------------------------------------------------+-----------------------+------------------------+
| table_name | column_name | constraint_name | referenced_table_name | referenced_column_name |
+--------------------------------------+--------------+------------------------------------------------------------------+-----------------------+------------------------+
| catalog_category_entity_datetime | attribute_id | FK_CAT_CTGR_ENTT_DTIME_ATTR_ID_EAV_ATTR_ATTR_ID | eav_attribute | attribute_id |
| catalog_category_entity_decimal | attribute_id | FK_CAT_CTGR_ENTT_DEC_ATTR_ID_EAV_ATTR_ATTR_ID | eav_attribute | attribute_id |
| catalog_category_entity_int | attribute_id | FK_CAT_CTGR_ENTT_INT_ATTR_ID_EAV_ATTR_ATTR_ID | eav_attribute | attribute_id |
| catalog_category_entity_text | attribute_id | FK_CAT_CTGR_ENTT_TEXT_ATTR_ID_EAV_ATTR_ATTR_ID | eav_attribute | attribute_id |
......many more
| googlebase_attributes | attribute_id | GOOGLEBASE_ATTRIBUTES_ATTRIBUTE_ID | eav_attribute | attribute_id |
| salesrule_product_attribute | attribute_id | FK_SALESRULE_PRD_ATTR_ATTR_ID_EAV_ATTR_ATTR_ID | eav_attribute | attribute_id |
| weee_tax | attribute_id | FK_WEEE_TAX_ATTRIBUTE_ID_EAV_ATTRIBUTE_ATTRIBUTE_ID | eav_attribute | attribute_id |
+--------------------------------------+--------------+------------------------------------------------------------------+-----------------------+------------------------+

I was then able to do a quick select against these tables:

select * from catalog_category_entity_datetime where attribute_id = 51;
select * from catalog_category_entity_decimal where attribute_id = 51;
select * from catalog_category_entity_int where attribute_id = 51;
select * from catalog_category_entity_text where attribute_id = 51;
..... many more
select * from googlebase_attributes where attribute_id = 51;
select * from salesrule_product_attribute where attribute_id = 51;
select * from weee_tax where attribute_id = 51;

Then I could see that two tables had references to my first table (eav_attribute) with the values '51'.

Not sure how these got there, but since they dont match the FK I could happily delete them.