Re: Error adding FK
Posted by:
Filipe Sá
Date: February 04, 2015 10:09AM
mg_catalog_product_entity:
<sql>
CREATE TABLE `mg_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_762F759E5D6A03C98DA667539F69518B` FOREIGN KEY (`entity_type_id`) REFERENCES `mg_eav_entity_type` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_CAT_PRD_ENTT_ATTR_SET_ID_EAV_ATTR_SET_ATTR_SET_ID` FOREIGN KEY (`attribute_set_id`) REFERENCES `mg_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 `mg_eav_entity_type` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_mg_CAT_PRD_ENTT_ATTR_SET_ID_mg_EAV_ATTR_SET_ATTR_SET_ID` FOREIGN KEY (`attribute_set_id`) REFERENCES `mg_eav_attribute_set` (`attribute_set_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5231 DEFAULT CHARSET=utf8 COMMENT='Catalog Product Table'
</sql>
The table were the error happens:
<sql>
CREATE TABLE `mg_catalog_product_flat_1` (
`entity_id` int UNSIGNED NOT NULL COMMENT 'entity_id' ,
`attribute_set_id` smallint UNSIGNED NOT NULL default '0' COMMENT 'attribute_set_id' ,
`type_id` varchar(32) NOT NULL default 'simple' COMMENT 'type_id' ,
`accessories_size` int NULL COMMENT 'accessories_size' ,
`accessories_size_value` varchar(255) NULL COMMENT 'accessories_size_value' ,
`bags_sizes` int NULL COMMENT 'bags_sizes' ,
`bags_sizes_value` varchar(255) NULL COMMENT 'bags_sizes_value' ,
`boot_sizes` int NULL COMMENT 'boot_sizes' ,
`boot_sizes_value` varchar(255) NULL COMMENT 'boot_sizes_value' ,
`cost` decimal(12,4) NULL COMMENT 'cost' ,
`created_at` timestamp NULL default NULL COMMENT 'created_at' ,
`description` text NULL COMMENT 'description' ,
`gift_message_available` smallint NULL COMMENT 'gift_message_available' ,
`has_options` smallint NOT NULL default '0' COMMENT 'has_options' ,
`image_label` varchar(255) NULL COMMENT 'image_label' ,
`is_recurring` smallint NULL COMMENT 'is_recurring' ,
`links_exist` int NULL COMMENT 'links_exist' ,
`links_purchased_separately` int NULL COMMENT 'links_purchased_separately' ,
`links_title` varchar(255) NULL COMMENT 'links_title' ,
`manufacturer` int NULL COMMENT 'manufacturer' ,
`manufacturer_value` varchar(255) NULL COMMENT 'manufacturer_value' ,
`msrp` decimal(12,4) NULL COMMENT 'msrp' ,
`msrp_display_actual_price_type` varchar(255) NULL COMMENT 'msrp_display_actual_price_type' ,
`msrp_enabled` smallint NULL COMMENT 'msrp_enabled' ,
`name` varchar(255) NULL COMMENT 'name' ,
`news_from_date` datetime NULL COMMENT 'news_from_date' ,
`news_to_date` datetime NULL COMMENT 'news_to_date' ,
`price` decimal(12,4) NULL COMMENT 'price' ,
`price_type` int NULL COMMENT 'price_type' ,
`price_view` int NULL COMMENT 'price_view' ,
`recurring_profile` text NULL COMMENT 'recurring_profile' ,
`required_options` smallint UNSIGNED NOT NULL default '0' COMMENT 'required_options' ,
`shipment_type` int NULL COMMENT 'shipment_type' ,
`short_description` text NULL COMMENT 'short_description' ,
`size_chart_url` varchar(255) NULL COMMENT 'size_chart_url' ,
`sku` varchar(64) NULL COMMENT 'sku' ,
`sku_type` int NULL COMMENT 'sku_type' ,
`small_image` varchar(255) NULL COMMENT 'small_image' ,
`small_image_label` varchar(255) NULL COMMENT 'small_image_label' ,
`special_from_date` datetime NULL COMMENT 'special_from_date' ,
`special_price` decimal(12,4) NULL COMMENT 'special_price' ,
`special_to_date` datetime NULL COMMENT 'special_to_date' ,
`tax_class_id` int UNSIGNED NULL COMMENT 'tax_class_id' ,
`thumbnail` varchar(255) NULL COMMENT 'thumbnail' ,
`thumbnail_label` varchar(255) NULL COMMENT 'thumbnail_label' ,
`updated_at` timestamp NULL default NULL COMMENT 'updated_at' ,
`url_key` varchar(255) NULL COMMENT 'url_key' ,
`url_path` varchar(255) NULL COMMENT 'url_path' ,
`visibility` smallint UNSIGNED NULL COMMENT 'visibility' ,
`volume_weight` decimal(12,4) NULL COMMENT 'volume_weight' ,
`weight` decimal(12,4) NULL COMMENT 'weight' ,
`weight_type` int NULL COMMENT 'weight_type' ,
PRIMARY KEY (`entity_id`),
INDEX `IDX_MG_CATALOG_PRODUCT_FLAT_1_TYPE_ID` (`type_id`),
INDEX `IDX_MG_CATALOG_PRODUCT_FLAT_1_ATTRIBUTE_SET_ID` (`attribute_set_id`),
INDEX `IDX_MG_CATALOG_PRODUCT_FLAT_1_ACCESSORIES_SIZE` (`accessories_size`),
INDEX `IDX_MG_CATALOG_PRODUCT_FLAT_1_ACCESSORIES_SIZE_VALUE` (`accessories_size_value`),
INDEX `IDX_MG_CATALOG_PRODUCT_FLAT_1_BAGS_SIZES` (`bags_sizes`),
INDEX `IDX_MG_CATALOG_PRODUCT_FLAT_1_BAGS_SIZES_VALUE` (`bags_sizes_value`),
INDEX `IDX_MG_CATALOG_PRODUCT_FLAT_1_BOOT_SIZES` (`boot_sizes`),
INDEX `IDX_MG_CATALOG_PRODUCT_FLAT_1_BOOT_SIZES_VALUE` (`boot_sizes_value`),
INDEX `IDX_MG_CATALOG_PRODUCT_FLAT_1_NAME` (`name`),
INDEX `IDX_MG_CATALOG_PRODUCT_FLAT_1_PRICE` (`price`),
CONSTRAINT `FK_MG_MG_CAT_PRD_FLAT_1_ENTT_ID_MG_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `mg_catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) COMMENT='Catalog Product Flat (Store 1)' ENGINE=INNODB charset=utf8 COLLATE=utf8_general_ci
</sql>
If I remove the FK from the above query it runs fine.
Regards,