2

I have three tables as seen below.

enter image description here

what my requirement is, when I delete a row from a donate_club table, I need to delete all the entries related to this row from 'donationRequest' and 'committments'. Here 'commitments' table has a foreign key relation ship with 'donatinRequest' table. So when I delete an entry from 'donationRequest' table the corresponding entries are being deleted from 'committments' table also. That works fine. Also 'donationRequest' table has a foreign key relationship to 'donate_club' table, so when I delete an entry from 'donate_club' it also deletes related entries from 'donationRequest' table. It is also working fine. Now my requirement is when I delete an entry from 'donate_club', I need to delete all the related entries from both 'doantionRequest' and 'commitments' table.

Now the situation is, when I delete a row from 'donate_club', it automatically deletes related entries from 'donationRequest' table but it doesnt delete anything from 'committments' table eventhough 'committments' table has an entry related to the deleted row from 'donationRequest' table

EDIT: donate_club table create query

CREATE TABLE `donate_club` ( `id` int(11) NOT NULL AUTO_INCREMENT, `firstName` varchar(100) DEFAULT NULL, `secondName` varchar(100) DEFAULT NULL, `email` varchar(100) DEFAULT NULL, `password` varchar(20) DEFAULT NULL, `mobile` varchar(20) DEFAULT NULL, `bloodGroup` varchar(10) DEFAULT NULL, `age` varchar(10) DEFAULT NULL, `gender` varchar(20) DEFAULT NULL, `country` varchar(50) DEFAULT NULL, `location` varchar(50) DEFAULT NULL, `latitude` varchar(50) DEFAULT NULL, `longitude` varchar(50) DEFAULT NULL, `profilePicFIleName` varchar(100) DEFAULT NULL, `profilePicURL` text, PRIMARY KEY (`id`), UNIQUE KEY `mobile` (`mobile`), UNIQUE KEY `email` (`email`) ) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8

donationRequest create query

CREATE TABLE `donationrequest` ( `id` int(11) NOT NULL AUTO_INCREMENT, `latitude` varchar(50) DEFAULT NULL, `longitude` varchar(50) DEFAULT NULL, `mobile` varchar(15) DEFAULT NULL, `contactPerson` varchar(50) DEFAULT NULL, `bloodGroup` varchar(10) DEFAULT NULL, `bloodBank` varchar(200) DEFAULT NULL, `location` varchar(100) DEFAULT NULL, `postedDate` varchar(30) DEFAULT NULL, `userID` int(11) DEFAULT NULL, `fcmToken` varchar(1000) DEFAULT NULL, `requiredDate` varchar(20) DEFAULT NULL, `need` varchar(20) DEFAULT NULL, `unit` varchar(5) DEFAULT NULL, `patient` varchar(50) DEFAULT NULL, `status` tinyint(4) DEFAULT NULL, PRIMARY KEY (`id`), KEY `userID` (`userID`), CONSTRAINT `fk_to_donate_club_id` FOREIGN KEY (`userID`) REFERENCES `donate_club` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=332 DEFAULT CHARSET=utf8

committments create table query

CREATE TABLE `commitments` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userID` varchar(50) DEFAULT NULL, `fcmTockenPatient` varchar(10000) DEFAULT NULL, `fcmTockenDonor` varchar(10000) DEFAULT NULL, `committedDate` varchar(15) DEFAULT NULL, `patientName` varchar(100) DEFAULT NULL, `byStanderName` varchar(100) DEFAULT NULL, `byStanderMobile` varchar(15) DEFAULT NULL, `bloodGroup` varchar(5) DEFAULT NULL, `location` varchar(100) DEFAULT NULL, `hospital` varchar(100) DEFAULT NULL, `requestID` int(11) DEFAULT NULL, `isFinished` tinyint(2) DEFAULT NULL, `receiverID` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`), KEY `requestID` (`requestID`), CONSTRAINT `fk_to_donation_request` FOREIGN KEY (`requestID`) REFERENCES `donationrequest` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=286 DEFAULT CHARSET=utf8
Improve this question
1
  • 2
    What you have (the 2 FKs are defined with ON DELETE CASCADE) should work. Are you claiming that you delete rows from table donate_club and you are left with rows in commitments that violate the FK constraint? If that happens, it's a bug. Which version are you using? Also: are there any DELETE triggers, in any of the tables? Commented Jan 11, 2018 at 9:21

2 Answers 2

Reset to default
4

You should check out your data, according to your table definition it must work.

drop table if exists xcommitments; drop table if exists xdonations; drop table if exists xusers; create table xusers ( user_id int not null, name varchar(100), primary key (user_id) ); create table xdonations ( donation_id int not null, user_id int not null, primary key (donation_id), constraint fk_users foreign key (user_id) references xusers (user_id) on delete cascade ); create table xcommitments ( commitment_id int not null, donation_id int not null, primary key (commitment_id), constraint fk_donations foreign key (donation_id) references xdonations (donation_id) on delete cascade ); insert into xusers values (1, 'John'), (2, 'Anna'); insert into xdonations values (1, 1), (2, 1), (3, 2); insert into xcommitments values (1, 2), (2, 3); select * from xusers u left join xdonations d on u.user_id = d.user_id left join xcommitments c on d.donation_id = c.donation_id; 
 | user_id | name | donation_id | user_id | commitment_id | donation_id | |---------|------|-------------|---------|---------------|-------------| | 1 | John | 1 | 1 | NULL | NULL | | 1 | John | 2 | 1 | 1 | 2 | | 2 | Anna | 3 | 2 | 2 | 3 | 
delete from xusers where user_id = 1; select * from xusers u left join xdonations d on u.user_id = d.user_id left join xcommitments c on d.donation_id = c.donation_id; 
 | user_id | name | donation_id | user_id | commitment_id | donation_id | |---------|------|-------------|---------|---------------|-------------| | 2 | Anna | 3 | 2 | 2 | 3 | 
drop table if exists xcommitments; drop table if exists xdonations; drop table if exists xusers;

rextester here

Improve this answer
-1

try this:

DELETE FROM donate_club, donationrequest, committments INNER JOIN donationrequest ON donate_club.id = donationrequest.userID INNER JOIN committments ON donationrequest.id = committments.requestID
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.