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