How to delete all child records when parent record is deleted?

Question

I have two tables, one being Floor. Each floor can contain rooms which contain the foreign key of the Floor_ID located in the Floor table. I've been reading about ON CASCADE DELETE which seems like it would be my answer but I'm confused on what I've read about it. I know I can just call a routine that contains two commands,

DELETE FROM Room WHERE Floor_ID = floorID;
DELETE FROM Floor WHERE Floor_ID = floorID;

But how could I use ON CASCADE DELETE to perform the same thing? When a floor is deleted, all rooms associated to that floor will be deleted as well.

CREATE TABLE `Floor` (
`Floor_ID` int(11) NOT NULL AUTO_INCREMENT,
`Hub_ID` int(11) NOT NULL,
`FloorNum` int(11) NOT NULL,
`FloorName` varchar(45) DEFAULT NULL,
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`Hub_ID`,`FloorNum`),
UNIQUE KEY `Floor_ID_UNIQUE` (`Floor_ID`),
KEY `fk_Floor_Hub1_idx` (`Hub_ID`),
CONSTRAINT `fk_Floor_Hub1` FOREIGN KEY (`Hub_ID`) REFERENCES `Hub` (`Hub_ID`) ON DELETE   NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=124 DEFAULT CHARSET=latin1

CREATE TABLE `Room` (
`Room_ID` int(11) NOT NULL AUTO_INCREMENT,
`RoomLabel` varchar(45) NOT NULL,
`Floor_ID` int(11) NOT NULL,
`FloorArea` decimal(10,2) NOT NULL COMMENT 'Square feet',
`RoomHeight` decimal(10,2) DEFAULT NULL COMMENT 'Feet',
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`Room_ID`),
KEY `fk_Room_Floor1_idx` (`Floor_ID`),
CONSTRAINT `fk_Room_Floor1` FOREIGN KEY (`Floor_ID`) REFERENCES `Floor` (`Floor_ID`) ON   DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=259 DEFAULT CHARSET=latin1

This might help: http://stackoverflow.com/questions/511361/how-do-i-use-on-delete-cascade-in-mysql — sgeddes, May 27 '13 at 16:18
I was thinking it worked like that, but wasn't entirely sure. I'll try it I guess and hope for the best. — Shane LeBlanc, May 27 '13 at 16:24

score 2 · Accepted Answer · answered May 27 '13 at 16:24

2

You have to define your Foreign Key constraints as ON DELETE CASCADE. Currently, you have them as NO ACTION. When they're on CASCADE, all records referring to the deleted record will be deleted too.

answered May 27 '13 at 16:24

Fons

155
1
9

I have a question though. In the event of a power failure or something of the sort during the deletion of a floor, would a rollback be performed? – Shane LeBlanc May 27 '13 at 17:52
Normally, you can always work with transactions (http://dev.mysql.com/doc/refman/5.0/en/commit.html). I think you have to setup your database with the SET autocommit to false to make a COMMIT necessary to effectively complete the operation. Else, a non committed transaction is committed by default, even though you have not told the database to do so... – Fons May 27 '13 at 20:36

How to delete all child records when parent record is deleted?

1 Answers1