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?
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).