SQL ondelete cascade with join table

Question

I have three tables in database Notes, Tags, NoteTagJoin I'm using many to many database relation with foreign keys which are stored in NoteTagJoin table now I know that you can set for foreign keys onCascade delete and as far as I know if I would delete Note all Tags and NoteTagJoin references would be deleted associated with this Note, but is there any way to do so that only from Notes entries would be deleted and references from NoteTagJoin but not from Tags because other notes can have same tags using onCascade delete?

score 2 · Answer 1 · answered Jun 13 '18 at 11:04

2

Yes you can

ALTER TABLE `notes_tags` 
  ADD FOREIGN KEY (`note_id`) REFERENCES `notes` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
  ADD FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON UPDATE CASCADE ON DELETE CASCADE ;

Demo

answered Jun 13 '18 at 11:04

M Khalid Junaid

63,861
10
90
118

score 2 · Accepted Answer · answered Jun 13 '18 at 11:06

The simplest solution which comes to mind for me would be to just use ON DELETE CASCADE with the bridge table NoteTagJoin. Then, deleting notes from the Notes table would remove the note-tag associations, but would not remove anything from the Tags table.

This leaves the problem of possibly dangling tags which are not associated with any notes. But maybe you could run a periodic cleanup job to remove those, or perhaps these would be managed manually by someone (e.g. an app).

SQL ondelete cascade with join table

2 Answers2