MySQL: Deleting Orphans
Referential integrity is a beautiful thing. Foreign key constraints are a godsend. However, sometimes, in certain situations you might have to get dirty and deal with orphans. Orphans are records that contain foreign key references to other records that don't exist anymore.
Suppose we have two tables, tags and article. Now suppose they have a many to many relationship via the tag_article_map table. Now, suppose for some unknown reason that someone deleted some tags from the tags table. We may now have orphans in the tag_article_map table. How do we delete them? Some special MySQL syntax makes it quite easy:
Suppose we have two tables, tags and article. Now suppose they have a many to many relationship via the tag_article_map table. Now, suppose for some unknown reason that someone deleted some tags from the tags table. We may now have orphans in the tag_article_map table. How do we delete them? Some special MySQL syntax makes it quite easy:
DELETE tag_article_map FROM tag_article_mapReferences:
LEFT JOIN tags
ON tag_article_map.tag_id = tags.tag_id
WHERE tags.tag_id IS NULL;
Comments
DELETE FROM tag_article_map
WHERE tag_id NOT IN(SELECT tag_id FROM tags);
Anyway, it's because MySQL is a broke-ass excuse for a database and anyone using it deserves what they get!
(I suppose it's likely this post gets deleted now...)
I run queries like that (and much more complicated) all day in Oracle and I never run into such odd restrictions.
> deleted now...)
Nope ;) I can differentiate a strong opinion from closed mindedness or trolling ;)
Cheers shannon.
Thanks for the kudos ;)