How to use DELETE ON CASCADE on many-to-one relation

Question

May someone help me out. I'm trying something, but I'm (too) new to (my)SQL.

I use two tables: Items and Categories. Table Items has a field with foreign key: category_id.

I want the table Categories to be kept tidy. So when no item in Items is of category X in Categories, the category X should be deleted from Categories. How do you establish that. I guessed by using DELETE ON CASCADE, but so far it was only deleting corresponding items from Items when I deleted a categorie from Categories.

Thanks a lot for helping me!

Answer 1

ON DELETE CASCADE is a way of deleting a row when a row it references is deleted. This means:

• You have a row in table A
• You have a row in table B that references a row in table A
• You delete the row in table A
• The database deletes the corresponding row in table B

So you have items, and each item belongs to a particular category. In your items table, you have a category_id (and please fix your spelling) that refers to a row in the categories table. So, in your situation:

• You have a category
• You have an item that references a category
• You delete a category
• The database deletes all the items that correspond to that category

What you're asking for is sort of the other way around:

• You have items
• You delete the last item in a particular category
• The database goes and finds that category and deletes it

There's no way to do this with ON DELETE CASCADE, for two reasons:

1. How are you going to create an empty category before inserting your first item into it? The database would have to delete it immediately.
2. The database would have to do a lot of extra work scanning the table. It doesn't "know" that item #23082 was the last item in the category; it would have to somehow be keeping track of the number of items in the category to do that.

This all stems from the fact that ON DELETE CASCADE is a way of maintaining referential integrity. That is, it's a way for the database to give you a strong guarantee that if you see category #20393 on item #9847, when you go look for category #20393 you know it exists. It is not a labor saving device. :) This is why the other options are ON DELETE SET NULL and ON DELETE RESTRICT: they also guarantee integrity, but instead of deleting, they remove the bad reference or prevent the original delete from occurring.

So the answer is, you will have to either write a cron job to periodically clean that table or use some kind of ON DELETE trigger, if you're worried about empty categories.