Mysql – Deleting from multiple tables with foreign constraints in thesql

foreign keyMySQL

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

Best Answer

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

Related Question