ERROR 1451: 1451: Cannot delete or update a parent row: a foreign key constraint fails

Vote count:

0

i have been searching for this error and stumbled upon a few questions of the same nature, but as i understand it, they seem to be concerned on UPDATING issue. Mine stems from DELETING of an entry.

Here's how my table is made of:

CREATE TABLE `product` (
  `product_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT 'represents unique identifier for every existing products',
  `code` varchar(20) NOT NULL,
  `name` varchar(45) NOT NULL COMMENT 'description',
  `price` decimal(11,4) NOT NULL,
  `short_name` varchar(10) NOT NULL COMMENT 'name that can be used quickly to referenc or immediately know what is the product',
  `count` bigint(19) unsigned NOT NULL DEFAULT '0',
  `product_type_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `is_active` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`product_id`),
  KEY `product_product_typeFK_idx` (`product_type_id`),
  CONSTRAINT `product_product_typeFK` FOREIGN KEY (`product_type_id`) REFERENCES `product_type` (`product_type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

Then it also has some accompanying TRIGGER:

USE `RFVPOS`;
DELIMITER $$
CREATE TRIGGER `Product_BDEL` BEFORE DELETE ON `product` FOR EACH ROW

BEGIN
    INSERT INTO `product_audit`
    (product_id,
    code, 
    name, 
    short_name, 
    price,
    count,
    delete_user,
    delete_date
    )

    values
    (OLD.product_id,
    OLD.code,
    OLD.name,
    OLD.short_name,
    OLD.price,
    OLD.count,
    CURRENT_USER(),
    NOW()
    );
END

Then it flashes this error: ERROR 1451: 1451: Cannot delete or update a parent row: a foreign key constraint fails

Now, what confuses me before was that, no other table entries have been using the entry that i am deleting on 'product' table. This delete should go smoothly.

So, i tried removing my TRIGGER on the 'product' table and BLAM, the delete was a success. This means the error lies on my TRIGGER, can you help me point out where exactly (if not on the trigger) and WHY the error happened.

Best Regards, Jeong

asked 1 min ago

Lee Jeong

22

ERROR 1451: 1451: Cannot delete or update a parent row: a foreign key constraint fails