MySQL Forums
Forum List  »  Newbie

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,

Options: ReplyQuote


Subject
Written By
Posted
February 03, 2015 08:25PM
February 03, 2015 10:54PM
Re: Error adding FK
February 04, 2015 10:09AM
February 04, 2015 12:08PM
February 04, 2015 12:12PM
February 04, 2015 12:16PM
February 04, 2015 12:42PM
February 04, 2015 01:20PM
February 04, 2015 01:23PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.