Foreign Key Constraints

[Jim Emery]

Good afternoon, hoping someone has a better grip on foreign keys than I do. I am attempting t ad foreign key contraints to a database model and find mysel completely foiled when joining my tbl_batcjh_manifest with tbl_product.

Here are the results of the join after drawing in the schema builder. Both fields are Integer(10) unsigned. I have tried the constraint from both sides.

Any suggestions? I really want to use these constraints for this project.

Jim

alter table `db_logicold_ops`.`tbl_batch_manifest` add constraint `FK_tbl_batch_manifestProduct` FOREIGN KEY (`product_id`) REFERENCES `tbl_product` (`id`)

Cannot add or update a child row: a foreign key constraint fails (`db_logicold_ops`., CONSTRAINT `FK_tbl_batch_manifestProduct` FOREIGN KEY (`product_id`) REFERENCES `tbl_product` (`id`))

alter table `db_logicold_ops`.`tbl_product` add constraint `FK_tbl_productManifest` FOREIGN KEY (`id`) REFERENCES `tbl_batch_manifest` (`product_id`)

Cannot add or update a child row: a foreign key constraint fails (`db_logicold_ops`., CONSTRAINT `FK_tbl_productManifest` FOREIGN KEY (`id`) REFERENCES `tbl_batch_manifest` (`product_id`))

CREATE TABLE `tbl_batch_manifest` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'unique identifier',

`batch_id` int(10) unsigned DEFAULT NULL COMMENT 'batch_id',

`lot` int(10) unsigned DEFAULT NULL COMMENT 'lot number',

`product_id` int(10) unsigned DEFAULT NULL COMMENT 'product_id',

`cust_lot` varchar(25) DEFAULT NULL COMMENT 'customer lot number',

`product_code` varchar(25) DEFAULT NULL COMMENT 'Product Code',

`product_date` date DEFAULT NULL COMMENT 'production date',

`quantity` int(5) unsigned DEFAULT NULL COMMENT 'Product Quantity',

`line_net` double unsigned DEFAULT NULL COMMENT 'net weight',

`line_gross` double unsigned DEFAULT NULL COMMENT 'gross weight',

`hold_id` tinyint(3) unsigned DEFAULT '0' COMMENT 'current lot status',

`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'timestamp',

PRIMARY KEY (`id`),

KEY `batchManifestID` (`batch_id`),

KEY `batchManifestProductID` (`product_id`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

CREATE TABLE `tbl_product` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Product ID',

`client_id` int(10) unsigned NOT NULL DEFAULT '1' COMMENT 'Client ID',

`customer_id` int(10) unsigned NOT NULL COMMENT 'Customer ID',

`customer_product_id` varchar(25) NOT NULL COMMENT 'Customer Product ID',

`supplier_id` varchar(25) DEFAULT NULL COMMENT 'Supplier Product ID',

`description` varchar(50) NOT NULL COMMENT 'Product Description',

`container_id` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT 'Container Type',

`weight` double unsigned DEFAULT NULL COMMENT 'Product Weight',

`catch_weight` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Catch Weight',

`weight_min` double unsigned DEFAULT NULL COMMENT 'Minimum Weight',

`weight_max` double unsigned DEFAULT NULL COMMENT 'Maximum Weight',

`catch_weight_activity` tinyint(3) unsigned NOT NULL COMMENT 'Catch Weight Activity ID',

`tare` double unsigned DEFAULT NULL COMMENT 'Product Tare',

`weight_id` tinyint(3) unsigned NOT NULL DEFAULT '1',

`height` double unsigned DEFAULT NULL COMMENT 'Product Height',

`width` double unsigned DEFAULT NULL COMMENT 'Product Width',

`depth` double unsigned DEFAULT NULL COMMENT 'Product Depth',

`measure_id` tinyint(3) unsigned DEFAULT '1',

`ti` tinyint(3) unsigned DEFAULT NULL COMMENT 'Ti',

`hi` tinyint(3) unsigned DEFAULT NULL COMMENT 'Hi',

`cube` double unsigned DEFAULT NULL COMMENT 'Product Cube',

`pallet_cube` double unsigned DEFAULT NULL COMMENT 'Product Pallet Cube',

`crush_id` tinyint(3) unsigned NOT NULL DEFAULT '1',

`default_status_id` tinyint(3) unsigned DEFAULT '1' COMMENT 'Default Hold ID',

`class` varchar(10) DEFAULT NULL COMMENT 'Product Classification',

`commodity_id` tinyint(3) unsigned DEFAULT '1' COMMENT 'Commodity Code',

`rotation_id` tinyint(3) unsigned DEFAULT '1',

`group_code` varchar(10) DEFAULT NULL COMMENT 'Customer Group Code',

`temp_class_id` tinyint(3) unsigned DEFAULT '1' COMMENT 'Temperature Classification ID',

`shelf_life` int(10) unsigned DEFAULT NULL COMMENT 'Days of Shelf Life from production',

`primary_put_start` varchar(25) DEFAULT NULL COMMENT 'Start of Primary Put Zone',

`primary_put_end` varchar(25) DEFAULT NULL COMMENT 'End of Primary Put Zone',

`secondary_put_start` int(25) unsigned DEFAULT NULL COMMENT 'Start of Secondary Put Zone',

`secondary_put_end` int(25) unsigned DEFAULT NULL COMMENT 'End of Secondary Put Zone',

`final_put_start` int(25) unsigned DEFAULT NULL COMMENT 'Start of Final Put Zone',

`final_put_end` int(25) unsigned DEFAULT NULL COMMENT 'End of Final Put Zone',

`active` tinyint(1) DEFAULT '-1',

`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`id`),

