MySQL Forums
Forum List  »  InnoDB

Re: Foreign Key constraint issue in MultiThreading
Posted by: Rishi anjan
Date: February 02, 2016 10:37PM

Thanks James for your reply.
Now we remove Foreign key and use below trigger, but still facing the same deadlock.

CREATE DEFINER=`dbtrial`@`%` TRIGGER `emsent_demoma`.`LineItem_before_delete` BEFORE DELETE ON emsent_demoma.t_ent_line_item FOR EACH ROW
BEGIN
DECLARE C INT;

SELECT COUNT(LineItemId) INTO C FROM T_Ent_Line_Item WHERE LinkdLineItemId = OLD.LineItemId;

IF (C > 0) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Can’t Delete: LineItem has dependencies';
END IF;
END;


Below is the table Structure:-

CREATE TABLE `t_ent_line_item` (
`LineItemId` int(11) NOT NULL AUTO_INCREMENT,
`ENTId` int(11) NOT NULL,
`SuiteId` int(11) DEFAULT NULL,
`PRDId` int(11) DEFAULT NULL,
`Cost` int(11) NOT NULL,
`StartDate` datetime NOT NULL,
`EndDate` datetime NOT NULL,
`isEnabled` tinyint(1) NOT NULL DEFAULT '1',
`LineItemState` int(11) NOT NULL,
`LineItemType` int(11) NOT NULL DEFAULT '1',
`LicenseType` int(11) NOT NULL,
`quantity` int(11) NOT NULL DEFAULT '0',
`remainingQuantity` int(11) NOT NULL DEFAULT '0',
`overrideAllowed` tinyint(1) NOT NULL DEFAULT '1',
`cascadeDates` tinyint(1) NOT NULL DEFAULT '0',
`NumberActivationBased` tinyint(1) NOT NULL DEFAULT '0',
`LINKDLINEITEMID` int(11) DEFAULT NULL,
`TXNTYPE` int(11) NOT NULL DEFAULT '1',
`TXNENTID` int(11) DEFAULT NULL,
`FixedQuantity` int(11) NOT NULL DEFAULT '0',
`ActivationType` int(11) NOT NULL,
`CREATEDBY` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT 'admin',
`CREATEDATE` datetime DEFAULT NULL,
`MODIFIEDBY` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT 'admin',
`MODIFIEDDATE` datetime DEFAULT NULL,
PRIMARY KEY (`LineItemId`),
KEY `IX_T_LI_LINKDLINEITEMID` (`LINKDLINEITEMID`),
KEY `IX_T_LI_ENTId_TXNTYPE` (`ENTId`,`TXNTYPE`),
KEY `FK_T_ENT_LINE_ITEM_PRDId_T_PRD_PRDId` (`PRDId`),
KEY `FK_T_LI_T_ENT_TXNENTID` (`TXNENTID`),
KEY `FK_T_ENT_LINE_ITEM_SuiteId_T_SUITE_SuiteId` (`SuiteId`),
CONSTRAINT `FK_T_ENT_LINE_ITEM_ENTID_T_ENT_ENTID` FOREIGN KEY (`ENTId`) REFERENCES `t_ent` (`ENTId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_T_ENT_LINE_ITEM_PRDId_T_PRD_PRDId` FOREIGN KEY (`PRDId`) REFERENCES `t_prd` (`PRDId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_T_LI_T_ENT_TXNENTID` FOREIGN KEY (`TXNENTID`) REFERENCES `t_ent` (`ENTId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_T_ENT_LINE_ITEM_SuiteId_T_SUITE_SuiteId` FOREIGN KEY (`SuiteId`) REFERENCES `t_suite` (`SuiteId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=660 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Foreign Key constraint issue in MultiThreading
656
February 02, 2016 10:37PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.