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
ON tag_article_map.tag_id = tags.tag_id
WHERE tags.tag_id IS NULL;


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

Ubuntu 20.04 on a 2015 15" MacBook Pro

I decided to give Ubuntu 20.04 a try on my 2015 15" MacBook Pro. I didn't actually install it; I just live booted from a USB thumb drive which was enough to try out everything I wanted. In summary, it's not perfect, and issues with my camera would prevent me from switching, but given the right hardware, I think it's a really viable option. The first thing I wanted to try was what would happen if I plugged in a non-HiDPI screen given that my laptop has a HiDPI screen. Without sub-pixel scaling, whatever scale rate I picked for one screen would apply to the other. However, once I turned on sub-pixel scaling, I was able to pick different scale rates for the internal and external displays. That looked ok. I tried plugging in and unplugging multiple times, and it didn't crash. I doubt it'd work with my Thunderbolt display at work, but it worked fine for my HDMI displays at home. I even plugged it into my TV, and it stuck to the 100% scaling I picked for the othe

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 B

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