KEY `productCustomerProductID` (`customer_product_id`),

KEY `productContainerID` (`container_id`),

KEY `productCatchWeightActivityID` (`catch_weight_activity`),

KEY `productWeightID` (`weight_id`),

KEY `productCustomerID` (`customer_id`),

KEY `productCommodityID` (`commodity_id`),

KEY `FK_tbl_productTempID` (`temp_class_id`),

KEY `productMeasureID` (`measure_id`),

KEY `productCrushID` (`crush_id`),

KEY `productDefaultStatusID` (`default_status_id`),

KEY `productRotationID` (`rotation_id`),

KEY `productClientID` (`client_id`),

CONSTRAINT `FK_tbl_productcatchweightactivity` FOREIGN KEY (`catch_weight_activity`) REFERENCES `tbl_catch_weight_activity` (`activity_id`),

CONSTRAINT `FK_tbl_productCommodity` FOREIGN KEY (`commodity_id`) REFERENCES `tbl_commodity` (`id`),

CONSTRAINT `FK_tbl_productcontainer` FOREIGN KEY (`container_id`) REFERENCES `tbl_container` (`id`),

CONSTRAINT `FK_tbl_productCrush` FOREIGN KEY (`crush_id`) REFERENCES `tbl_product_crush` (`id`),

CONSTRAINT `FK_tbl_productCustomerID` FOREIGN KEY (`customer_id`) REFERENCES `tbl_customer` (`customer_id`),

CONSTRAINT `FK_tbl_productMeasureID` FOREIGN KEY (`measure_id`) REFERENCES `tbl_measure_unit` (`id`),

CONSTRAINT `FK_tbl_productRotation` FOREIGN KEY (`rotation_id`) REFERENCES `tbl_rotation_type` (`id`),

CONSTRAINT `FK_tbl_productStatus` FOREIGN KEY (`default_status_id`) REFERENCES `tbl_hold_status` (`id`),

CONSTRAINT `FK_tbl_productTempID` FOREIGN KEY (`temp_class_id`) REFERENCES `tbl_temp_class` (`id`),

CONSTRAINT `FK_tbl_productWeightID` FOREIGN KEY (`weight_id`) REFERENCES `tbl_weight_unit` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

[ashwin]

Quote:

(`db_logicold_ops`., CONSTRAINT `FK_tbl_batch_manifestProduct` FOREIGN KEY (`product_id`) REFERENCES `tbl_product` (`id`))

This error means that there is data in the child table that is not present in the parent table and hence while trying to create a foreign key constraint between the two tables you are getting the above error.

Please ensure that the column that you are trying to create the foreign key on has same data in both the child and the parent table and then try creating a constraint.

[Jim Emery]

'ashwin' wrote:

This error means that there is data in the child table that is not present in the parent table and hence while trying to create a foreign key constraint between the two tables you are getting the above error.

Please ensure that the column that you are trying to create the foreign key on has same data in both the child and the parent table and then try creating a constraint.

Thanks for the help.

[Author]

Posts