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

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 which inserts some data:

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

Here as well is the structure of 'product_audit':

CREATE TABLE `product_audit` (
  `product_audit_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` mediumint(8) unsigned NOT NULL,
  `code` varchar(20) NOT NULL,
  `name` varchar(45) NOT NULL,
  `price` decimal(11,4) NOT NULL,
  `short_name` varchar(10) NOT NULL,
  `count` bigint(19) unsigned NOT NULL,
  `delete_user` varchar(45) NOT NULL,
  `delete_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`product_audit_id`),
  KEY `product_audit_productFK_idx` (`product_id`),
  CONSTRAINT `product_audit_productFK` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

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.

Solution

Before deleting the product your trigger will insert into product_audit. If the product_id column in product_audit is a foreign key to product, then you can't delete this row from product anymore, because it is a parent to the newly created row in product_audit.

Try to remove the foreign key constraint from product_audit.

Since you didn't show the table definition for product_audit, the above is guessing in that regard. (At the time of writing, that is. But my guess was correct!)