多对多关系中自动删除无引用行的实现方法

我有artists和tracks两张表，二者为多对多关系，通过中间表artists2tracks关联。目前删除艺术家时，能自动删除artists2tracks中关联该艺术家的行，但无法自动删除tracks中不再关联任何艺术家的孤儿行（比如删除id=1的艺术家后，tracks中id=1的行没有其他关联，需要一并删除）。现有表结构及操作代码如下：

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;
/* 希望这步同时删除tracks.id=1的行 */

外键的ON DELETE CASCADE只能处理直接关联的中间表行，无法自动清理孤儿tracks，需要通过触发器实现。可以在artists2tracks表上创建AFTER DELETE触发器，每当中间表的行被删除后，检查对应的track是否还存在其他关联记录，若不存在则删除该track。

具体代码如下：

-- 创建触发器，自动清理无关联的track孤儿行
CREATE TRIGGER delete_orphaned_tracks
AFTER DELETE ON artists2tracks
FOR EACH ROW
BEGIN
  DELETE FROM tracks
  WHERE id = OLD.tracks_id
  AND NOT EXISTS (
    SELECT 1 FROM artists2tracks WHERE tracks_id = OLD.tracks_id
  );
END;

测试步骤 #

1. 依次执行表创建语句、触发器创建语句
2. 插入所有测试数据
3. 执行DELETE FROM artists WHERE id=1;
4. 查询tracks表，会发现id=1的行已被删除，id=2、3的行保留（它们仍与其他艺术家存在关联）

这个触发器的优势在于只针对被删除关联行对应的track做检查，性能更高效，避免了全表扫描。

内容的提问来源于stack exchange，提问作者robertspierre