Cannot delete or update a parent row, a foreign key constraint fails

Question

When doing this DELETE code:

$update = "DELETE from  `products`  WHERE `product_code` = $id; ";
$updateResult = mysqli_query($link, $update) OR DIE("Update Query Error ".mysqli_error($link)."Query [".$update ."]");

It says an ERROR:

Update Query Error Cannot delete or update a parent row: a foreign key constraint fails (gibson_db.trans, CONSTRAINT prod_trans_fk FOREIGN KEY (product_code) REFERENCES products (product_code))Query [DELETE from products WHERE product_code = 1; ]

Here are my related TABLES:

CREATE TABLE IF NOT EXISTS `products` (
  `product_code` int(4) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(2500) NOT NULL,
  `description` varchar(1000) DEFAULT 'N/A',
  `price` float NOT NULL DEFAULT '0',
  `quantity` int(11) NOT NULL DEFAULT '0',
  `product_type` int(4) NOT NULL,
  `img_path` varchar(255) NOT NULL DEFAULT 'products/',
  PRIMARY KEY (`product_code`),
  KEY `prod_type_fk` (`product_type`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=37 ;

INSERT INTO `products` (`product_code`, `product_name`, `description`, `price`, `quantity`, `product_type`, `img_path`)

CREATE TABLE IF NOT EXISTS `producttype` (
  `product_type` int(4) NOT NULL AUTO_INCREMENT,
  `description` varchar(255) NOT NULL DEFAULT 'N/A',
  PRIMARY KEY (`product_type`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `producttype` (`product_type`, `description`)

ALTER TABLE `products`
  ADD CONSTRAINT `prod_type_fk` FOREIGN KEY (`product_type`) REFERENCES `producttype` (`product_type`);

Answer 1

Your Product table has relationship with some other table may be trans with constraint prod_trans_fk. Normally innodb will throw the error when you try to delete the parent record to avoid the orphan data. Following are some alternatives

1. Delete the child record 1st
2. Use cascading to delete all child records when parent is deleted.(can be dangerous eg: you might lose all transactions when you delete product)
3. Use soft deletion(set flag like is_deleted to Yes or No)(highly recommended)