Delete unreferenced row automatically in many-to-many relationships

Question

Suppose I have a table artists and a table tracks, with a many-to-many relationship between the two.

When I delete an artist, I also want to delete all the tracks that have no corresponding artists anymore automatically, i.e. in the same command of the delete operation.

I'm able to do this for the associative table (i.e. delete the corresponding rows that referenced the artist), but not for the orphan rows in the tracks table.

Code example:

DROP TABLE IF EXISTS artists2tracks;
DROP TABLE IF EXISTS artists;
DROP TABLE IF EXISTS tracks;

CREATE TABLE artists (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL);

CREATE TABLE tracks (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL);

CREATE TABLE artists2tracks (
id INTEGER PRIMARY KEY,
artists_id INTEGER REFERENCES artists(id) ON DELETE CASCADE,
tracks_id INTEGER REFERENCES tracks(id) ON DELETE CASCADE
);

INSERT INTO artists (name) VALUES ("A1"),("A2"),("A3");

INSERT INTO tracks (title) VALUES ("T1"),("T2"),("T3");

INSERT INTO artists2tracks (artists_id, tracks_id) VALUES (1,1),(1,2),(2,2),(2,3),(3,3);

DELETE FROM artists WHERE id=1;
/* I want this to also delete row with tracks.id=1 */

Answer 1

you can create a trigger which is triggered after each delete on the artist table

CREATE TRIGGER delete_track_without_artist
AFTER DELETE ON artist
FOR EACH ROW
BEGIN
DELETE
FROM tracks
WHERE tracks.id NOT IN (SELECT tracks_id FROM artists2tracks)
END;

after deleting a row from artists table, the trigger deletes every track, which is not referenced in the artist2tracks table and hence has no corresponding artist