mysqlforeign-keysinnodb

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


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)


Solution

  • 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