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

Question

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.

Answer 1

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!)