1

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
  • 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? – ypercubeᵀᴹ Jan 11 '18 at 9:21
3

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

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.