How to find rows in a table that have no references left?

Question

I have this user-agents-table in my db:

CREATE TABLE user_agents (
  pk bigint NOT NULL AUTO_INCREMENT,
  user_agent TEXT NOT NULL,
  user_agent_hash BINARY(16) UNIQUE NOT NULL,
  PRIMARY KEY (pk),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs;

And this table is referenced by several other tables by foreign-key-constraints. Now I want to clean-up all user-agents that aren't referenced anymore. Of course I can do this:

SELECT pk
FROM user_agents ua
  LEFT JOIN table_1 t1 ON ua.pk = t1.user_agent_fk
  LEFT JOIN table_2 t2 ON ua.pk = t2.user_agent_fk
  LEFT JOIN table_3 t3 ON ua.pk = t3.user_agent_fk
  LEFT JOIN table_4 t4 ON ua.pk = t4.user_agent_fk
WHERE t1.pk IS NULL
  AND t2.pk IS NULL
  AND t3.pk IS NULL
  AND t4.pk IS NULL;

But this looks pretty ugly and if someone adds table_5, that also references the user-agent-table, he also has to remember to add the new table to the clean-up-job.

So I'm searching for an easy way to ask my db: Find all entries of the table user_agents that have no foreign-key-references anymore (without listing the tables explicitly)

Answer 1

Add ON DELETE RESTRICT to all FOREIGN KEY definitions then use simple DELETE IGNORE.

CREATE TABLE main (id INT PRIMARY KEY)
SELECT 1 id UNION SELECT 2 UNION SELECT 3 UNION SELECT 4;
CREATE TABLE slave1 (id INT, FOREIGN KEY (id) REFERENCES main (id) ON DELETE RESTRICT);
INSERT INTO slave1 SELECT 1 id UNION SELECT 2;
CREATE TABLE slave2 (id INT, FOREIGN KEY (id) REFERENCES main (id) ON DELETE RESTRICT);
INSERT INTO slave2 SELECT 2 id UNION SELECT 4;
SELECT * FROM main;

id	remarks
1	referenced from slave1
2	referenced from slave1 and slave2
3	have no references, should be deleted
4	referenced from slave2

DELETE IGNORE FROM main;
SELECT * FROM main;

id
1
2
4

fiddle