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

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

ERNOS: Erlang Networked Operating System

I've been reading Dreaming in Code lately, and I really like it. If you're not a dreamer, you may safely skip the rest of this post ;) In Chapter 10, "Engineers and Artists", Alan Kay, John Backus, and Jaron Lanier really got me thinking. I've also been thinking a lot about Minix 3 , Erlang , and the original Lisp machine . The ideas are beginning to synthesize into something cohesive--more than just the sum of their parts. Now, I'm sure that many of these ideas have already been envisioned within Tunes.org , LLVM , Microsoft's Singularity project, or in some other place that I haven't managed to discover or fully read, but I'm going to blog them anyway. Rather than wax philosophical, let me just dump out some ideas: Start with Minix 3. It's a new microkernel, and it's meant for real use, unlike the original Minix. "This new OS is extremely small, with the part that runs in kernel mode under 4000 lines of executable code.&quo

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