Mysql Error- Cannot delete or update a parent row: a foreign key constraint fails

Question

I am getting following error not sure what the issue is

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[MySQL][ODBC 3.51 Driver][mysqld-5.5.32-31.0-log]Cannot delete or update a parent row: a foreign key constraint fails (Databasename.FADLSongs, CONSTRAINT FK_FADLSongs_OrderID FOREIGN KEY (OrderID) REFERENCES Orders (OrderID) ON DELETE NO ACTION ON UPDATE NO ACTION)

The Query that its throwing errors is

DELETE FROM Orders WHERE OrderID Not In (SELECT Distinct OrderID FROM MOrder) And 
OrderID Not In (SELECT Distinct OrderID FROM PSGOrder)

My Table Structure is as follows

CREATE TABLE IF NOT EXISTS `MOrder` (
  `MOrderID` int(11) NOT NULL AUTO_INCREMENT,
  `MediaID` int(11) NOT NULL,
  `OrderID` int(11) NOT NULL,
  `MediaOption` tinyint(4) DEFAULT NULL,
  `UnitCost` double NOT NULL,
  `Quantity` int(11) NOT NULL,
  `BonusDeduction` double DEFAULT NULL,
  `MediaOptions` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`MOrderID`),
  KEY `MediaID` (`MediaID`),
  KEY `OrderID` (`OrderID`),
  CONSTRAINT `FK_MOrder_MediaID` FOREIGN KEY (`MediaID`) REFERENCES `Media` (`MediaID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `FK_MOrder_OrderID` FOREIGN KEY (`OrderID`) REFERENCES `Orders` (`OrderID`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `Orders` (
  `OrderID` int(11) NOT NULL AUTO_INCREMENT,
  `UserID` int(11) NOT NULL,
  `PNMType` varchar(1) DEFAULT NULL,
  `PNMDeliveryOption` varchar(1) DEFAULT NULL,
  `PNMQuantity` smallint(5) unsigned DEFAULT NULL,
  `PNMSongCost` double DEFAULT NULL,
  `PNMDiscLabel` varchar(50) DEFAULT NULL,
  `ShippingCost` double DEFAULT NULL,
  PRIMARY KEY (`OrderID`),
  KEY `IDX_Orders_CouponNo` (`CouponNo`),
  KEY `IDX_Orders_VoucherNo` (`VoucherNo`),
  KEY `OrderStatusID` (`OrderStatusID`),
  KEY `UserID` (`UserID`),
  CONSTRAINT `FK_Orders_CouponNo` FOREIGN KEY (`CouponNo`) REFERENCES `Coupons` (`CouponNo`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `FK_Orders_OrderStatusID` FOREIGN KEY (`OrderStatusID`) REFERENCES `OrderStatus_LU` (`OrderStatusID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `FK_Orders_UserID` FOREIGN KEY (`UserID`) REFERENCES `Users` (`UserID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `FK_Orders_VoucherNo` FOREIGN KEY (`VoucherNo`) REFERENCES `Vouchers` (`VoucherNo`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `PSGOrder` (
  `PSGOrderID` int(11) NOT NULL AUTO_INCREMENT,
  `OrderID` int(11) NOT NULL,
  `SongID` int(11) NOT NULL,
  `DateDownloaded` datetime DEFAULT NULL,
  PRIMARY KEY (`PSGOrderID`),
  KEY `IDX_PSGOrder_OrderID` (`OrderID`),
  KEY `IDX_PSGOrder_SongID` (`SongID`),
  CONSTRAINT `FK_PSGOrder_OrderID` FOREIGN KEY (`OrderID`) REFERENCES `Orders` (`OrderID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_PSGOrder_SongID` FOREIGN KEY (`SongID`) REFERENCES `Songs` (`SongID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `FADLSongs` (
  `FADLSongID` int(11) NOT NULL AUTO_INCREMENT,
  `OrderID` int(11) NOT NULL,
  `SongID` int(11) NOT NULL,
  `DateDownloaded` datetime NOT NULL,
  PRIMARY KEY (`FADLSongID`),
  KEY `IDX_FADLSongs_OrderID` (`OrderID`),
  KEY `IDX_FADLSongs_SongID` (`SongID`),
  CONSTRAINT `FK_FADLSongs_OrderID` FOREIGN KEY (`OrderID`) REFERENCES `Orders` (`OrderID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_FADLSongs_SongID` FOREIGN KEY (`SongID`) REFERENCES `Songs` (`SongID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1768 DEFAULT CHARSET=utf8;

Where is "FADLSongs"? In there is a reference to "Orders" and it is not letting it delete. Check there. — brian, Oct 02 '14 at 06:30
FADLSongs is another table where orderid and songid fields are linked — user580950, Oct 02 '14 at 06:37
SELECT Distinct OrderID FROM MOrder->this part is giving you the error.According to your table structure you trying to manipulate parent id (foreign key) which is FOREIGN KEY (`OrderID`). orderID is referenced (is parent of other table) probably "FADLSongs" Table. So you need to deference the FADLsongs where it has OrderID as foreignkey, if that should be. — Mubo, Oct 02 '14 at 06:51

score 2 · Accepted Answer · edited May 23 '17 at 12:33

well

have a look here as well

Deleting from a MySQL table with foreign key constraints

and you can set foreign key constraints to 0 and can update/delete the row and then set back the foreign key constraints

SET foreign_key_checks = 0
   DELETE FROM Orders WHERE OrderID Not In (SELECT Distinct OrderID FROM MOrder) And OrderID Not In (SELECT Distinct OrderID FROM PSGOrder)
SET foreign_key_checks = 1

Mysql Error- Cannot delete or update a parent row: a foreign key constraint fails

1 Answers1