Skip to main content

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

Anonymous 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.
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.
jjinux 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.
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);
jjinux 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.
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.
jjinux said…
> (I suppose it's likely this post gets
> deleted now...)

Nope ;) I can differentiate a strong opinion from closed mindedness or trolling ;)
Greg McDonald 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!
jjinux said…
:-D
Anonymous said…
Cheers for the example, Shannon. It was exactly what I needed.
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. :)
Anonymous 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.
jjinux said…
> Cheers shannon.

Thanks for the kudos ;)
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.
jjinux said…
Hmm, maybe you meant they don't support InnoDB tables :-/

Popular posts from this blog

Drawing Sierpinski's Triangle in Minecraft Using Python

In his keynote at PyCon, Eben Upton, the Executive Director of the Rasberry Pi Foundation, mentioned that not only has Minecraft been ported to the Rasberry Pi, but you can even control it with Python. Since four of my kids are avid Minecraft fans, I figured this might be a good time to teach them to program using Python. So I started yesterday with the goal of programming something cool for Minecraft and then showing it off at the San Francisco Python Meetup in the evening.

The first problem that I faced was that I didn't have a Rasberry Pi. You can't hack Minecraft by just installing the Minecraft client. Speaking of which, I didn't have the Minecraft client installed either ;) My kids always play it on their Nexus 7s. I found an open source Minecraft server called Bukkit that "provides the means to extend the popular Minecraft multiplayer server." Then I found a plugin called RaspberryJuice that implements a subset of the Minecraft Pi modding API for Bukkit s…

Apple: iPad and Emacs

Someone asked my boss's buddy Art Medlar if he was going to buy an iPad. He said, "I figure as soon as it runs Emacs, that will be the sign to buy." I think he was just trying to be funny, but his statement is actually fairly profound.

It's well known that submitting iPhone and iPad applications for sale on Apple's store is a huge pain--even if they're free and open source. Apple is acting as a gatekeeper for what is and isn't allowed on your device. I heard that Apple would never allow a scripting language to be installed on your iPad because it would allow end users to run code that they hadn't verified. (I don't have a reference for this, but if you do, please post it below.) Emacs is mostly written in Emacs Lisp. Per Apple's policy, I don't think it'll ever be possible to run Emacs on the iPad.

Emacs was written by Richard Stallman, and it practically defines the Free Software movement (in a manner of speaking at least). Stal…

Creating Windows 10 Boot Media for a Lenovo Thinkpad T410 Using Only a Mac and a Linux Machine

TL;DR: Giovanni and I struggled trying to get Windows 10 installed on the Lenovo Thinkpad T410. We struggled a lot trying to create the installation media because we only had a Mac and a Linux machine to work with. Everytime we tried to boot the USB thumb drive, it just showed us a blinking cursor. At the end, we finally realized that Windows 10 wasn't supported on this laptop :-/I've heard that it took Thomas Edison 100 tries to figure out the right material to use as a lightbulb filament. Well, I'm no Thomas Edison, but I thought it might be noteworthy to document our attempts at getting it to boot off a USB thumb drive:Download the ISO. Attempt 1: Use Etcher. Etcher says it doesn't work for Windows. Attempt 2: Use Boot Camp Assistant. It doesn't have that feature anymore. Attempt 3: Use Disk Utility on a Mac. Erase a USB thumb drive: Format: ExFAT Scheme: GUID Partition Map Mount the ISO. Copy everything from the I…