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:
DELETE tag_article_map FROM tag_article_map
LEFT JOIN tags
ON tag_article_map.tag_id = tags.tag_id
WHERE tags.tag_id IS NULL;
References:

Comments

Daniel Lyons said…
Not to be a total cad but if your database supports referential integrity, this scenario cannot occur. I'm talking about PostgreSQL of course, but tables of type InnoDB in MySQL also can handle this.
5:06 PM
stedi said…
If you have referential integrity, what you are describing can't happen (otherwise, the integrity part didn't work out). For example, if you define foreign keys on MySQL (InnoDB), you need to add 'ON UPDATE CASCADE' and 'ON DELETE CASCADE' clauses.
12:29 AM
Shannon -jj Behrens said…
I agree with both of you. I was trying to say that I *prefer* to be in situations where the database forces referential integrity. However, sometimes, for various reasons, that's not the case. For instance, I believe full text indexes don't work as well in InnoDB. My post is about how to deal with orphans when you *can't* use InnoDB.
11:06 AM
Anonymous said…
You don't need any MySQL specific feature (presumably you mean for specifying a join in a DELETE query)...

DELETE FROM tag_article_map
WHERE tag_id NOT IN(SELECT tag_id FROM tags);
5:13 PM
Shannon -jj Behrens said…
The sub-select was my first approach. Unfortunately, it doesn't work. MySQL doesn't want you to modify a table that you're using in the sub-select or something like that.
5:19 PM
Anonymous said…
As best I can tell, there's no good reason to not run that query. The problem you describe I have run into as well, in the first test I ran on a MySQL build that "supported" subselects.

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.
5:26 PM
Shannon -jj Behrens said…
> (I suppose it's likely this post gets
> deleted now...)

Nope ;) I can differentiate a strong opinion from closed mindedness or trolling ;)
10:21 PM
Greg said…
Sometimes one is required to go back in and fix a mess someone else left behind. I found this example to be quite handy tonight. So...much thanks!
9:42 PM
Anonymous said…
Cheers for the example, Shannon. It was exactly what I needed.
7:33 AM
Anonymous said…
That was very useful - thank you. Word of warning though, don't substitute in table aliases as it won't work with them... took me a while to figure that one out. :)
8:17 AM
biznuge said…
fantastic. nice little query. helped me no end tonight on a clients live db that had gotten full of dead relations from a previous bodge.

Cheers shannon.
2:03 PM
Shannon -jj Behrens said…
> Cheers shannon.

Thanks for the kudos ;)
2:20 PM
Anonymous said…
Another situation where this command can come in handy: when the MySQl db at your hosting provider does not support ISAM tables and thus automatic referential integrity constraints are not available.
1:43 AM
Shannon -jj Behrens said…
Hmm, maybe you meant they don't support InnoDB tables :-/
6:49 PM
Post a Comment