delete parent automatically if child was deleted

Question

This my database relationship.

I got data like this:

LikeDislike
|-------------|--------------|-----------------|
|      id     |     userId   |    createdAt    |
|-------------|--------------|-----------------|
|       1     |       1      |    2019-03-26   |
|-------------|--------------|-----------------|

RoomLikes
|------------------|--------------|
|   likeDislikeId  |     roomId   | 
|------------------|--------------|
|       1          |       1      |
|------------------|--------------|

I want to do:

When I delete a data from RoomLikes, like DELETE FROM roomlikes where id = 1
The data in LikeDislike that related with RoomLikes will be deleted automatically.

Is there are ways to delete data of parent automatically if data of child was deleted?

Please let me know if you need more info.

Thanks.

This is what you need : [ON DELETE CASCADE OR TRIGGER](https://stackoverflow.com/a/21301567/2794280) — kgzdev, Mar 29 '19 at 08:32
What about the `CommentLikes`? They appear to have a foreign key towards `LikeDislike` Aren't you afraid records become orphan when deleting an element from `LikeDislike`? — Robert Kock, Mar 29 '19 at 08:46
@ikram maybe I'm missing something but ON DELETE CASCADE deletes child records automatically when deleting a parent record. Not the other way around — Robert Kock, Mar 29 '19 at 08:49

score 1 · Answer 1 · answered Mar 29 '19 at 08:35

1

If I understand the question you need to look into using a join in your delete statement - something like this:

DELETE RoomLikes, LikeDislike
FROM RoomLikes
INNER JOIN LikeDislike ON RoomLikes.likeDislikeId = LikeDislike.id
WHERE roomlikes.id=1;

answered Mar 29 '19 at 08:35

bhttoan

2,641
5
42
71

score 1 · Accepted Answer · answered Mar 29 '19 at 09:06

1

You can use following queries

ALTER TABLE RoomLikes add foreign key (likeDislikeId) references LikeDislike(id) ON DELETE CASCADE;

Just remember that you need to have primary key in the referenced table (LikeDislike) to add foreign key. By using this, if you delete data on parent table, data of child table will be deleted.

answered Mar 29 '19 at 09:06

Alina

56
6

You said when I delete a data on parent table then, data of child table will be deleted, then Is the opposite way possible? I mean when I delete data of child table then, data on parent will be deleted. – JillAndMe Mar 29 '19 at 11:36
It is not possible using just foreign key but if you want do that maybe you can use stored procedures or triggers – Alina Apr 01 '19 at 03:42

score 1 · Answer 3 · answered Mar 29 '19 at 09:11

Is it correct that a record in LikeDislike has exactly 1 child record that is either a RoomLike or a CommentLike?

In that case you might consider altering the corresponding foreign keys with ON DELETE CASCADE and most of all, deleting the parent record instead of the child record.

delete parent automatically if child was deleted

3 Answers3